I have a table with multiple entries for patient id numbers. Basically a patient transitions from one program to another keeping the same id number. I need to know how many patients have transitioned and I am having trouble writing the code. I have the following columns: patid and patgroup. So the data is like this:
I want to do something like:

select count (patid)
from
where patgroup <this is where I need help> where A became B

SELECT patid, count(patid)
FROM patients
GROUP BY patid;

to extract number of participations in groups for each id number

SELECT patid, count(patid)
FROM patients
GROUP BY patid
HAVING count(patid) > 1;

to show only those that transitioned at least once.

I suppose there are no duplicates in your data, e.g.
id group
1 A
1 A

Hope this helps,
Dimitar

Hi Dimitar,

That is great, but now I am stuck again. Your code gave me the patid number and count for those patid that showed up more than once in my table. But here is the question, how many patid went from A or C to B only?

PatId PatGroup
1 A
1 B
2 C
2 B
3 C
3 C

Using the example above the answer is 2, PatId 1 and 2 transitioned to B. How do I write that in SQL?

Thanks so much!
LB

Hi,

I will think about it tomorrow.
But here is a question:

id group
1 A
1 B

How do you know that the patient transited from A to B, and not from B to A?
Tables are unordered sets, unless there is a dedicated column that specifies which row is "after" or "before" another.

Also, what DB engine are you using?

Dimitar

Dimitar,

I have a third column which shows date, so I know participants when participants transitioned from A to B. I work in SQL server and am using SQL Query Analyzer version 8.00.194

Thanks,
LB

LB,

Here is what came to mind first:

SELECT p1.PatId,p1.PatGroup FROM patients AS p1 WHERE p1.PatGroup='B'
AND
 p1.PatId IN (
            SELECT p2.PatId FROM patients AS p2 WHERE (p2.PatGroup='A' OR p2.PatGroup='C') AND (p2.DateEntered < p1.DateEntered)
)
;

Did it work?

Dimitar

SELECT count(*) FROM (
SELECT p1.PatId,p1.PatGroup FROM patients AS p1 WHERE p1.PatGroup='B'
AND
 p1.PatId IN (
            SELECT p2.PatId FROM patients AS p2 WHERE (p2.PatGroup='A' OR p2.PatGroup='C') AND (p2.DateEntered < p1.DateEntered)
)) AS dt
;

to show the count.

Hi Dimitar...YEAH!

I think it worked. I had to change part of it but you were so much help. I am new to SQL, like I just learned it two weeks ago. I'm not in IT so the hard stuff, like subqueries, are soo hard for me. You are a great resource and I would love to get an email address for you. This is the query that I used:

USE xx
SELECT p1.patid, p1.patgroup
FROM patients AS p1
WHERE p1.patgroup = 'B' AND p1.patid IN
(SELECT p2.patid
FROM patients AS p2
WHERE p2.billprogramid IN ('A', 'C')

Thank you!
LB