MAX, SUM OLAP Functions

Analytics
Fan

MAX, SUM OLAP Functions

Hi

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...

SEL

a.CU_Customer_ID 

,a.SU_Subscriber_ID 

,a.Initial_Activation_Date 

,d.effective_date 

,(d.effective_date  - a.Initial_Activation_Date ) AS DAYS_ON_THE_NETWORK

,SUM(c.BILL_PYM_RECEIVED_AMT) OVER (PARTITION BY a.SU_subscriber_id ORDER BY d.effective_date DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS BILL_PYMT_AMT

,CASE WHEN BILL_PYMT_AMT = 0 THEN 'N' ELSE  'Y' END AS PAYMENT_MADE

,CASE WHEN BILL_PYMT_AMT IN ('199','219','299','149','99','39','198','150','250','100','49','169') THEN 'Y' ELSE 'N' END AS PREPAYMENT

,CASE WHEN (PAYMENT_MADE = 'Y' AND PREPAYMENT = 'Y') THEN 'N'

WHEN PAYMENT_MADE = 'N' THEN 'N'

ELSE 'Y'

END AS BILL_PAYMENT_MADE

FROM SN_SUBSCRIBER_SNAPSHOT_CURR a

LEFT JOIN P_FRAUD.PK_FRAUD_REPORT b

ON (a.CU_CUSTOMER_ID = b.BAN AND a.SU_SUBSCRIBER_ID = b.CTN)

LEFT JOIN F_BILL_CUSTOMER c 

ON a.CU_CUSTOMER_ID = c.CU_CUSTOMER_ID 

JOIN su_amdocs_sub_hist d

ON (a.CU_CUSTOMER_ID = d.CU_CUSTOMER_ID AND d.SU_SUBSCRIBER_ID = d.SU_SUBSCRIBER_ID)

WHERE a.su_sub_status_last_rsn_code = 'FORC'

AND a.Su_Sub_Status_Last_Date (FORMAT 'dd/mm/yyyy')>'30/06/2013' 

AND a.customer_type  <> 'PPD'

QUALIFY ROW_NUMBER () OVER (PARTITION BY a.su_subscriber_id ORDER BY d.effective_date DESC) = 1

2 REPLIES
Junior Contributor

Re: MAX, SUM OLAP Functions

Hi Peter,

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)

Dieter

Fan

Re: MAX, SUM OLAP Functions

Dieter,

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. 

Thanks again,

Peter