Cursor, Temp Tables, or Subquery?

[Simon2112]

Long time lurker.

I am well versed in VBA and can solve this problem there, but I really need to do this entirely with TSQL. I am not sure what the best approach is, and am having limited success with the many things I have tried.

TblPerson gives me PersonID and FormDate. One or more entries per person

TblInsurance gives me PersonID and ChangeDate. One or more entries per Person. I need the TblInsurance entry with the last ChangeDate *before* the TblPerson.FormDate.

I know I need to get the PersonID and FormDate from TblPerson, then get TOP 1 from TblInsurance for that PersonID where ChangeDate < FormDate.

I then need to combine several fields from both tables into a single record in a temp table.

I will need to move through every record in TblPerson.

I have been trying to do this with a cursor, is there a better way?

Thank you!

 

[gmmastros]

try this:

;With LastFormDate As
(
Select PersonId, Max(FormDate) As MaxFormDate
From   @tblPerson 
Group BY PersonId
), MaxChangeDate As
(
Select LastFormDate.PersonId,
       LastFormDate.MaxFormDate,
       tblInsurance.ChangeDate 
From   LastFormDate
       Inner Join @tblInsurance As tblInsurance
         On LastFormDate.PersonId = tblInsurance.PersonId
         And LastFormDate.MaxFormDate >= tblInsurance.ChangeDate
)
Select PersonId, MaxFormDate, Max(ChangeDate) As ChangeDate
From   MaxChangeDate
Group By PersonId, MaxFormDate

If this returns the correct results and you would like me to explain it, let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Simon2112]

George, This does work, and after much trial and error, I believe I understand it. Thank you very much!