I've got a table with the following fields in it:
UniqueID
Sequence
BeforeStatus
AfterStatus
HistoryStatusDate
Sample data looks like :
08-40089445, 1, '','BNT',10/17/17
08-40089445, 2, 'BNT','VFD',1/18/18
08-40089445, 3, 'VFD','NST',1/23/18
08-40089445, 4, 'NST','SLD',1/23/18
What I need to do is basically make a "beginning history" and "ending history" date for each record:
08-40089445, 1, '','BNT',10/17/17, '1/17/18'
08-40089445, 2, 'BNT','VFD',1/18/18, '1/22/18'
08-40089445, 3, 'VFD','NST',1/23/18,'1/23/18'
08-40089445, 4, 'NST','SLD',1/23/18,''
When I write a self join such as
SELECT * FRom
(SELECT s.*,h.HistoryStatusDate statusEnddate
FROM [StatusHistory] S
JOIN [StatusHistory] h
ON s.UniqueID=h.UniqueID
WHERE s.Sequence=h.Sequence-1
) s
--Still trying to find the 7th record for this example
WHERE s.[unique#]='08-40089445'
I end up getting the first 3 back but always miss the 4th (in my example).
Anyone have an idea of a better way to write this/solve this problem? (it's tracking when a record changes a status but it's done in an iSeries so getting the programmers to put the "ending" date on isn't very likely, and the query is ignoring due to the sequence-1 portion for the 4th (max) record.