Transcript 2015 Wingap Conference

2015 WinGAP Conference
Kenny Colson – [email protected]
Gregg Reese– [email protected]
Topics
 Data mining – What is it?
 Tools that are needed
 Caution
 Databases, Data Tables and Fields
 Common Data Requests/Needs
 What to do with Results
 NADA/ABOS/DNR
Data Mining
 Data Mining is an analytic process designed to
explore data (usually large amounts of data - typically
business or market related - also known as "big data")
in search of consistent patterns and/or systematic
relationships between variables
 In other words, finding the stuff you want to see
Data Mining Tools
 SSMS
 SQL Server Management Studio
 Software external to WinGAP
 Installed only on computers specified by Chief Appraiser
and/or IT
 SQL Master
 WinGAP menu driven application

Tools >> SQL Database Utility
 Limited access via Password Admin in WinGAP
 Must be setup on computers that will be using it
 Excel
Data Mining Tools
 Which should I use?
 SSMS


Little more robust than SQL Master
Very easy to copy/paste data into Excel spreadsheet
 SQL Master



Handy with it being on WinGAP menu
Contains a few functions not found in SSMS
 Directly works with DBF data
 Facility to copy an existing DB to a “test” DB
Writes data to CSV files that can be opened with Excel
Data Mining Tools
 Which should I use?
 Excel


Process of connecting to live data is complicated and
hazardous
Best used with data that has been extracted with SSMS or
SQL Master
Data Mining – Caution?
 Remember the old days when you heard “Let’s run
Fox!”
 Same cautionary procedures apply!!!
Data Mining – Caution?
 Plan your actions
 Remove as many distractions as possible
Data Mining – Caution?
 Be careful with mouse clicks or menu selections
 Some can remove all your data
Working with Data
 Before using data altering statements such as
 Update
 Replace
 Delete
BACKUP!!!
We will not be talking about those command…
Databases – Tables - Columns
AY2016
(Database)
Owner
(Table)
LastName
(Column)
Realprop
(Table)
Personal
(Table)
Parcel_No
(Column)
Invn_Val
(Column)
Databases – Tables - Columns
Databases – Tables - Columns
 Databases contain
 Tables
 Stored Procedures
 Other SQL stuff
 Examples of Databases


AY2015
AY2016
Databases – Tables - Columns
 Tables are found within a database and contain
columns
 Examples of Tables
 Owner
 Realprop
 Personal
 Mobile
Databases – Tables - Columns
 Columns are within tables and contain data in rows
 Examples of columns in the table Owner


LastName
Address1
 Examples of columns in the table Realprop


Parcel_No
TotalAcres
 Examples of columns in the table Personal
 Perskey
 Invn_Val
Data
 Data is what we are after!
 Where does it live?
 Within rows under Columns found in Tables
 How do we know which Table or Column to use?
 WinGAP Technical Manual
 Use SSMS/SQL Master tree view
 Ask


Email
Text
 Experience
Data Example from Owner
Data Types
 Char (N)
 Character data



Letters
Numbers
Symbols
 N is width of the field
 Must be delimited with single quote

LastName = ‘smith john’
 Case does not matter
Data Types
 Int, Tinyint, Bigint, Numeric(N,D)
 All are numbers
 Size of largest value determines which one is used
 All are treated the same when evaluating data
 (N,D) – width of field, number of decimal places

Decimal point counts as 1 position
 No delimiters are needed

Totalacres = 100
Data Types
 Bit
 Used for logical fields



Notice flags
Audits
Pricing method, etc
 Values are


0 = false
1 = true
 No delimiters are needed

Guide = 1
Data Types
 Date
 Evaluated in the same manner as we write dates
 Must be delimited with single quotes
 Example

Datenow = ‘06/24/2014’
Data Types – How do I know?
 Use the tree view in SSMS
 Expand the Database

click the + to the left of the DB name
 Expand the Table

Click the + to the left of the Table
 Expand Columns

Click the + to the left of Columns
 Data Type information for each column is available
 To close, click the minus sign to the left of each item
Review
 What best describes Data Mining?
a) Extracting ore from an open pit quarry
b) Panning for gold in Dahlonega
c) Getting the information out of WinGAP tables
Review
 What are some Data Mining tools?
a) Caterpillar skid steer
b) SSMS & SQL Master
c) John Deere backhoe
Review
 What is the proper hierarchical listing for SQL data?
a) Tables – Columns - Database
b) Database – Tables - Columns
c) Columns – Database - Tables
Review
 In WinGAP terms, what is the definition of a
