I want to group by all the primary key and select the largest effectivity year per PIN. Here's my example.
PIN OwnerNum Effectivity_Year Total Tax
pin123 1003 2014 2,000.00
pin123 1002 2009 3,000.00
pin456 4002 2015 1,500.00
pin456 4001 2014 900.00
And My desired output in sql query
PIN OwnerNum Effectivity Year Total Tax
pin123 1003 2014 2,000.00
pin456 4002 2015 1,500.00
I really find this hard because i'm conducting BI report for Real Property Tax System that has no future or past entities for effectivity year.To simplify the data, I created a module whose effectivity year is <=2015. I want to fetch the highest effectivity year only per pin and owner.Thanks for all your help.
Here's my script
[past_or_present effyr]:
LOAD PCINum,
MDINum,
BINum,
SINum,
Parcel,
PType,
OwnerNum,
Date(Max(yr)) as pyear,
PCINum&'-'&MDINum&'-'&BINum&'-'&SINum&'-'&Parcel&'-'&PType as Pin3;
SQL SELECT PCINum,
MDINum,
BINum,
SINum,
Parcel,
PType,
OwnerNum,
yr
FROM PROP.PUB.Property
WHERE yr <= 2015 Group By PCINum,
MDINum,
BINum,
SINum,
Parcel,
PType;
I try to use this query bu t it has some errors.
