Assessments, Appeals, and Digests

Back to Basics (keeping it as simple as possible)2015 WinGAP ConferenceKenny Colson jkcolson@wingap.netGregg Reese rgreese@wingap.net1WinGAP 2015 ConferenceTopicsData mining What is it?Tools that are neededCautionDatabases, Data Tables and FieldsCommon Data Requests/NeedsWhat to do with ResultsNADA/ABOS/DNRData MiningData 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 variablesIn other words, finding the stuff you want to seeData Mining ToolsSSMSSQL Server Management StudioSoftware external to WinGAPInstalled only on computers specified by Chief Appraiser and/or ITSQL MasterWinGAP menu driven applicationTools >> SQL Database UtilityLimited access via Password Admin in WinGAPMust be setup on computers that will be using itExcelData Mining ToolsWhich should I use?SSMS Little more robust than SQL MasterVery easy to copy/paste data into Excel spreadsheetSQL MasterHandy with it being on WinGAP menuContains a few functions not found in SSMSDirectly works with DBF dataFacility to copy an existing DB to a test DBWrites data to CSV files that can be opened with Excel

Data Mining ToolsWhich should I use?ExcelProcess of connecting to live data is complicated and hazardousBest used with data that has been extracted with SSMS or SQL MasterData Mining Caution?Remember the old days when you heard Lets run Fox!

Same cautionary procedures apply!!!

Data Mining Caution?Plan your actionsRemove as many distractions as possible

Data Mining Caution?Be careful with mouse clicks or menu selectionsSome can remove all your data

Working with DataBefore using data altering statements such asUpdateReplaceDeleteBACKUP!!!

We will not be talking about those commandDatabases Tables - ColumnsAY2016 (Database)Owner (Table)Realprop (Table)Personal (Table)LastName (Column)Parcel_No (Column)Invn_Val (Column)Databases Tables - Columns

Databases Tables - ColumnsDatabasescontainTablesStored ProceduresOther SQL stuffExamples of DatabasesAY2015AY2016

Databases Tables - ColumnsTables are found within a database and contain columnsExamples of TablesOwnerRealpropPersonalMobile

Databases Tables - ColumnsColumns are within tables and contain data in rowsExamples of columns in the table OwnerLastNameAddress1Examples of columns in the table RealpropParcel_NoTotalAcresExamples of columns in the table PersonalPerskeyInvn_Val

DataData is what we are after!Where does it live?Within rows under Columns found in TablesHow do we know which Table or Column to use?WinGAP Technical ManualUse SSMS/SQL Master tree viewAskEmailText Experience

Data Examplefrom Owner

Data TypesChar (N)Character dataLettersNumbersSymbolsN is width of the fieldMust be delimited with single quoteLastName = smith johnCase does not matterData TypesInt, Tinyint, Bigint, Numeric(N,D)All are numbersSize of largest value determines which one is usedAll are treated the same when evaluating data(N,D) width of field, number of decimal placesDecimal point counts as 1 positionNo delimiters are neededTotalacres = 100

Data TypesBitUsed for logical fields Notice flagsAuditsPricing method, etcValues are 0 = false1 = trueNo delimiters are neededGuide = 1

Data TypesDateEvaluated in the same manner as we write datesMust be delimited with single quotesExampleDatenow = 06/24/2014

Data Types How do I know?Use the tree view in SSMSExpand the Database click the + to the left of the DB nameExpand the TableClick the + to the left of the TableExpand ColumnsClick the + to the left of ColumnsData Type information for each column is availableTo close, click the minus sign to the left of each itemReview What best describes Data Mining?Extracting ore from an open pit quarryPanning for gold in DahlonegaGetting the information out of WinGAP tables

Review What are some Data Mining tools?Caterpillar skid steerSSMS & SQL Master John Deere backhoe

Review What is the proper hierarchical listing for SQL data?Tables Columns - DatabaseDatabase Tables - ColumnsColumns Database - Tables

Review In WinGAP terms, what is the definition of a Database?Most of the data & procedures for an Appraisal YearNo clueThe data for commercial improvements

Review How do I know which Table to use?Depends on where my fav food is placedWinGAP Tech manualSWAG

Review What is the technical term for the item that holds the heated area of a house?ColumnTableDatabase

