I had asked this question before but never received a correct answer. I'm currently using SQL 2000 and need to calculate the following:
EffDT TermDT Status
20070801 20071031 D
20070601 20070630 A <-------- Error
20070501 20070531 A
The above shows dates of member eligibility. Everytime that a new eligibility sequence (or line) is added the prior one is closed with the termdt and a new one is open. I need to make sure there are no gaps in the dates. For example if you like at the line marked with error, the line has a term date of 20070630 and the top line has an effective date of 20070801. This is incorrect because there's a time gap (July). The correct termdt for the error line should be 20070731 or the effective date on the next line should be 20070701. I can't figure out how to have SQL read the effdt on the top line and do a datediff of one day with the termdt in the bottom line.