SQL last date per patient

Posted on 2009-02-11
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
    LVL 6

    Expert Comment

    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

    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 68

    Accepted Solution

    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

    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 68

    Expert Comment

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

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now