Query help needed. (Sub-Query)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query help needed. (Sub-Query)

Author

Topic

lman
Starting Member

2 Posts

Posted - 2004-08-17 : 14:21:34

Here's the query I'm working on.

Select P.CorporateID, P.[Last Name], P.[First Name], L.[Admit Date],L.[Account Number] From Patient P
inner join PatientLogDetails L on P.CorporateID = L.CorporateID
Where (L.[Admit Date] >= '7/1/2004 00:00' And L.[Admit Date] < '7/31/2004 23:59')
and Exists (
Select 1 From PatientLogDetails M Where L.[CorporateID] = M.[CorporateID]
and L.[Admit Date] > M.[Admit Date]
and L.[Admit Date] < DateAdd(hh,72,M.[Admit Date]))
Group by L.[Account Number],P.CorporateID, P.[Last Name], P.[First Name], L.[Admit Date]
Order by P.[Last Name]

Two tables Patient (Demographics) and PatientLogDetails (Visit Information) (Simple Patient Log Database). What I'm after is any patient for the month that returned with-in 72 hours. The unique identifier for a patient is the CorporateID. Each time a patient visits the hospital they get a new record in the PatientLogDetails Table . So this query works fine, but what it returns is the last visit.

i.e. John Doe Visited the Hospital 3 time in July. The patientlogdetail record would have 3 records.

Admit Date | some field | some field |.....etc
1. 7/1/2004 00:00 | ..... | ......
2. 7/12/2004 04:30 | ...... |.......
3. 7/13/2004 00:00......BACK WITHIN 72 HOURS ....This is the returned record for John which is right.

Is there a way to return the previous record related to the one thats within 72 hours ? So that would return 2 and 3 (Above).

Thank you,
Lman

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-17 : 19:22:59

Instead of a sub-query you should probably use a join because I believe they're more efficient. You add the 72 hour condition in the JOIN clause.

SELECT L.CorporateID, L.AdmitDate
FROM PatientLogDetails L INNER JOIN PatientLogDetails L1 
             ON L.CorporateID = L.CorporateID AND DateDiff(h, L.AdmitDate, L1.AdmitDate) <= 72

I haven't tested it, but it should do what you need with some minor customisations.

HTH,
Tim

lman
Starting Member

2 Posts

Posted - 2004-08-19 : 16:32:25

Got it for those interested.

select Distinct OD.[Account Number],AB.[Last Name], AB.[First Name],OD.[Admit Date], OD.[Chief Complaint] from (
Select P.CorporateID CorporateID, P.[Last Name], P.[First Name], L.[Admit Date],L.[Account Number] From Patient P
inner join PatientLogDetails L on P.CorporateID = L.CorporateID
Where (L.[Admit Date] >= ''' + @dtFROM + '''And L.[Admit Date] < ''' + @dtTO + ''')
and Exists (
Select 1 From PatientLogDetails M Where L.[CorporateID] = M.[CorporateID]
and L.[Admit Date] > M.[Admit Date]
and L.[Admit Date] < DateAdd(hh,72,M.[Admit Date]))
Group by L.[Account Number],P.CorporateID, P.[Last Name], P.[First Name], L.[Admit Date]
) AB inner join PatientLogDetails OD On OD.CorporateID = AB.CorporateID
Where DateDiff(hh,OD.[Admit Date],AB.[Admit Date]) Between 0 and 72
Order by AB.[Last Name], AB.[First Name]

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2004-08-20 : 05:26:16

but still, arent joins more effecient? There seems to be a lot less code on the second post... Does it return the same data???

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources