Terry Gamble - Wednesday, January 31, 2018 1:22 PM

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.