Solved

# SQL 2005 Query help

Posted on 2011-10-29
Medium Priority
275 Views
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)

1 1/1/83
2 1/1/80
4 1/1/84

I appreciate the time it takes to reply!

Thanks,
gsgi
0
Question by:gsgi
• 3
• 3

LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 37050337
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
``````
0

LVL 93

Expert Comment

ID: 37050369
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
``````
0

LVL 13

Author Comment

ID: 37050409
hmmm, much better than my approach.  I'll study it.  Thanks!
0

LVL 13

Author Comment

ID: 37050469
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

LVL 93

Expert Comment

ID: 37050495
That is correct: since I am using aggregate functions, the GROUP BY is required.

:)
0

LVL 13

Author Closing Comment

ID: 37050626
Fantastic!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
###### Suggested Courses
Course of the Month12 days, 15 hours left to enroll