SQL 2005 Query help

I need help with this query for SQL 2005

Patient   Date       Code
1             1/1/80    10
1             1/1/81    20
1             1/1/82    50
1             1/1/83    10
2             1/1/80    20
2             1/1/81    30
2             1/1/82    40
3             1/1/80    10
3             1/1/81    50
4             1/1/80    10
4             1/1/81    50
4             1/1/82    20
4             1/1/83    50
4             1/1/84    10

Query is:  What is the earliest date occurrence of a code 10 or 20 for each patient that occurs after the last date of a code 50 (code 50 may or may not exist in the table of codes for each patient)

Answer for above:
1 1/1/83
2 1/1/80
4 1/1/84

I appreciate the time it takes to reply!

Thanks,
gsgi
LVL 13
gsgiAsked:
Who is Participating?
 
Patrick MatthewsCommented:
THis seems to be working for me:



CREATE TABLE SomeTable(Patient int, [Date] datetime, Code int)

INSERT INTO SomeTable (Patient, [Date], Code)
SELECT 1, '1980-01-01', 10 UNION ALL
SELECT 1, '1981-01-01', 20 UNION ALL
SELECT 1, '1982-01-01', 50 UNION ALL
SELECT 1, '1983-01-01', 10 UNION ALL
SELECT 2, '1980-01-01', 20 UNION ALL
SELECT 2, '1981-01-01', 30 UNION ALL
SELECT 2, '1982-01-01', 40 UNION ALL
SELECT 3, '1980-01-01', 10 UNION ALL
SELECT 3, '1981-01-01', 50 UNION ALL
SELECT 4, '1980-01-01', 10 UNION ALL
SELECT 4, '1981-01-01', 50 UNION ALL
SELECT 4, '1982-01-01', 20 UNION ALL
SELECT 4, '1983-01-01', 50 UNION ALL
SELECT 4, '1984-01-01', 10

SELECT t2.Patient, MIN(t2.[Date]) AS MinDate
FROM SomeTable t2
WHERE (t2.Code = 10 OR t2.Code = 20) AND t2.[Date] > COALESCE(
    (SELECT MAX(t1.[Date])
    FROM SomeTable t1
    WHERE t1.Code = 50 AND t1.Patient = t2.Patient
    GROUP BY t1.Patient), 0)
GROUP BY t2.Patient

DROP TABLE SomeTable

Open in new window

0
 
Patrick MatthewsCommented:
ANother way:


CREATE TABLE SomeTable(Patient int, [Date] datetime, Code int)

INSERT INTO SomeTable (Patient, [Date], Code)
SELECT 1, '1980-01-01', 10 UNION ALL
SELECT 1, '1981-01-01', 20 UNION ALL
SELECT 1, '1982-01-01', 50 UNION ALL
SELECT 1, '1983-01-01', 10 UNION ALL
SELECT 2, '1980-01-01', 20 UNION ALL
SELECT 2, '1981-01-01', 30 UNION ALL
SELECT 2, '1982-01-01', 40 UNION ALL
SELECT 3, '1980-01-01', 10 UNION ALL
SELECT 3, '1981-01-01', 50 UNION ALL
SELECT 4, '1980-01-01', 10 UNION ALL
SELECT 4, '1981-01-01', 50 UNION ALL
SELECT 4, '1982-01-01', 20 UNION ALL
SELECT 4, '1983-01-01', 50 UNION ALL
SELECT 4, '1984-01-01', 10

SELECT t2.Patient, MIN(t2.[Date]) AS MinDate
FROM SomeTable t2 INNER JOIN
    (SELECT t1.Patient, MAX(CASE WHEN t1.Code = 50 THEN t1.[Date] ELSE 0 END) AS Date50
    FROM SomeTable t1
    GROUP BY t1.Patient) z ON t2.Patient = z.Patient AND t2.[Date] > z.Date50
GROUP BY t2.Patient

DROP TABLE SomeTable

Open in new window

0
 
gsgiAuthor Commented:
hmmm, much better than my approach.  I'll study it.  Thanks!
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
gsgiAuthor Commented:
In example 1, why is the "GROUP BY t1.Patient" needed?

I feel a little dumb asking, but it has been many months since I had to write sql queries so I am a little rusty I guess...  oh maybe it is required because you used an aggregate function ?

thanks,
gsgi
0
 
Patrick MatthewsCommented:
That is correct: since I am using aggregate functions, the GROUP BY is required.

:)
0
 
gsgiAuthor Commented:
Fantastic!
0
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.

All Courses

From novice to tech pro — start learning today.