Database?
a) Most of the data & procedures for an Appraisal Year
b) No clue
c) The data for commercial improvements
Review
 How do I know which Table to use?
a) Depends on where my fav food is placed
b) WinGAP Tech manual
c) SWAG
Review
 What is the technical term for the item that holds the
heated area of a house?
a) Column
b) Table
c) Database
Review
 In SQL, where you do find all the info for a particular
mfg home?
a) PRC in filing cabinet
b) On your co-worker’s desk
c) In a row within the Mobile table
Review
 Which of these is NOT a SQL data type?
a) Column
b) Int
c) Char
Review
 Which of these data types must be delimited with
single quotes in a SQL statement?
a) Int
b) Bit
c) Char
Review
 Which of the data types below are numeric?
a) Int
b) Char
c) TinyInt
Review
 What Data type would be used to identify when a
parcel was added to WinGAP?
a) Int
b) Bit
c) Date
Review
 What should you do before using SQL commands like
Update, Replace or Delete?
a) Say a little prayer
b) Never use them
c) Backup Database
Getting your hands dirty!!
Creating SQL Statements
 Run SSMS
Creating SQL Statements
 Expand Databases (click on + to left of Databases)
Creating SQL Statements
 Right click on Database you want to work with
 Select New Query
Creating SQL Statements
 Now you have a query window (on the right) where
commands can be typed
Creating SQL Statements
 Many types of SQL commands
 Everything we are going to do begins with Select
Select Statement
 Follows the following format
 Select always comes first
 Second section of statement contains what you wish to
see (column list)

Items are separated by commas
Can be calculated or data combined columns

* can be substituted for column list


Displays all columns
 Third section is where to pull data from (the table)

Table name is always preceded by “from”
Select Statement
 Very basic example
 Select lastname from owner
 Displays all the lastname data in owner after Execute
is clicked or F5 is pressed
Select Statement
 Follows the following format
 Select always comes first
 Second section of statement contains what you wish to
see (column list)


Items are separated by commas
Can be calculated or data combined columns
 Third section is where to pull data from (the table)


Table name is always preceded by “from”
Can contain multiple tables using a “join” clause
Select Statement (optional
sections)
 Filter section
 Always follows the “table” section
 Begins with “where”
 Typically contains



Field name or calculated fields
Relational operator
 =,
>, <, <>
Comparative value to the right of relational operator
 Multiple filters can be present
Select Statement (optional
sections)
 Filter section examples
 Where Totalacres >= 500
 Where guide = 0
 Where lastname = ‘smith tom’ and state = ‘ga’
Select Statement (optional
sections)
 Order By section (sorting the list)
 Always follows the “filter” section
 Begins with “order by”
 Typically contains

Field name or calculated fields
 Multiple orders can be imposed
Select Statement (optional
sections)
 Order By section examples
 Order by lastname
 Order by parcel_no
 Order by taxdistric, perskey
Select Statement Construct
 Select <column list> from <table name> [where
<filters>] [order by <column list>]
 [ ] indicates optional section
Select Statement
 Select statement comparison (column list VS *)
Select Statement
 Two tabs are present
 Results – displays columns
 Message – displays # of rows
Select Statement – What if I do
something wrong?
 Is it
Select Statement – What if I do
something wrong?
 SQL will tell you
 What is wrong with this statement
 select from owner
Select Statement – What if I do
something wrong?
 select from owner
 Here is what SQL says
 Red squiggly line indicates general area of error
Select Statements
 Display owner name and city for Smith’s that live in
Portal
a) Select * from owner
b) Select lastname from owner where lastname = ‘smith’
and city = ‘statesboro’
c) Select lastname from owner where lastname like
‘smith%’ and city = ‘norwood’
Select Statements
 Display owner name and city for Smith’s that live in
Portal
Select Statements
 Display cost items that need revaluing ($1.00 value)
and order by acct #
a) Select * from personal where meff_val = 1
b) Select perskey, costkey from cost where valmethod =
‘m’ and marketval = 1 order by perskey
c) Select * from cost order by perskey
Select Statements
 Display cost items that need revaluing ($1.00 value)
and order by acct #
Select Statements
 Display a list of Sales Reasons
a) Select * from saleinfo
b) Select * from reason
c) Select * from reason where reasontype = ‘s’
Select Statements
 Display a list of Sales Reasons
Select Statements
 Display a list of Sales Reasons
Select Statements
 Display parcel #, acres for parcels over 20.00 acres
