need teradata query for below scenario

Database
Enthusiast

need teradata query for below scenario

Hi,

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

1 REPLY
Enthusiast

Re: need teradata query for below scenario

Select

id,

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

from table

) a

Just a high level solution .Check whether this is working... If it throws any error try calculating the diff separately and split the query.