rm(list=ls())
library(data.table)
library(RSQLite)
library(DBI)dbdir = "/data/ztrax.db"
con <- dbConnect(RSQLite::SQLite(),dbdir)dbListTables(con)## [1] "zasmt_building" "zasmt_buildingareas"
## [3] "zasmt_careofname" "zasmt_exteriorwall"
## [5] "zasmt_extrafeature" "zasmt_garage"
## [7] "zasmt_interiorflooring" "zasmt_interiorwall"
## [9] "zasmt_mailaddress" "zasmt_main"
## [11] "zasmt_name" "zasmt_oby"
## [13] "zasmt_pool" "zasmt_saledata"
## [15] "zasmt_taxdistrict" "zasmt_taxexemption"
## [17] "zasmt_typeconstruction" "zasmt_value"
## [19] "zasmt_vestingcodes" "ztrans_bkmanagedspecific"
## [21] "ztrans_borrowermailaddress" "ztrans_borrowername"
## [23] "ztrans_borrowernamedescriptioncode" "ztrans_buyermailaddress"
## [25] "ztrans_buyername" "ztrans_buyernamedescriptioncode"
## [27] "ztrans_foreclosurenameaddress" "ztrans_foreclosurenodnosdoc"
## [29] "ztrans_foreclosureoriginalloan" "ztrans_legacy"
## [31] "ztrans_main" "ztrans_propertyinfo"
## [33] "ztrans_sellermailaddress" "ztrans_sellername"
## [35] "ztrans_sellernamedescriptioncode"zasmt_main (Properties)df <- data.table(dbGetQuery(con,"SELECT * FROM zasmt_main LIMIT 10"))
str(df)## Classes 'data.table' and 'data.frame': 10 obs. of 96 variables:
## $ rowid : chr "00002A09-0000-0000-0FAD-000000000000" "00003500-0000-0000-0FA1-000000000000" "00003600-0000-0000-0FA3-000000000000" "00003700-0000-0000-0FA3-000000000000" ...
## $ importparcelid : int 153747456 3473408 3538944 3604480 3670016 3735552 3801088 3866624 3932160 3997696
## $ fips : int 4013 4001 4003 4003 4005 4007 4013 4013 4013 4013
## $ state : chr "AZ" "AZ" "AZ" "AZ" ...
## $ county : chr "MARICOPA" "APACHE" "COCHISE" "COCHISE" ...
## $ valuecertdate : chr "2019-10-01" "2018-10-01" "2019-10-01" "2019-10-01" ...
## $ extractdate : int 22020 12019 112019 112019 122019 102018 22020 22020 22020 22020
## $ edition : int 27 25 27 27 27 26 27 27 27 27
## $ zvendorstndcode : chr "ARS" "ARS" "ARS" "ARS" ...
## $ assessorparcelnumber : chr "134-28-008S" "104-45-010" "105-13-038" "206-07-315" ...
## $ dupapn : chr NA NA NA NA ...
## $ unformattedassessorparcelnumber : chr "13428008S" "10445010" "10513038" "20607315" ...
## $ parcelsequencenumber : int 1 1 1 1 1 1 1 1 1 1
## $ alternateparcelnumber : chr NA NA NA NA ...
## $ oldparcelnumber : chr NA NA NA NA ...
## $ parcelnumbertypestndcode : chr NA NA NA NA ...
## $ recordsourcestndcode : chr NA NA NA NA ...
## $ recordtypestndcode : chr "MP" "MP" "MP" "MP" ...
## $ confidentialrecordflag : chr "" "" "" "" ...
## $ propertyaddresssourcestndcode : chr "" "T" "" "" ...
## $ propertyhousenumber : chr "1520" "785" "348" "" ...
## $ propertyhousenumberext : chr NA NA NA NA ...
## $ propertystreetpredirectional : chr "W" "N" "W" "" ...
## $ propertystreetname : chr "Southern" "Alta" "Brown" "" ...
## $ propertystreetsuffix : chr "Ave" "Vis" "Dr" "" ...
## $ propertystreetpostdirectional : chr "" "" "" "" ...
## $ propertyfullstreetaddress : chr "1520 W Southern Ave" "785 N Alta Vis" "348 W Brown Dr" "" ...
## $ propertycity : chr "Mesa" "Springerville" "Sierra Vista" "" ...
## $ propertystate : chr "AZ" "AZ" "AZ" "AZ" ...
## $ propertyzip : chr "85202" "85938" "85635" "85609" ...
## $ propertyzip4 : int 4817 NA 3016 NA NA NA 5511 2056 3450 5529
## $ originalpropertyfullstreetaddress : chr "1520 W SOUTHERN AVE" "785 N Alta Vis" "348 W BROWN DR" "" ...
## $ originalpropertyaddresslastline : chr "MESA,AZ 85202" "Springerville,AZ 85938" "AZ 85615" "AZ 85609" ...
## $ propertybuildingnumber : chr "" "" "" "" ...
## $ propertyzoningdescription : chr "" "" "" "" ...
## $ propertyzoningsourcecode : chr NA NA NA NA ...
## $ censustract : int NA NA NA NA NA NA NA NA NA NA
## $ taxidnumber : chr "" "R0006600" "" "" ...
## $ taxamount : num 209318.1 72.8 661.7 61.6 2218.8 ...
## $ taxyear : int 2019 2018 2019 2019 2019 2018 2019 2019 2019 NA
## $ taxdelinquencyflag : chr "" "" "" "" ...
## $ taxdelinquencyamount : chr NA NA NA NA ...
## $ taxdelinquencyyear : chr NA NA NA NA ...
## $ taxratecodearea : int 41000 1060 6830 2200 150 1276 621300 451300 11300 481400
## $ legallot : chr "" "" "36" "24" ...
## $ legallotstndcode : chr "" "" "" "" ...
## $ legalotherlot : chr NA NA NA NA ...
## $ legalblock : chr "" "" "" "662" ...
## $ legalsubdivisioncode : chr NA NA NA NA ...
## $ legalsubdivisionname : chr "" "HILLCREST" "" "" ...
## $ legalcondoprojectpuddevname : chr NA NA NA NA ...
## $ legalbuildingnumber : chr NA NA NA NA ...
## $ legalunit : chr "" "" "" "" ...
## $ legalsection : int 29 NA NA NA NA NA NA NA NA NA
## $ legalphase : chr "" "" "" "" ...
## $ legaltract : chr "" "" "" "" ...
## $ legaldistrict : chr NA NA NA NA ...
## $ legalmunicipality : chr NA NA NA NA ...
## $ legalcity : chr NA NA NA NA ...
## $ legaltownship : chr "" "" "" "" ...
## $ legalstrsection : chr "29" "" "" "" ...
## $ legalstrtownship : chr "1N" "" "" "" ...
## $ legalstrrange : chr "5E" "" "" "" ...
## $ legalstrmeridian : chr NA NA NA NA ...
## $ legalsectwnrngmer : chr "SEC 29 TWN 1N RNG 5E" "" "" "" ...
## $ legalrecordersmapreference : chr "" "" "" "" ...
## $ legaldescription : chr "POR S2 SEC 29 DAF COM AT S4 COR SD SEC TH N 55F TH N 88D 54M E 206.21F TPOB TH N 01D 05M W 275.93F TH S 88D 54M"| __truncated__ "SUBDIVISION: HILLCREST LOT: 10" "GARDEN CANYON TERR LOT 36" "ARIZ SUN SITES #6 LOT 24 BLK 662" ...
## $ legalneighborhoodsourcecode : num 4 NA NA NA 1.03 NA 2 2 7 5
## $ noofbuildings : int 1 0 1 0 1 0 1 1 1 1
## $ lotsizeacres : num 4.332 0.31 NA 0.264 0.351 ...
## $ lotsizesquarefeet : num 188700 13504 NA 11500 15273 ...
## $ lotsizefrontagefeet : chr NA NA NA NA ...
## $ lotsizedepthfeet : chr NA NA NA NA ...
## $ lotsizeirr : chr NA NA NA NA ...
## $ lotsitetopographystndcode : chr NA NA NA NA ...
## $ loadid : chr NA NA NA NA ...
## $ propertyaddressmatchcode : chr NA NA NA NA ...
## $ propertyaddressunitdesignator : chr "" "" "" "" ...
## $ propertyaddressunitnumber : chr "" "" "" "" ...
## $ propertyaddresscarrierroute : chr "C043" "" "C043" "" ...
## $ propertyaddressgeocodematchcode : chr NA NA NA NA ...
## $ propertyaddresslatitude : num 33.4 34.1 31.5 NA 35.2 ...
## $ propertyaddresslongitude : num -112 -109 -110 NA -112 ...
## $ propertyaddresscensustractandblock:integer64 40134221031003 40019705012007 40030015011043 NA NA NA 40130822042025 40131125112035 ...
## $ propertyaddressconfidencescore : chr NA NA NA NA ...
## $ propertyaddresscbsacode : chr NA NA NA NA ...
## $ propertyaddresscbsadivisioncode : chr NA NA NA NA ...
## $ propertyaddressmatchtype : int 1 1 1 1 1 1 1 1 1 1
## $ propertyaddressdpv : chr "N" "" "Y" "" ...
## $ propertygeocodequalitycode : chr "Parcel" "Interpolatedranged" "Parcel" "Zip5" ...
## $ propertyaddressqualitycode : chr "High" "High" "High" "VeryLow" ...
## $ subedition : int 0 3 0 0 1 3 0 0 0 0
## $ batchid : int 1001057785 1001009301 1001089405 1001089405 1001076661 1001027203 1001057785 1001057785 1001057785 1001057785
## $ bkfspid : int -1 -1 -1 -1 -1 -1 -1 -1 -1 -1
## $ sourcechksum : chr NA NA NA NA ...
## $ statefips : chr "04" "04" "04" "04" ...
## - attr(*, ".internal.selfref")=<externalptr>rowid,propertyfullstreetaddress,propertycity,propertyzip, propertyaddresscensustractandblock, and importparcelid for all properties in Louisiana (statefips=22)lahomes <- data.table(dbGetQuery(con,"SELECT rowid,propertyfullstreetaddress,propertycity,propertyzip,propertyaddresscensustractandblock,importparcelid FROM zasmt_main where statefips='22'"))## Warning in result_fetch(res@ptr, n = n): Column
## `propertyaddresscensustractandblock`: mixed type, first seen values of type
## integer64, coercing other values of type realhead(lahomes)## rowid propertyfullstreetaddress propertycity
## 1: 00004909-0000-0000-5629-000000000000 126 E 48th St Cut Off
## 2: 00007303-0000-0000-55F5-000000000000 1028 State Highway 1 S Prairieville
## 3: 00007403-0000-0000-55F9-000000000000 Marksville
## 4: 00007503-0000-0000-55FF-000000000000 111 Norman St Lafayette
## 5: 00007603-0000-0000-5601-000000000000 11165 Heritage Oaks Shreveport
## 6: 00007703-0000-0000-5601-000000000000 5454 Financial Plz Shreveport
## propertyzip propertyaddresscensustractandblock importparcelid
## 1: 70345 220570211002009 155779072
## 2: 70769 <NA> 57868288
## 3: 71351 <NA> 57933824
## 4: 70508 220550014071007 57999360
## 5: 71106 220170240001031 58064896
## 6: 71129 220170243031008 58130432ztrans_main (Transactions)df <- data.table(dbGetQuery(con,"SELECT * FROM ztrans_main LIMIT 10"))
str(df)## Classes 'data.table' and 'data.frame': 10 obs. of 132 variables:
## $ transid : int 31446935 31446936 31446937 31446938 31446939 31446940 31446941 31446942 31446943 31446944
## $ fips : int 4001 4001 4001 4001 4001 4001 4001 4001 4001 4001
## $ state : chr "AZ" "AZ" "AZ" "AZ" ...
## $ county : chr "APACHE" "APACHE" "APACHE" "APACHE" ...
## $ dataclassstndcode : chr "H" "D" "D" "D" ...
## $ recordtypestndcode : chr "P" "P" "P" "P" ...
## $ recordingdate : chr "2005-01-03" "2005-01-03" "2005-01-03" "2005-01-03" ...
## $ recordingdocumentnumber : chr "2005-000039" "2005-000035" "2005-000033" "2005-000012" ...
## $ recordingbooknumber : chr "" "" "" "" ...
## $ recordingpagenumber : chr "" "" "" "" ...
## $ rerecordedcorrectionstndcode : chr "" "" "" "" ...
## $ priorrecordingdate : chr "" "" "" "" ...
## $ priordocumentdate : chr "" "" "" "" ...
## $ priordocumentnumber : chr "" "" "" "" ...
## $ priorbooknumber : chr "" "" "" "" ...
## $ priorpagenumber : chr "" "" "" "" ...
## $ documenttypestndcode : chr "WRDE" "WRDE" "INTR" "WRDE" ...
## $ documentdate : chr "2004-06-04" "2004-11-08" "2004-02-23" "1998-06-19" ...
## $ signaturedate : chr "" "" "" "" ...
## $ effectivedate : chr "" "" "" "" ...
## $ buyervestingstndcode : chr "" "WS" "" "JT" ...
## $ buyermultivestingflag : chr "" "" "" "" ...
## $ partialinteresttransferstndcode: chr "" "" "" "" ...
## $ partialinteresttransferpercent : num NA NA NA NA NA NA NA NA NA NA
## $ salespriceamount : num 22000 15000 NA NA NA 110000 7850 NA 58000 NA
## $ salespriceamountstndcode : chr "AV" "AV" "" "" ...
## $ citytransfertax : num 0 0 0 0 0 0 0 0 0 0
## $ countytransfertax : num 0 0 0 0 0 0 0 0 0 0
## $ statetransfertax : num NA NA NA NA NA NA NA NA NA NA
## $ totaltransfertax : num 0 0 0 0 0 0 0 0 0 0
## $ intrafamilytransferflag : chr "" "" "Y" "" ...
## $ transfertaxexemptflag : chr "" "" "Y" "" ...
## $ propertyusestndcode : chr "UL" "UL" "" "" ...
## $ assessmentlandusestndcode : chr "RR103" "VL000" "VL000" "VL000" ...
## $ occupancystatusstndcode : chr "" "" "" "" ...
## $ legalstndcode : chr "C" "C" "C" "C" ...
## $ borrowervestingstndcode : chr "" "" "" "" ...
## $ lendername : chr "MARK E GRAFF ETAL" "" "" "" ...
## $ lendertypestndcode : chr "PP" "" "" "" ...
## $ lenderidstndcode : chr "000357" "" "" "" ...
## $ lenderdbaname : chr "" "" "" "" ...
## $ dbalendertypestndcode : chr "" "" "" "" ...
## $ dbalenderidstndcode : chr "" "" "" "" ...
## $ lendermailcareofname : chr NA NA NA NA ...
## $ lendermailhousenumber : chr NA NA NA NA ...
## $ lendermailhousenumberext : chr NA NA NA NA ...
## $ lendermailstreetpredirectional : chr NA NA NA NA ...
## $ lendermailstreetname : chr NA NA NA NA ...
## $ lendermailstreetsuffix : chr NA NA NA NA ...
## $ lendermailstreetpostdirectional: chr NA NA NA NA ...
## $ lendermailfullstreetaddress : chr "" "" "" "" ...
## $ lendermailbuildingname : chr NA NA NA NA ...
## $ lendermailbuildingnumber : chr NA NA NA NA ...
## $ lendermailunitdesignator : chr NA NA NA NA ...
## $ lendermailunit : chr "" "" "" "" ...
## $ lendermailcity : chr "" "" "" "" ...
## $ lendermailstate : chr "" "" "" "" ...
## $ lendermailzip : int NA NA NA NA NA NA NA NA NA NA
## $ lendermailzip4 : int NA NA NA NA NA NA NA NA NA NA
## $ loanamount : num 22000 0 0 0 0 ...
## $ loanamountstndcode : chr "" "" "" "" ...
## $ maximumloanamount : num NA NA NA NA NA NA NA NA NA NA
## $ loantypestndcode : chr "SL" "" "" "" ...
## $ loantypeclosedopenendstndcode : chr "" "" "" "" ...
## $ loantypefutureadvanceflag : chr "" "" "" "" ...
## $ loantypeprogramstndcode : chr "" "" "" "" ...
## $ loanratetypestndcode : chr "" "" "" "" ...
## $ loanduedate : chr "" "" "" "" ...
## $ loantermmonths : int NA NA NA NA NA NA NA NA NA NA
## $ loantermyears : int NA NA NA NA NA NA NA NA NA NA
## $ initialinterestrate : num 0 0 0 0 0 6.62 0 0 0 0
## $ armfirstadjustmentdate : chr "" "" "" "" ...
## $ armfirstadjustmentmaxrate : num NA NA NA NA NA 8.62 NA NA NA NA
## $ armfirstadjustmentminrate : num NA NA NA NA NA 6.62 NA NA NA NA
## $ armindexstndcode : chr "" "" "" "" ...
## $ armadjustmentfrequencystndcode : chr "" "" "" "" ...
## $ armmargin : num NA NA NA NA NA 5 NA NA NA NA
## $ arminitialcap : num NA NA NA NA NA NA NA NA NA NA
## $ armperiodiccap : num NA NA NA NA NA NA NA NA NA NA
## $ armlifetimecap : num NA NA NA NA NA NA NA NA NA NA
## $ armmaxinterestrate : num NA NA NA NA NA ...
## $ armmininterestrate : num NA NA NA NA NA NA NA NA NA NA
## $ interestonlyflag : chr "" "" "" "" ...
## $ interestonlyterm : int NA NA NA NA NA NA NA NA NA NA
## $ prepaymentpenaltyflag : chr "" "" "" "" ...
## $ prepaymentpenaltyterm : chr "" "" "" "" ...
## $ biweeklypaymentflag : chr "" "" "" "" ...
## $ assumabilityriderflag : chr "" "" "" "" ...
## $ balloonriderflag : chr "" "" "" "" ...
## $ condominiumriderflag : chr "" "" "" "" ...
## $ plannedunitdevelopmentriderflag: chr "" "" "" "" ...
## $ secondhomeriderflag : chr "" "" "" "" ...
## $ onetofourfamilyriderflag : chr "" "" "" "" ...
## $ concurrentmtgedocorbkpg : chr "2005-000040" "" "" "" ...
## $ loannumber : chr "" "" "" "" ...
## $ mersminnumber : chr "" "" "" "" ...
## $ casenumber : chr "" "" "" "" ...
## $ mersflag : chr "" "" "" "" ...
## $ titlecompanyname : chr "TRANSNATION TITLE INS CO" "TRANSNATION TITLE INS CO" "TRANSNATION TITLE INS CO" "NONE AVAILABLE" ...
## [list output truncated]
## - attr(*, ".internal.selfref")=<externalptr>ztrans_propertyinfo (Transactions)df <- data.table(dbGetQuery(con,"SELECT * FROM ztrans_propertyinfo LIMIT 10"))
str(df)## Classes 'data.table' and 'data.frame': 10 obs. of 69 variables:
## $ transid : int 31446935 31446936 31446937 31446938 31446939 31446940 31446940 31446941 31446942 31446943
## $ assessorparcelnumber : chr "106-73-009U" "205-13-023" "204-69-220" "202-36-161" ...
## $ apnindicatorstndcode : chr "" "" "" "" ...
## $ taxidnumber : chr "" "" "" "" ...
## $ taxidindicatorstndcode : chr "" "" "" "" ...
## $ unformattedassessorparcelnumber : chr "10673009U" "20513023" "20469220" "20236161" ...
## $ alternateparcelnumber : chr "" "" "" "" ...
## $ hawaiicondocprcode : chr "" "" "" "" ...
## $ propertyhousenumber : chr "95" "" "" "" ...
## $ propertyhousenumberext : chr NA NA NA NA ...
## $ propertystreetpredirectional : chr "" "" "" "" ...
## $ propertystreetname : chr "County Road 3099" "" "" "" ...
## $ propertystreetsuffix : chr "" "" "" "" ...
## $ propertystreetpostdirectional : chr "" "" "" "" ...
## $ propertybuildingnumber : chr "" "" "" "" ...
## $ propertyfullstreetaddress : chr "95 County Road 3099" "115 N7212" "131 N7118" "" ...
## $ propertycity : chr "Vernon" "" "" "" ...
## $ propertystate : chr "AZ" "" "" "" ...
## $ propertyzip : chr "85940" "85936" "85936" "85936" ...
## $ propertyzip4 : int NA NA NA NA NA 5242 NA NA NA NA
## $ originalpropertyfullstreetaddress : chr "" "" "" "" ...
## $ originalpropertyaddresslastline : chr "" "" "" "" ...
## $ propertyaddressstndcode : chr "" "" "" "" ...
## $ legallot : chr "9" "107" "220" "161" ...
## $ legalotherlot : chr "" "" "" "" ...
## $ legallotcode : chr "P" "" "" "" ...
## $ legalblock : chr "" "" "" "" ...
## $ legalsubdivisionname : chr "" "WITCH WELL RANCHES" "RED SKY RANCH" "RIVER SPRINGS RANCH" ...
## $ legalcondoprojectpuddevname : chr "" "" "" "" ...
## $ legalbuildingnumber : chr "" "" "" "" ...
## $ legalunit : chr "" "2" "" "V" ...
## $ legalsection : chr "" "" "" "" ...
## $ legalphase : chr "" "" "4" "" ...
## $ legaltract : chr "" "" "" "" ...
## $ legaldistrict : chr "" "" "" "" ...
## $ legalmunicipality : chr "" "" "" "" ...
## $ legalcity : chr "" "" "" "" ...
## $ legaltownship : chr "" "" "" "" ...
## $ legalstrsection : chr NA NA NA NA ...
## $ legalstrtownship : chr NA NA NA NA ...
## $ legalstrrange : chr NA NA NA NA ...
## $ legalstrmeridian : chr "" "" "" "" ...
## $ legalsectwnrngmer : chr "" "" "S07T16NR28E G&SRB&M" "S07T12NR29E G&SRB&M" ...
## $ legalrecordersmapreference : chr "MB6 PG29" "" "MB10 PG79" "MB7 PG36-39" ...
## $ legaldescription : chr "" "" "" "" ...
## $ legallotsize : chr "2.000 AC" "40.000 AC" "36.000 AC" "36.250 AC" ...
## $ propertysequencenumber : int 1 1 1 1 1 1 2 1 1 1
## $ propertyaddressmatchcode : chr "N" "N" "N" "N" ...
## $ propertyaddressunitdesignator : chr "" "" "" "" ...
## $ propertyaddressunitnumber : chr "" "" "" "" ...
## $ propertyaddresscarrierroute : chr "" "" "" "" ...
## $ propertyaddressgeocodematchcode : chr "N" "N" "N" "N" ...
## $ propertyaddresslatitude : num 34.3 NA NA NA NA ...
## $ propertyaddresslongitude : num -110 NA NA NA NA ...
## $ propertyaddresscensustractandblock:integer64 40019705023080 NA NA NA NA 40019703001433 NA NA ...
## $ propertyaddressconfidencescore : chr NA NA NA NA ...
## $ propertyaddresscbsacode : chr NA NA NA NA ...
## $ propertyaddresscbsadivisioncode : chr NA NA NA NA ...
## $ propertyaddressmatchtype : int 1 1 1 1 1 1 1 255 1 1
## $ propertyaddressdpv : chr "" "" "" "" ...
## $ propertygeocodequalitycode : chr "Parcel" "Zip5" "Zip5" "Interpolatedranged" ...
## $ propertyaddressqualitycode : chr "High" "High" "High" "High" ...
## $ fips : int 4001 4001 4001 4001 4001 4001 4001 4001 4001 4001
## $ loadid :integer64 6406963080 6407015047 6407025740 6406990126 6406982370 6406949428 6406949431 6407041287 ...
## $ importparcelid : int 3478959 3506283 3504918 3497663 3497632 3474719 3474720 NA 3511667 3475512
## $ bkfspid : int 25872 25869 25868 25865 25866 25873 25874 25864 25867 25875
## $ assessmentrecordmatchflag : int 1 1 1 1 1 1 1 0 1 1
## $ batchid : int 1001386864 1001386864 1001386864 1001386864 1001386864 1001386864 1001386864 1001386864 1001386864 1001386864
## $ statefips : chr "04" "04" "04" "04" ...
## - attr(*, ".internal.selfref")=<externalptr>importparcelid from zasmt_maindf <- data.table(dbGetQuery(con,"SELECT importparcelid,propertyfullstreetaddress,propertycity FROM zasmt_main where propertyfullstreetaddress='10622 Hillshire Ave' and propertycity='Baton Rouge'"))
print(df)## importparcelid propertyfullstreetaddress propertycity
## 1: 58479460 10622 Hillshire Ave Baton Rougetransid in ztrans_propertyinfo table that links to importparcelid=58479460df <- data.table(dbGetQuery(con,"SELECT transid FROM ztrans_propertyinfo where importparcelid=58479460"))
df## transid
## 1: 229248051
## 2: 229279407
## 3: 229389040
## 4: 229417994
## 5: 229439408
## 6: 383893640
## 7: 417482905
## 8: 505731231salespriceamount and documentdate for each of the above transaction from ztrans_main tabledf <- data.table(dbGetQuery(con,"SELECT salespriceamount,documentdate FROM ztrans_main where transid in (229248051,229279407,229389040,229417994,229439408,383893640,417482905,505731231)"))
df## salespriceamount documentdate
## 1: 29900 1999-03-04
## 2: 172500 2001-02-16
## 3: 254000 2014-04-15
## 4: NA 2005-01-25
## 5: NA 2006-08-25
## 6: 264000 2016-05-13
## 7: 272000 2017-08-08
## 8: NA 2020-07-29(This takes about 30 minutes to run)
df <- data.table(dbGetQuery(con,
"SELECT
ztrans_main.transid,
ztrans_main.salespriceamount,
ztrans_main.documentdate,
zasmt_main.propertyfullstreetaddress,
zasmt_main.propertycity,
zasmt_main.importparcelid
FROM ztrans_main
JOIN ztrans_propertyinfo
ON ztrans_propertyinfo.transid=ztrans_main.transid
JOIN zasmt_main
ON zasmt_main.importparcelid= ztrans_propertyinfo.importparcelid
where
zasmt_main.statefips='22' and
zasmt_main.propertyfullstreetaddress='10622 Hillshire Ave' and
zasmt_main.propertycity='Baton Rouge'"))
df## transid salespriceamount documentdate propertyfullstreetaddress
## 1: 229248051 29900 1999-03-04 10622 Hillshire Ave
## 2: 229279407 172500 2001-02-16 10622 Hillshire Ave
## 3: 229389040 254000 2014-04-15 10622 Hillshire Ave
## 4: 229417994 NA 2005-01-25 10622 Hillshire Ave
## 5: 229439408 NA 2006-08-25 10622 Hillshire Ave
## 6: 383893640 264000 2016-05-13 10622 Hillshire Ave
## 7: 417482905 272000 2017-08-08 10622 Hillshire Ave
## 8: 505731231 NA 2020-07-29 10622 Hillshire Ave
## propertycity importparcelid
## 1: Baton Rouge 58479460
## 2: Baton Rouge 58479460
## 3: Baton Rouge 58479460
## 4: Baton Rouge 58479460
## 5: Baton Rouge 58479460
## 6: Baton Rouge 58479460
## 7: Baton Rouge 58479460
## 8: Baton Rouge 58479460dbDisconnect(con)