Cumulative data difference between rows

Database

Cumulative data difference between rows

Hi All ,

I have the below requirement and trying to figure out the SQL.

Scenario:

ID          FROM_DT              THRU_DT

1         2013-04-07             2013-12-28

1        2013-04-07             2013-12-28

1        2014-01-29              2014-04-28

Output

ID          FROM_DT              THRU_DT              DIFF

1         2013-04-07             2013-12-28            NULL

1        2013-04-07             2013-12-28              -265

1        2014-01-29              2014-04-28             -32

I have tried with rows between 1 preceding and 1 preceding but not getting the desired output.

Kindly suggest

3 REPLIES
Enthusiast

Re: Cumulative data difference between rows

 How did you get -265 ? Can u please explain the requirement in a bit more detail

Re: Cumulative data difference between rows

Hi Mani,

The requirement is to find the cumulative difference between the second row from_dt and first row thru_dt .

For the first record as there is no predecessor record it will be null from the second it should check with one record above.(only previous).

select date  '2013-04-07' - date '2013-12-28' is -265.

Senior Apprentice

Re: Cumulative data difference between rows

But why is the 2nd DIFF -32 instead of 32?

FROM_DT - 
max(THRU_DT)
over (partition by ID
order by FROM_DT, THRU_DT
rows between 1 preceding and 1 preceding)