We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

SQL last date per patient

Medium Priority
508 Views
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

Comment
Watch Question

Commented:
try
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

Commented:
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

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
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
INNER JOIN (
    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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Yep, you're quite right, for less than 10K rows it's not an issue :-) .
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.