IntercareSupport
asked on
SQL last date per patient
I am using MS SQL Server 2005.
Patients have different insurances at different times. For each patient, I need their most recent insurance. For the code below, when I specify a
where (Patient_Id = Patient123)
It finds that patient's most recent insurance.
My problem is that I need to do this for ALL patients in my database. Any ideas?
Patients have different insurances at different times. For each patient, I need their most recent insurance. For the code below, when I specify a
where (Patient_Id = Patient123)
It finds that patient's most recent insurance.
My problem is that I need to do this for ALL patients in my database. Any ideas?
SELECT Patient_Account_No, Payor_Insurance_Id, Payor_Individual_Id, Payor_Effective_Date, Payor_Term_Date
into #temp1
FROM vw_Patient_Insurance
WHERE (Payor_Term_Date IS NULL) AND (Patient_Account_No = 'bangor1010')
ORDER BY Payor_Effective_Date DESC, Patient_Account_No
SELECT TOP 1 * FROM #temp1
drop table #temp1
Try -
SELECT Patient_Account_No, Payor_Insurance_Id, Payor_Individual_Id, Payor_Effective_Date, Payor_Term_Date
FROM vw_Patient_Insurance v1
WHERE Payor_Term_Date IS NULL
AND Payor_Insurance_Id IN (SELECT MAX(v2.Payor_Insurance_Id) FROM vw_Patient_Insurance v2
WHERE v2.PayorAccount_No = v1.Payor_Account_No)
ORDER BY Payor_Effective_Date DESC, Patient_Account_No
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks! I got your to work first. As for the view, I'm stuck using it for now. I'm not too concerned because I'm only dealing with 2500-3500 records. Thanks for the tip, though.
Yep, you're quite right, for less than 10K rows it's not an issue :-) .
Open in new window