Query to retrieve patient history given a case 1

[TheBugSlayer]

Hi guys,

Worked all week-end and have very little gas left in the tank. I need help with a query. Here is the problem:
I have a
Case(CaseID, CaseNumber, VisitID,...),
Visit(VisitID, PatientID,...), and a
Patient(PatientID, ...) tables.
Given a list of cases (I concatenated them into a string in C# and I am using the string in the IN clause of my queries), I need to retrieve the "history" of the Patients associated with the Cases in the list. There is a 1-to-many relationship between between Patient and Visit as you can see. The history is all visits by the same patient, excluding the Visit generated by the Case in the list (I can remove this myself later). Any help to this tired brain is appreciated.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[markros]

What seems to be the problem?

select V.*, P.* from Visits V inner join Patients P
on V.PatientID = P.PatientID where not exists (select 1
from Case where Case in (List of cases here) and Case.VisitID = V.VisitID)

PluralSight Learning Library

 

[TheBugSlayer]

That returns nothing.

The only problem is I am so tired that every solution I come up with seems too complicated and only work half way.

Giving up for today. Resuming tomorrow. Thanks for trying.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

I think this might do the trick.

Select Visit.*
From   Visit
       Inner Join (
         Select PatientId
         From   Visit
                Inner Join (
                  Select VisitId
                  From   [Case]
                  Where  CaseId In ([!]YourList[/!])
                  ) as A
                  On Visit.VisitId = A.VisitID
         ) As A
         On Visit.PatientId = A.PatientId

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

It should work, but I think your problem may be in the list of codes if you're passing it as a parameter. If so, you need to split them into a table.

PluralSight Learning Library

 

[TheBugSlayer]

Hi all, thanks for your help. This is the ugly version that works so far:

select C.CaseNumber, V.PatientID from Case C
join Visit V on C.VisitID = V.VisitID
where V.PatientID IN 
(
    select top 100 percent PatientID from Visit
    where PatientID in (
        select V.PatientID from Case C
        join Visit V on C.VisitID = V.VisitID
        where C.CaseNumber in ('ABC..', 'DEF..')
    ) group by V.PatientID having count(V.ObjectID) > 1 order by V.PatientID 
) 
and C.CaseNumber not in ('ABC..', 'DEF..')
order by C.CaseNumber

I don' t like it...especially because I am using the list twice. Will try the other suggestions and let you know. Thanks.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[markros]

I think I somehow don't understand the requirement as George's and yours query don't match what you want.

PluralSight Learning Library

 

[TheBugSlayer]

I have cases- each time a patient goes to see doc, a Case and a Visit are created. The link between patient and case is through visit. Now I have a case (say patient went to the doc Oct 1), from that case I want to know what other visits that same patient had (say he went July 10 and Aug 10).
An illustration:

Case
CaseID VisitID
AAA 001
AAB 002
AAC 003
BAA 004
BAB 005
BAC 006
BAD 007

Visit
VisitID PatientID
001 PAT1
002 PAT1
003 PAT1
004 PAT2
005 PAT2
006 PAT2
007 PAT2


Now, for example, given cases AAA and BAB, the query should return

OrgCaseID CaseID VisitID PatID
AAA, AAA, 001, PAT1
AAA, AAB, 002, PAT1
AAA, AAC, 003, PAT1
BAB, BAB, 005, PAT2
BAB, BAA, 004, PAT2
BAB, BAC, 006, PAT2
BAB, BAD, 007, PAT2


I hope that' s clearer.

Thank you.




MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

Sample data makes things so much easier.

Declare @Case Table(CaseId VarChar(10), VisitId VarChar(10))

Insert Into @Case Values('AAA','001')
Insert Into @Case Values('AAB','002')
Insert Into @Case Values('AAC','003')
Insert Into @Case Values('BAA','004')
Insert Into @Case Values('BAB','005')
Insert Into @Case Values('BAC','006')
Insert Into @Case Values('BAD','007')

Declare @Visit Table(VisitId VarChar(10), PatientId VarChar(10))
Insert Into @Visit Values('001','PAT1')
Insert Into @Visit Values('002','PAT1')
Insert Into @Visit Values('003','PAT1')
Insert Into @Visit Values('004','PAT2')
Insert Into @Visit Values('005','PAT2')
Insert Into @Visit Values('006','PAT2')
Insert Into @Visit Values('007','PAT2')

Select A.CaseId, C.CaseId, V2.VisitId, V2.PatientId
From   @Visit V
       Inner Join (
         Select VisitId, CaseId
         From   @Case
         Where  CaseId In ('AAA','BAB')
         ) As A
         On V.VisitId = A.VisitId
       Inner Join @Visit V2
         On V.PatientId = V2.PatientId
       Inner Join @Case C
         On V2.VisitId = C.VisitId

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Hurray George!!!
Saved me a couple of neurones. One task down in the list of X things to do.

Thanks a million

PS:
I read somewhere that multitasking is actually a bad thing. Yet, at every job interview...

"Habitual multitasking may condition the brain to an overexcited state, making it difficult to focus even when you want to"

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

I'm glad I could help

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom