I have a table with transactions. I don't want to rely on the TransactionID to assume the order of records as things could be inserted in the wrong "order of events".
There are three types of transactions, 1=new, 2=change, 3=expiration. This is regarding insurance policy changes.
Lets say here are a few records. I am going to keep the TrasactionIDs in order for the example to be clear, but again, I need to use dates instead in case some slipped in a change after they entered an expiration.
TransactionID TypeFlag PolicyNumber EffectiveDate ChangeDate ExpireDate
1 1 123 1/1/2011
2 2 123 1/1/2011 2/1/2011
3 2 123 1/1/2011 3/1/2011
4 3 123 1/1/2011 5/1/2011 4/1/2011
I need to have something like
TransactionID TransactionFromDate TransactionToDate
1 1/1/2011 1/31/2011
2 2/1/2011 2/28/2011
3 3/1/2011 4/1/2011
Select *, isNull(ChangeDate, EffectiveDate) as TransactionFromDate,
isNull(nextRecord.ExpireDate, nextRecord.ChangeDate - 1) as TransactionToDate
Where FlagType In (1,2)
the issue being nextRecord/TransactionToDate
I tried different joins on a subQuery to get the nextRecord but they didn't work. I tried using (Select blah blah) as TransactionToDate but that select statement didn't work either.