Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
try like this :
[past_or_present effyr]:
LOAD PCINum,
MDINum,
BINum,
SINum,
Parcel,
PType,
OwnerNum,
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;
Result_table:
LOAD
max(OwnerNum ) as New_OwnerNum ,
PIN as NEW_PIN,
Effectivity_Year as NEW_Effectivity_Year,
[Total Tax] as NEW_Total_Tax
Resident
[past_or_present effyr]
group by
PIN,
Effectivity_Year,
Total Tax;
Resultant table will give you the result