Date Calculation

[dw2005]

Hi Gurus,

Please help me getting previous dates in the current record based on next record date.


Example:         
Start Date   End Date   
12-Sep-12   13-Sep-11   
14-Sep-11        11-Aug-13   
12-Aug-13        1-Feb-13   
2-Feb-13           4-May-13   
5-May-13           20-Nov-13   
         
In Above example:
first record endate = Second record start date -1
second record endate = Third record start date -1 .. etc.

13-Sept-11 = [(14-sept-11)-1]
11-Aug-13 = [(12-Aug-13) - 1]   

   

[TheCognosDave]

Hello dw2005,

What a fun problem to have eh ?  I've wrestled with this same problem when analyzing transaction logs, and you have to somehow compare different rows of data, with a condition to match end dates to start dates and so on ... not so easy in a non sequential language eh ?

For myself I had a table similar to this..

TableName:Table_1

[ID]        [Start_Date]         [End_Date]
1            12-Sep-12            13-Sep-11   
2            14-Sep-11            11-Aug-13   
3            12-Aug-13            1-Feb-13   

So I queried the table using SQL and then joined the table to itself on the End_Date and Start_Date fields as follows:

SELECT
   Frst_record.ID as ID1,   Frst_record.Start_Date as Start_Date1,   Frst_record.End_Date as End_Date1,
   Sec_record.ID as ID2,   Sec_record.Start_Date as Start_Date2,   Sec_record.End_Date as End_Date2,
   Thrd_record.ID as ID3,   Thrd_record.Start_Date as Start_Date3,   Thrd_record.End_Date as End_Date3
FROM Table1 Frst_record
   INNER JOIN Table1 Sec_record ON (Frst_record.End_Date = (Sec_record.Start_Date-1))
   INNER JOIN Table1 Thrd_record ON (Sec_record.End_Date = (Thrd_record.Start_Date-1))

Good Luck dw !