a) Select parcel_no, totalacres from realprop where
totalacres > 20
b) Select parcel_no, acres from landsubs where acres >
20
c) Select * from realprop where totalacres > 20
Select Statements
 Display parcel #, acres for parcels over 20.00 acres
Select Statements
 How many personal property accounts do I have with a
value over 1 million dollars?
a) Select * from personal where curr_val > 1,000,000
b) Select curr_val from personal where curr_val>=
1000000
c) Select count(*) from personal where curr_val >
1000000
Select Statements
 How many personal property accounts do I have with a
value over 1 million dollars?
Let’s kick it up a notch!!!
Select Statements
 List parcel #, improvement #, pct complete for all res imps
less than 100% complete and order by parcel #
 Select parcel_no,impkey,pctcomp from reprop where
pctcomp<100
 Select r.parcel_no, ri.repropkey, ri.pcom from reprop ri inner
join realprop r on ri.realkey = r.realkey where ri.pcom < 1 and
ri.occupancy<>4 order by r.parcel_no
 Select r.parcel_no, ri.repropkey, ri.pcom from reprop ri inner
join realprop r on ri.realkey = r.realkey where ri.pcom < 100
and ri.occupancy<>4 order by r.parcel_no
Select Statements
 List parcel #, improvement #, pct complete for all res
imps less than 100% complete and order by parcel #
Select Statements
 Create a list of parcels on maps 6 through 14 to be reviewed
for pct complete.
 Select parcel_no,impkey,pctcomp from reprop where
pctcomp<1.00 and parcel_no >= ‘006’ and parcel_no<=‘014’
 Select r.parcel_no, ri.repropkey, ri.pcom from reprop ri inner
join realprop r on ri.realkey = r.realkey where ri.pcom < 1 and
ri.occupancy<>4 and parcel_no>=‘006’ and parcel_no<=‘014’
order by r.parcel_no
 Select r.parcel_no, ri.repropkey, ri.pcom from reprop ri inner
join realprop r on ri.realkey = r.realkey where ri.pcom < 1 and
ri.occupancy<>4 and left(parcel_no,3)>=‘006’ and
left(parcel_no,3)<=‘014’ order by r.parcel_no
Select Statements
 Create a list of parcels on maps 6 through 14 to be
reviewed for pct complete.
Select Statements
 Create a list containing names and parcel id’s of property
owners with vacant land
 Select o.lastname,r.parcel_no from realprop r inner join owner
o on r.ownkey = r.ownkey where a_value+p_value>0
 Select o.lastname,r.parcel_no from realprop r inner join owner
0 on r.realkey = o.ownkey where fmvres+fmvcom+fmvacc=0
 Select o.lastname,r.parcel_no from realprop r inner join owner
0 on r.ownkey = o.ownkey where fmvres+fmvcom+fmvacc=0
Select Statements
 Create a list containing names and parcel id’s of
property owners with vacant land
Select Statements
 Create a list of deleted prebill mfg homes. Include
owner acct #, prebill acct #, mfg, model
 Select ownacct, prebillacct, mfg, model from mobile
where deleted
 Select ownkey,mobilekey,mfg,model from mobile where
deleted and mobtype = 3
 Select ownkey,mobilekey,mfg,model from del_mobile
where mobtype = 3
Select Statements
 Create a list of deleted prebill mfg homes. Include
owner acct #, prebill acct #, mfg, model
Select Statements
 Create a list comparing Freeport accounts with previous
year Freeport accounts. Display Acct # and freeport values
 Select acct, freeportvalue from personal where
freeeportvalue>0
 Select perskey,frport_val from personal where frport_val>0
 Select p.perskey,pr.frport_val as ay2015freeport, p.frport_val
as ay2016freeport from personal p inner join
ay2015.dbo.personal pr on p.perskey=pr.perskey where
p.frport_val > 0 or pr.frport_val > 0
Select Statements
 Create a list comparing Freeport accounts with
previous year Freeport accounts. Display Acct # and
freeport values
Copying Data to Excel
 Right click on data cell in Results tab
 Choose the Select All option
 Again, right click on data cell and select Copy with
Headers option
 Open a new Excel worksheet
 Right click on first cell (A1)
 Select Paste
Open Reporting Services and click on County
Projects
2
1
Using report queries in SSMS
Using report queries in SSMS
Using report queries in SSMS
Using report queries in SSMS
Using report queries in SSMS
Using report queries in SSMS
Copy query from SSMS to Reports
Copy query from SSMS to Reports
Copy query from SSMS to Reports