# Date Difference between two different columns and rows

Hi Experts:

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
20071101                                      A
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.
###### Who is Participating?

Commented:
here's something to get you started
``````select a.effdt, a.status,
a.termdt,
b.effdt as nexteffdt
from #temp a
inner join #temp b on b.seq = a.seq + 1
order by a.effdt
``````
0

Commented:
Please post the sql query you run to analyze this.
0

Author Commented:
SELECT t1.abeligno,t1.absubno, t1.abeffdt, t1.abtermdt, DATEDIFF(d, CAST(t1.abeffdt as datetime), (
SELECT MAX(CAST(t2.abtermdt as datetime))
FROM diamond.JMELIGM0_DAT t2
WHERE t2.abtermdt > '0' and t2.abeligno < t1.abeligno)) AS Interval
FROM diamond.JMELIGM0_DAT t1
WHERE t1.absubno like '199803000000'
ORDER BY t1.abeffdt desc

Where t1.abeligno is the line number and t1.absubno is the member ID
0

Author Commented:
imitchie:
Your solution almost acts like a union query it's not comparing the dates (effdate and termdate) between the two lines
0

Author Commented:
imitchie:
I used your solution but also joined the subid number and that seems to give me the answer and I needed I also added to the where statement so that I see only the line I needed.   This seems to be the solution I'm looking for.  Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.