I have requirement as below .
ID FROM_DT THRU_DT
1 03-01-2008 01-01-2009
1 04-01-2009 11-01-2009
1 12-01-2009 10-01-2010
2 01-01-2007 10-01-2007
2 11-01-2007 01-01-2008
Need OUTPUT for a new column covered months for each id
for ex: for the above table for ID1 --- covered months = (04-01-2009 - 01-01-2009) +(12-01-2009 - 11-01-2009) .
max(thru_dt) over (partition by id order by from_dt,thru_dt rows between 1 preceding and 1 preceding) as prev_dt
CASE WHEN PREV_DT IS NOT NULL THEN
SUM(from_dt-prev_dt) OVER (PARTITION BY ID ORDER BY FROM_dT,THRU_DT ROWS UNBOUNDED PRECEDING) as CVRD_MNTHS
Just a high level solution .Check whether this is working... If it throws any error try calculating the diff separately and split the query.