SQL query help...

[hharry]

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_QUESTIO N] [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_QUESTIO N: 2001
NUM_OF_VISITS: 20

PATIENT_ID: 2
YEAR_IN_QUESTIO N: 2000
NUM_OF_VISITS: 50

Thanks in advance!

[Ed Murphy]

Re: SQL query help...

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_QUESTIO N] [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_QUESTIO N: 2001
NUM_OF_VISITS: 20
>
PATIENT_ID: 2
YEAR_IN_QUESTIO N: 2000
NUM_OF_VISITS: 50

Here are two ways to do it.

select t1.Patient_ID, t1.Year_in_Ques tion, t1.Num_of_Visit s
from Patient_Visits_ by_Year t1
where t1.Num_of_Visit s = (
select max(Num_of_Visi ts)
from Patient_Visits_ by_Year t2
where t2.Patient_ID = t1.Patient_ID
)

select t1.Patient_ID, t1.Year_in_Ques tion, t1.Num_of_Visit s
from Patient_Visits_ by_Year t1
left join Patient_Visits_ by_Year t2
on t1.Patient_ID = t2.Patient_ID
and t1.Year_in_Ques tion < t2.Year_in_Ques tion
where t2.Year_in_Ques tion is null

[--CELKO--]

Re: SQL query help...

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.