?
Solved

Need results returned based on prior records

Posted on 2009-07-08
5
Medium Priority
?
188 Views
Last Modified: 2013-11-26
I have a query that returns a list of patients including a discharge date, admit date, account number and drg.  It currently lists all patients.  The unique identifier to the patient is the unit number.  The account number is different every time they come in.  I need to know for each patient - if the admitdate is within 30 days of the last disdate and only if the drg was the same for both account numbers.  The query below is very basic - only showing the fields and their tables. I have no idea how to do this....  I've attached some sample data and shaded in the ones that would need to be returned from the query.  
Basically we are trying to find out which patients came back within 30 days for the same thing.

I am writing this report in Visual Studio with SQL Server 2005
Thanks!
SELECT  AbsPatDischarges.DisDate, AbsPat.UnitNumber, AbsPat.AccountNumber, AbsPatDrgData.Drg, AbsPat.PtStatus, AdmPatientFile.AdmitDate
FROM     AbsPat INNER JOIN
               AbsPatDischarges ON AbsPat.UrnABSPAT = AbsPatDischarges.UrnABSPAT INNER JOIN
               AbsPatDrgData ON AbsPatDischarges.UrnABSPAT = AbsPatDrgData.UrnABSPAT INNER JOIN
               AdmPatientFile ON AdmPatientFile.UrnADMPAT = AbsPat.AdmUrn
WHERE  (AbsPat.PtStatus LIKE 'in')
ORDER BY AbsPat.UnitNumber, AbsPatDischarges.DisDate

Open in new window

30DAY-SAMPLE-DATA.xls
0
Comment
Question by:palmer9
  • 4
5 Comments
 
LVL 53

Expert Comment

by:Mark Wills
ID: 24811847
Might be a group by type problem where you need to check where count(*) is > 1 as a subquery - or maybe an inline query where date is within 30 days...

So, need to be looking at rows where account numbers are same, drg are same, and dates within that 30 day requirement.

Hmmmm....

Maybe as simple as


SELECT  AbsPatDischarges.DisDate, AbsPat.UnitNumber, AbsPat.AccountNumber, AbsPatDrgData.Drg, AbsPat.PtStatus, AdmPatientFile.AdmitDate
FROM     AbsPat INNER JOIN
               AbsPatDischarges ON AbsPat.UrnABSPAT = AbsPatDischarges.UrnABSPAT INNER JOIN
               AbsPatDrgData ON AbsPatDischarges.UrnABSPAT = AbsPatDrgData.UrnABSPAT INNER JOIN
               AdmPatientFile ON AdmPatientFile.UrnADMPAT = AbsPat.AdmUrn
WHERE  (AbsPat.PtStatus LIKE 'in')
AND EXISTS ( select NULL from FROM     AbsPat  AP
               INNER JOIN AbsPatDischarges APD ON AP.UrnABSPAT = APD.UrnABSPAT
               INNER JOIN AbsPatDrgData APDD ON APD.UrnABSPAT = APDD.UrnABSPAT
               INNER JOIN AdmPatientFile APF ON APF.UrnADMPAT = AP.AdmUrn
               WHERE ApsPat.AccountNumber = AP.AccountNumber
               and AbsPatDrgData.Drg = APDD.Drg
               and AdmPatientFile.AdmitDate < APD.DisDate + 30)

ORDER BY AbsPat.UnitNumber, AbsPatDischarges.DisDate



0
 
LVL 53

Expert Comment

by:Mark Wills
ID: 24811962
Instead of :

 and AdmPatientFile.AdmitDate < APD.DisDate + 30)

Use :

 and AdmPatientFile.AdmitDate between APD.DisDate and APD.DisDate + 30)
0
 
LVL 53

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 24811990
One more time...



SELECT AbsPatDischarges.DisDate, AbsPat.UnitNumber, AbsPat.AccountNumber, AbsPatDrgData.Drg, AbsPat.PtStatus, AdmPatientFile.AdmitDate
FROM AbsPat INNER JOIN
AbsPatDischarges ON AbsPat.UrnABSPAT = AbsPatDischarges.UrnABSPAT INNER JOIN
AbsPatDrgData ON AbsPatDischarges.UrnABSPAT = AbsPatDrgData.UrnABSPAT INNER JOIN
AdmPatientFile ON AdmPatientFile.UrnADMPAT = AbsPat.AdmUrn
WHERE (AbsPat.PtStatus LIKE 'in')
AND EXISTS ( select NULL from FROM AbsPat AP
                          INNER JOIN AbsPatDischarges APD ON AP.UrnABSPAT = APD.UrnABSPAT
                          INNER JOIN AbsPatDrgData APDD ON APD.UrnABSPAT = APDD.UrnABSPAT
                          INNER JOIN AdmPatientFile APF ON APF.UrnADMPAT = AP.AdmUrn
                          WHERE ApsPat.UnitNumber = AP.UnitNumber
                          and AbsPatDrgData.Drg = APDD.Drg
                          and AbsPatDischarges.DisDate <> APD.disdate
                          and ApsPat.AccountNumber <> AP.AccountNumber -- Think this differentiates the two visits
                          and AdmPatientFile.AdmitDate between APD.DisDate and APD.DisDate + 30)

ORDER BY AbsPat.UnitNumber, AbsPatDischarges.DisDate

0
 

Author Closing Comment

by:palmer9
ID: 31601295
Perfect - Thank you so much!!!
0
 
LVL 53

Expert Comment

by:Mark Wills
ID: 24814459
A pleasure - happy to help :)
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
An ASP.NET Web Form User Control is not newly introduced in ASP.NET. In fact, it was an old technology yet still playing a role to generate web content, especially when we want to use it to have a better and easy way to control part of the web conte…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

568 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