Please help me with this ..
I have a HEALTHISSUE_AUDIT table which looks like this :
UPDATEDATE ISSUEID HEALTHINDEX
1-Mar-08 1 5
1-Mar-08 2 6
2-Mar-08 1 7
2-Mar-08 3 9
3-Mar-08 2 8
5-Mar-08 4 2
I need to get a cumulative sum of the latest value of HEALTHINDEX column per UPDATEDATE.The cumulative sum per UPDATEDATE shud
1)contain the HEALTHINDEX values ( which is tied to a ISSUEID) for that UPDATEDATE
2)contain the HEALTHINDEX values , for UPDATEDATE lesser than the current UPDATEDATE
3)But , if a ISSUEID has already been considered as in point 1, and the same ISSUEID is present in the lesser UPDATEDATE , the cumulative sum must not include this value of HEALTHINDEX , ie , only the latest value of HEALTHINDEX per ISSUEID shud be considered per UPDATEDATE .
I googled and managed to frame a query like :
Expand|Select|Wrap|Line Numbers
- select UPDATEDATE,SUM(SUM(HEALTH_INDEX)) OVER (ORDER BY UPDATEDATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum from HEALTHISSUE_AUDIT where ISSUEID IN (1,2,3,4) GROUP BY UPDATEDATE ORDER BY UPDATEDATE
Is there a way to not add the duplicate ISSUEID ?
Thanks in advance