471,082 Members | 837 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,082 software developers and data experts.

SQL query help...

All,

I have the following table:

CREATE TABLE [PATIENT_VISITS_BY_YEAR] (
[ROW_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PATIENT_ID] [int] NOT NULL ,
[YEAR_IN_QUESTION] [int] NOT NULL ,
[NUM_OF_VISITS] [int] NOT NULL
) ON [PRIMARY]
GO

With rows:

INSERT INTO PATIENT_VISITS_BY_YEAR
VALUES (1, 2000, 10)

INSERT INTO PATIENT_VISITS_BY_YEAR
VALUES (1, 2001, 20)

INSERT INTO PATIENT_VISITS_BY_YEAR
VALUES (2, 2000, 50)

INSERT INTO PATIENT_VISITS_BY_YEAR
VALUES (2, 2001, 25)

I need to return the a single row for each patient, displaying the max
number of visits and the year those visits occurred:

e.g.

PATIENT_ID: 1
YEAR_IN_QUESTION: 2001
NUM_OF_VISITS: 20

PATIENT_ID: 2
YEAR_IN_QUESTION: 2000
NUM_OF_VISITS: 50

Thanks in advance!

May 4 '07 #1
2 1438
hharry wrote:
I have the following table:

CREATE TABLE [PATIENT_VISITS_BY_YEAR] (
[ROW_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PATIENT_ID] [int] NOT NULL ,
[YEAR_IN_QUESTION] [int] NOT NULL ,
[NUM_OF_VISITS] [int] NOT NULL
) ON [PRIMARY]
GO

With rows:

INSERT INTO PATIENT_VISITS_BY_YEAR
VALUES (1, 2000, 10)

INSERT INTO PATIENT_VISITS_BY_YEAR
VALUES (1, 2001, 20)

INSERT INTO PATIENT_VISITS_BY_YEAR
VALUES (2, 2000, 50)

INSERT INTO PATIENT_VISITS_BY_YEAR
VALUES (2, 2001, 25)

I need to return the a single row for each patient, displaying the max
number of visits and the year those visits occurred:

e.g.

PATIENT_ID: 1
YEAR_IN_QUESTION: 2001
NUM_OF_VISITS: 20

PATIENT_ID: 2
YEAR_IN_QUESTION: 2000
NUM_OF_VISITS: 50
Here are two ways to do it.

select t1.Patient_ID, t1.Year_in_Question, t1.Num_of_Visits
from Patient_Visits_by_Year t1
where t1.Num_of_Visits = (
select max(Num_of_Visits)
from Patient_Visits_by_Year t2
where t2.Patient_ID = t1.Patient_ID
)

select t1.Patient_ID, t1.Year_in_Question, t1.Num_of_Visits
from Patient_Visits_by_Year t1
left join Patient_Visits_by_Year t2
on t1.Patient_ID = t2.Patient_ID
and t1.Year_in_Question < t2.Year_in_Question
where t2.Year_in_Question is null
May 4 '07 #2
It is NOT a table at all; it has NO key and it has an IDENTITY (ugh!)
column. If you had used a relational design, ISO-11179 naming rules
etc. instead of this, would it look like this?

CREATE TABLE Patient_Visits_By_Years
(patient_id INTEGER NOT NULL
REFERENCES Patients (patient_id),
visit_year INTEGER NOT NULL
CHECK (visit_year BEYWEEN 1900 AND 2007),
visit_cnt INTEGER NOT NULL
CHECK (visit_cnt 0),
PRIMARY KEY (patient_id, visit_year));
>I need to return a single row for each patient; displaying the maximum number of visits and the year those visits occurred <<
SELECT VY1.patient_id, VY1.visit_year, VY1.visit_cnt
FROM Patient_Visits_By_Year AS VY1
WHERE VY1.visit_cnt
= (SELECT MAX(visits_cnt)
FROM Patient_Visits_By_Year AS VY2
WHERE VY2.patient_id = VY1.patient_id);

Learn the right conventions and ANSI/ISO Standards or you will always
be writing dialect and not SQL.

May 4 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by netpurpose | last post: by
7 posts views Thread by Simon Bailey | last post: by
36 posts views Thread by Liam.M | last post: by
4 posts views Thread by Doris | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.