SQL last date per patient

Posted on 2009-02-11
Medium Priority
Last Modified: 2012-05-06
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?
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

Open in new window

Question by:IntercareSupport

Expert Comment

ID: 23613792
SELECT     Patient_Account_No, Payor_Insurance_Id, Payor_Individual_Id, MAX(Payor_Effective_Date), Payor_Term_Date
FROM         vw_Patient_Insurance
WHERE     (Payor_Term_Date IS NULL) AND (Patient_Account_No = 'bangor1010')
ORDER BY Patient_Account_No

Open in new window

LVL 25

Expert Comment

ID: 23613870
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

Open in new window

LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 23614027
SELECT     Vpi.Patient_Account_No, Vpi.Payor_Insurance_Id, Vpi.Payor_Individual_Id,
           Vpi.Payor_Effective_Date, Vpi.Payor_Term_Date
into #temp1
FROM         vw_Patient_Insurance Vpi
    SELECT     Patient_Account_No, MAX(Payor_Effective_Date) AS Payor_Effective_Date
    FROM         vw_Patient_Insurance Vpi
    WHERE     (Payor_Term_Date IS NULL)
    GROUP BY   Patient_Account_No
) AS Pat_Last_Ins ON Pat_Last_Ins.Patient_Account_No = Vpi.Patient_Account_No AND
    Pat_Last_Ins.Payor_Effective_Date = Vpi.Payor_Effective_Date
WHERE     (Vpi.Payor_Term_Date IS NULL)
ORDER BY   Vpi.Patient_Account_no

--FYI, it would likely be much more efficient to go directly against the original data tables,
--       rather than using a view, if possible.

Author Closing Comment

ID: 31545662
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.
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23614729
Yep, you're quite right, for less than 10K rows it's not an issue :-) .

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question