Cumulative lag
616019Apr 24 2012 — edited Apr 24 2012I am using 'Lag' to get this result. Is there a way for me to get the cumulative lag (sum of lag)?
SQL:
SELECT XC_CASE_STATUS_DESC Status, vsh_case_status_date Status_Dt,
vsh_case_status_date-LAG(vsh_case_status_date) OVER (PARTITION BY vsh_case_number order by vsh_case_number, vsh_case_status_date) AS no_of_days
FROM vidcsh, tidcxc
where VSH_CASE_STATUS_CODE = XC_CASE_STATUS_CODE
and vsh_case_number = '2008-1078'
__Output:__
Status StatusDt no_of_Days_
Status1 4/25/2008
Status2 4/28/2008 3
Status3 9/30/2010 885
Status4 10/26/2010 26
Status5 10/28/2010 2
Status6 12/20/2010 53
__Desired output:__
Status StatusDt no_of_Days Cumm_days_
Status1 4/25/2008
Status2 4/28/2008 3 3
Status3 9/30/2010 885 888
Status4 10/26/2010 26 914
Status5 10/28/2010 2 916
Status6 12/20/2010 53 969
Thoughts?
Thank you for your help.
Sandeep.