?
Solved

SQL 2005 Query help

Posted on 2011-10-29
6
Medium Priority
?
275 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:gsgi
  • 3
  • 3
6 Comments
 
LVL 93

Accepted Solution

by:
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

Open in new window

0
 
LVL 93

Expert Comment

by:Patrick Matthews
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

Open in new window

0
 
LVL 13

Author Comment

by:gsgi
ID: 37050409
hmmm, much better than my approach.  I'll study it.  Thanks!
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 13

Author Comment

by:gsgi
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

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

:)
0
 
LVL 13

Author Closing Comment

by:gsgi
ID: 37050626
Fantastic!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question