Get Last Termination Instance And Calc # of Days...

[hneal98]

Hi I have an issue where i have sets of enroll segments like this:

MemID EnrollID EffDate TermDate
A1 enr1 01/01/2006 03/31/2006
A1 enr2 05/01/2006 06/30/2006
A1 enr3 07/01/2006 12/31/2078
A2 enr1 02/01/2006 04/30/2006
A2 enr2 05/01/2006 08/31/2006
A2 enr3 11/01/2006 01/31/2007
A2 enr4 04/01/2007 12/31/2078


As you can see, member A1 has 3 segments and member A2 has 4. I need to find the very last termdate that is not 12/31/2078 and find the difference between that and the next effdate. So for instance for member A1, I would need to find the difference between 06/30/2006 and 07/01/2006, which in this case would only be one, but for A2, I would need to find the diff between 01/31/2007 and 04/01/2007.

thanks for the help and let me know if this is not clear.

Harvard

 

[bborissov]

SELECT Tbl1.MemID,
DATEDIFF(??,Tbl1.EffDate, Tbl2.TermDate)
FROM (SELECT MemId, MAX(EffDate) AS EffDate
FROM YourTable
GROUP BY MemId) Tbl1
INNER JOIN ((SELECT MemId, MAX(TermDate) AS TermDate
FROM YourTable
WHERE YEAR(TermDate) <> 2078
GROUP BY MemId) Tbl2
ON Tbl1.MemId = Tbl2.MemId
[/code]
Change ?? to what you want as a difference between these two dates

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[hneal98]

Thanks for your response. I actually need to calculate what the difference is. In other words, I won't know what the difference is ahead of time.

Thanks,

Harvard

 

[hneal98]

Sorry Borrislav,

It took me a while, but i got what you meant. I will try this out now.

Thanks,

Harvard

 

[hneal98]

I am afraid it does not work. It gives the difference between the last effdate and the last termdate. I need the difference between the last effdate and the previous termdate.

Thanks,

Harvard

 

[bborissov]

Sorry,
DateDiff must be reverted:

DATEDIFF(??,Tbl2.TermDate, Tbl1.EffDate)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP