Hi All,
Thanks in Advance!
I am working with scala code and in a need to query which will produce below output:
Source Table :
ID yearMnth Marks
1 201612 25
1 201701 85
1 201702 72
1 201703 73
1 201704 73
1 201706 76
Target Table required :
ID yearMnth Marks TotMrksinlast4year
1 201612 25 25
1 201701 85 110
1 201702 72 182
1 201703 73 255
1 201704 73 303
1 201706 76 222
Basically It should add previous 4 year marks in new column (TotMrksinlast4year) i.e if year is 201706 then it should add mark from 201703. I tried using windows function like below but it does not work for me.
sum( case when yearMnth between yearMnth -4 and yearMnth then Marks else 0 end) over (Partition by ID order by yearMnth desc) as TotMrksinlast4year
Appreciate any help. Do let me know if any other way to do it. Thank you!