selective running total

[dazz]

Hello!

The following sql does a running total of 'Duration' for every Charged_MSISDN by month. It does a running total for every 'Call_Type'. However I only want a running total of certain 'Call_Type' with values 'CIN' and 'DIG'. Will appreciate any help.
Thanks!

select F.*
,CASE WHEN F.Dest_MSISDN_Zone_Desc = 'CIN' then
case when (CAST (to_char(F.Call_Time,'HH24')AS INTEGER)>=22 OR CAST (to_char(F.Call_Time,'HH24')AS INTEGER)<6) then 'CIN_Eve'
when (DATE_PART('DOW',F.Call_Time)= 0 ) THEN 'CIN_Sun'
else 'CIN' END
else F.Dest_MSISDN_Zone_Desc
end Call_Type
,sum(f.Duration) over (w_sub rows between unbounded preceding and current row) as duration_running_local
FROM RK.Fact_Voice_Billing_Augmented F
WHERE F.Call_Time between '1-Apr-2013' and '30-Apr-2013'

window w_sub as (partition by f.Charged_MSISDN, DATE_TRUNC('month', F.Call_Time) order by F.Call_Time)

[Julie]

Does this work?

select F.*
,CASE WHEN F.Dest_MSISDN_Zone_Desc = 'CIN' then
case when (CAST (to_char(F.Call_Time,'HH24')AS INTEGER)>=22 OR CAST (to_char(F.Call_Time,'HH24')AS INTEGER)<6) then 'CIN_Eve'
when (DATE_PART('DOW',F.Call_Time)= 0 ) THEN 'CIN_Sun'
else 'CIN' END
else F.Dest_MSISDN_Zone_Desc
end Call_Type
,sum(f.Duration) over (w_sub rows between unbounded preceding and current row) as duration_running_local
FROM RK.Fact_Voice_Billing_Augmented F
WHERE F.Call_Time between '1-Apr-2013' and '30-Apr-2013'
AND F.Call_Type IN ( 'CIN', 'DIG')
window w_sub as (partition by f.Charged_MSISDN, F.Call_Type, DATE_TRUNC('month', F.Call_Time) order by F.Call_Time)

[dazz]

Hey! thanks for responding I tried it, but it gave the error that..
'Column F.Call_Type does not exist'.

I ended up having to do the following:

select FV.*
,sum(FV.Duration) over (w_sub rows between unbounded preceding and current row) as duration_running_local

From
(select F.*
,CASE WHEN F.Dest_MSISDN_Zone_Desc = 'CIR' then 'Local'
when F.Dest_MSISDN_Zone_Desc ='DIG' then 'Local'
when F.Dest_MSISDN_Zone_Desc ='NCA' then 'Local'
when F.Dest_MSISDN_Zone_Desc ='Intl_Caribbean' then 'Intl_Carib'
when F.Dest_MSISDN_Zone_Desc ='Intl_UK' then 'Intl_UK_USA_Canada'
when F.Dest_MSISDN_Zone_Desc ='Intl_USA_Canada' then 'Intl_UK_USA_Canada'
else 'Other'
end Calling_Dest
FROM RK.Fact_Voice_Billing_Augmented F
) FV

WHERE FV.Call_Time between '1-Apr-2013' and '30-Apr-2013'

window w_sub as (partition by FV.Charged_MSISDN, DATE_TRUNC('month', FV.Call_Time), FV.Calling_Dest order by FV.Call_Time)

order by FV.Charged_MSISDN, FV.Calling_Dest , FV.Call_Time