Review In SQL, where you do find all the info for a particular mfg home?PRC in filing cabinetOn your co-workers deskIn a row within the Mobile table

Review Which of these is NOT a SQL data type?ColumnIntChar

Review Which of these data types must be delimited with single quotes in a SQL statement?IntBitChar

Review Which of the data types below are numeric?IntCharTinyInt

Review What Data type would be used to identify when a parcel was added to WinGAP?IntBitDate

Review What should you do before using SQL commands like Update, Replace or Delete?Say a little prayerNever use themBackup Database

Getting your hands dirty!!

Creating SQL StatementsRun SSMS

Creating SQL StatementsExpand Databases (click on + to left of Databases)

Creating SQL StatementsRight click on Database you want to work withSelect New Query

Creating SQL StatementsNow you have a query window (on the right) where commands can be typed

Creating SQL StatementsMany types of SQL commandsEverything we are going to do begins with Select

Select StatementFollows the following formatSelect always comes firstSecond section of statement contains what you wish to see (column list)Items are separated by commasCan be calculated or data combined columns* can be substituted for column listDisplays all columnsThird section is where to pull data from (the table)Table name is always preceded by from

Select StatementVery basic exampleSelect lastname from ownerDisplays all the lastname data in owner after Execute is clicked or F5 is pressed

Select StatementFollows the following formatSelect always comes firstSecond section of statement contains what you wish to see (column list)Items are separated by commasCan be calculated or data combined columnsThird section is where to pull data from (the table)Table name is always preceded by fromCan contain multiple tables using a join clause

Select Statement (optional sections)Filter sectionAlways follows the table section Begins with whereTypically containsField name or calculated fieldsRelational operator=, >, = 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 byTypically containsField name or calculated fieldsMultiple orders can be imposed

Select Statement (optional sections)Order By section examplesOrder by lastname

Order by parcel_no

Order by taxdistric, perskey

Select Statement ConstructSelect from [where ] [order by ][ ] indicates optional sectionSelect StatementSelect statement comparison (column list VS *)

Select StatementTwo tabs are presentResults displays columnsMessage displays # of rows

Select Statement What if I do something wrong?Is it

Select Statement What if I do something wrong?SQL will tell youWhat is wrong with this statementselect from owner

Select Statement What if I do something wrong?select from ownerHere is what SQL says Red squiggly line indicates general area of error

Select StatementsDisplay owner name and city for Smiths that live in Portal

Select * from owner

Select lastname from owner where lastname = smith and city = statesboro

Select lastname from owner where lastname like smith% and city = norwood

Select StatementsDisplay owner name and city for Smiths that live in Portal

Select StatementsDisplay cost items that need revaluing ($1.00 value) and order by acct #

Select * from personal where meff_val = 1

Select perskey, costkey from cost where valmethod = m and marketval = 1 order by perskey

Select * from cost order by perskeySelect StatementsDisplay cost items that need revaluing ($1.00 value) and order by acct #

Select StatementsDisplay a list of Sales Reasons

Select * from saleinfo

Select * from reason

Select * from reason where reasontype = sSelect StatementsDisplay a list of Sales Reasons

Select StatementsDisplay a list of Sales Reasons

Select StatementsDisplay parcel #, acres for parcels over 20.00 acres

Select parcel_no, totalacres from realprop where totalacres > 20

Select parcel_no, acres from landsubs where acres > 20

Select * from realprop where totalacres > 20Select StatementsDisplay parcel #, acres for parcels over 20.00 acres

Select StatementsHow many personal property accounts do I have with a value over 1 million dollars?

Select * from personal where curr_val > 1,000,000

Select curr_val from personal where curr_val>= 1000000

Select count(*) from personal where curr_val > 1000000Select StatementsHow many personal property accounts do I have with a value over 1 million dollars?

Lets kick it up a notch!!!

Select StatementsList parcel #, improvement #, pct complete for all res imps less than 100% complete and order by parcel #Select parcel_no,impkey,pctcomp from reprop where pctcomp0

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 > 0Select StatementsCreate a list comparing Freeport accounts with previous year Freeport accounts. Display Acct # and freeport values

Copying Data to ExcelRight click on data cell in Results tabChoose the Select All optionAgain, right click on data cell and select Copy with Headers optionOpen a new Excel worksheetRight click on first cell (A1)Select Paste

Open Reporting Services and click on County Projects

12

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