Analytic Functions reference

[stecal]

http://www.akadia.com/services/ora_a...functions.html

Overview and Introduction
How Analytic Functions Work
The Syntax
Calculate a running Total
Top-N Queries (with examples)
Windows
Range Windows
Compute average salary for defined range
Row Windows
Accessing Rows Around Your Current Row
LAG
LEAD
Determine the First Value / Last Value of a Group
Crosstab or Pivot Queries
Conclusion
Links and Documents

[redhot]

http://blogs.ittoolbox.com/oracle/ap...nd-excel-11994

Not sure if you have seen this.

[desert_rose]

Hi ,

Please ppl , 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 :

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

But , this query , also adds , in the cumulative sum the HEALTHINDEX for a repeating ISSUEID.

Is there a way to not add the duplicate ISSUEID ?

Thanks in advance