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]
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 !