I have been working on the query below and can't for the life of me get the result I need.
In short, I need a single row per SU_SUBSCRIBER_ID that is the MAX(d.effective_date)AND the SUM (c.BILL_PYM_RECEIVED_AMT).
My issue is that the SUM (c.BILL_PYM_RECEIVED_AMT) is taking values from other effective dates aside from the MAX(d.EFFECITVE_DATE) thus skewing the figures.
Any help on this one greatly appreciated...
the QUALIFY is applied *after* the calculation of the OLAP SUM, you probbaly need to use a Derived Table to calculate the ROW_NUMBER first and the apply the SUM on it.
Are there multuple rows returned per effective_date? Then use a RANK instead of a ROW_NUMBER first and then add another QUALIFY ROW_NUMBER in the outer query.
Or add effective_date to the PARTITION:
SUM(c.BILL_PYM_RECEIVED_AMT) OVER (PARTITION BY a.SU_subscriber_id, d.effective_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
Appreciate your response, sent me along the right trail.
I added D.EFFECTIVE_DATE to the partition and 96% of the SUM(c.BILL_PAYMT_RECEIVED_AMT) calculation was correct. Upon investigation within the 4% incorrect figures I realised I also need to add another DATE field to the PARTITION which finally gave me 100% correct figures.