December 22, 2010 at 3:07 am
Hi
I have a SQL Server database that holds patient information for the cancer department that I work in. I have a Patient table and Appointment table.
Patient (Pat_ID, Lastname, Firstname)
Appointment (Appt_ID, Date, Attended, FK_Pat_ID)
All the appointments for each patient are stored in the Appointment table. When a patient has attended and been seen by the doctor the Attended field (boolean) is marked true.
I am trying to do a join query so that I can see a list of selected patients together with the date last seen, as well as the next appointment date. It also must display the record if either of the 'Last seen' date or 'Next appt' date is blank / null.
Separately the queries will be something like this:
--the last attended appointment
SELECT Lastname, Firstname, MAX(Date) AS ‘Last seen’
FROM Patient
LEFT JOIN Appointment
ON Patient.Pat_ID = Appointment.Pat_ID
WHERE Date <= GETDATE() AND Attended = 1
--the next appointment
SELECT Lastname, Firstname, MIN(Date) AS ‘Next appt’
FROM Patient
LEFT JOIN Appointment
ON Patient.Pat_ID = Appointment.Pat_ID
WHERE Date >= GETDATE() AND Attended = 0
I am trying to do this al in one query using aliases, but not sure how to do the Where clause.
SELECT Lastname, Firstname, MAX(A1.Date) AS ‘Last seen’, MIN(A2.Date) AS ‘Next appt’
FROM Patient
LEFT JOIN Appointment A1
ON Patient.Pat_ID = A1.Pat_ID
LEFT JOIN Appointment A2
ON Patient.Pat_ID = A2.Pat_ID
--???? WHERE Date <= GETDATE() AND Attended = 1
Any help and advice will be greatly appreciated.
Thank you.
Kobus
December 22, 2010 at 3:41 am
This was removed by the editor as SPAM
December 22, 2010 at 3:44 am
If i understood you correctly, you need the Patient Details long with the last attended appointment and the immediate next appointment.
You can use the ROW_NUMBER() Function for the same
; WITH cte_Previous_Appointments AS
(
SELECTROW_NUMBER() OVER ( PARTITION BY P.Pat_ID ORDER BY A.Date DESC ) RN, P.LastName, P.FirstName,
A.Date
FROMPatient P
LEFT OUTER JOINAppointment A
ON P.Pat_ID = A.Pat_ID
AND A.Date <= GETDATE()
AND A.Attended = 1
), cte_Future_Appointments AS
(
SELECTROW_NUMBER() OVER ( PARTITION BY P.Pat_ID ORDER BY A.Date ASC ) RN, P.LastName, P.FirstName,
A.Date
FROMPatient P
LEFT OUTER JOINAppointment A
ON P.Pat_ID = A.Pat_ID
AND A.Date >= GETDATE()
AND A.Attended = 0
)
SELECTP.LastName, P.FirstName, P.Date AS 'Last Seen', A.Date AS 'Next Appointment'
FROMcte_Previous_Appointments P
INNER JOINcte_Future_Appointments F
ONP.Pat_ID = F.Pat_ID
ANDP.RN = 1 AND F.RN = 1
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 22, 2010 at 3:47 am
Hi Kobus,
This can be done in many ways, one simple method below
Input :
insert into patient values (1,'S','Jagan');
insert into patient values(2,'S','Madhan');
insert into patient values(3,'S','Vijay');
insert into patient values(4,'R','Ramu');
Insert into Appointment values(1, '2010-10-01', 1, 1);
Insert into Appointment values(2, '2010-11-01', 1, 1);
Insert into Appointment values(3, '2010-12-01', 0, 1);
Insert into Appointment values(4, '2010-10-01', 1, 2);
Insert into Appointment values(5, '2011-01-01', 0, 2);
Insert into Appointment values(6, '2010-10-01', 1, 3);
Insert into Appointment values(7, '2011-01-01', 0, 3);
Insert into Appointment values(8, '2010-11-01', 1, 4);
Insert into Appointment values(9, '2011-01-01', 1, 4);
Insert into Appointment values(10, '2011-02-01', 0, 4);
select * from patient
select * from appointment
SELECT Lastname, Firstname,
(select max(date) from appointment app where attended=1 and app.FK_Pat_ID=s.pat_id) AS "Last seen",
(select max(date) from appointment app where
attended=0 and app.FK_Pat_ID=s.pat_id) AS "Next App"
FROM Patient s
order by pat_id
Let me know whether is this helpful or not.
Thanks,
Jagan S.
December 22, 2010 at 4:35 am
Or the OUTER APPLY version
declare @Patient table
(
Pat_ID integer not null primary key,
LastName varchar(100),
FirstName varchar(100)
)
declare @Appointment table
(
Appt_ID integer identity not null primary key,
[Date] datetime,
Attended bit,
FK_Pat_ID integer
)
insert into @Patient (Pat_ID, LastName, FirstName)
select 1, 'Duck', 'Daffy' union all
select 2, 'Bunny', 'Bugs'
insert into @Appointment ([Date], Attended, FK_Pat_ID)
select '2010-10-10', 1, 1 union all
select '2010-10-11', 0, 1 union all
select '2010-10-20', 1, 1 union all
select '2010-12-30', 0, 1
select p.Pat_ID, p.LastName, p.FirstName, a1.Date, a2.Date
from @Patient p
outer apply (select top 1 [Date] from @Appointment a
where a.FK_Pat_Id = p.Pat_Id and a.Attended = 1 and a.Date <= GetDate()
order by a.Date desc) a1
outer apply (select top 1 [Date] from @Appointment a
where a.FK_Pat_Id = p.Pat_Id and a.Attended = 0 and a.Date > GetDate()
order by a.Date asc) a2
Oh and if you go with the GROUP BY... adding Pat_ID to the group by would be good. Doubt that LastName and FirstName are unique. So without it you would end up with merging several patients into one.
/T
December 22, 2010 at 4:41 am
Thank you all
As soon as I get to my development PC I shall try your solutions and will let you know how it went.
Kind regards.
Kobus
December 22, 2010 at 11:13 pm
CELKO (12/22/2010)
>> I have a SQL Server database that holds patient information for the cancer department that I work in. I have a Patients table and Appointments table. <<Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html
Why did you use singular names for the tables? They are sets and have more than one row, don't they?
Why does a patient_id change its name to fk_patient_id? This is wrong! A data element has one and only one name in a schema. We do not care HOW it is used; we want to know what it is.
CREATE TABLE Patients
(patient_id CHAR(15) NOT NULL PRIMARY KEY,
patient_last_name, VARCHAR(15) NOT NULL,
P.patient_first_name VARCHAR(15) NOT NULL);
DATE is a reserved word in SQL and too vague to ever be a column name. Your "attended" woudl be "attended_flg" under ISO-11179 rules. But we do not use flags in SQL; that was assembly language. You know when the appointment was and you know when (if wever) the patient got there. Why are you destroying data? Try SQL instead of Assembly language:
CREATE TABLE Appointments
(appointment_nbr INTEGER NOT NULL PRIMARY KEY,
appointment_date DATE NOT NULL
attendance_date DATE, -- NULL means no appearance
patient_id CHAR(15) NOT NULL
REFERENCES Patients (patient_id)
ON DELETE CASCADE
ON UPDATE CASCADE );
>> All the appointments for each patient are stored in the Appointments table. When a patient has attended and been seen by the doctor the Attended field (Boolean) is marked true. <<
No, we update the appearance date in RDBMS.
>> I am trying to do a query so that I can see a list of selected patients together with the date last seen, as well as the next appointment date. It also must display the record [sic:rows are not records] if either of the 'Last seen' date or 'Next appt' date is blank / NULL [blanks are not NULL]. <<
I dont know what doctors you go to but if i have an appointment i either go at that time or not. Its not like you can get an appointment and then serveral hours/days later go to the doctor. So why the heck would you want to store a date? All you need to know is if the patient used his appointment or not. Why waste all those extra bytes to store a date when... a bit would be enough.
You are not thinking in SQL yet; you got the basic words wrong. You also don't seem to know that the old Sybase/UNIX getdate() is replaced by the ANSI/ISO SQL CURRENT_TIMESTAMP today. Let's re-do you simple queries:
Which almost nobody uses so get over yourself.
-- previous appearance, if any
SELECT P.patient_last_name, P.patient_first_name,
MAX(A.appearance_date) AS previous_appearance_date
FROM Patients AS P
LEFT OUTER JOIN
Appointments AS A
ON P.patient_id = A.patient_id
GROUP BY P.patient_last_name, P.patient_first_name;
--the next appointment, if any
SELECT P.patient_last_name, P.patient_first_name,
MIN(appointment_date) AS next_appointment_date
FROM Patients AS P
LEFT OUTER JOIN
Appointments AS A
ON P.patient_id = A.patient_id
WHERE A.appearance_date IS NULL;
See how your silly flag destroyed data? Here is my untested guess:
SELECT P.patient_last_name, P.patient_first_name,
MAX(A1.appearance_date) AS previous_appearance_date,
MIN(A2.appointment_date) AS next_appointment_date
FROM Patients AS P
LEFT OUTER JOIN
(Appointments AS A1
INNER JOIN
Appointments AS A2
ON A1.patient_id = A2.patient_id
AND A1.appearance_date IS NOT NULL
AND A2.appearance_date IS NULL)
GROUP BY P.patient_last_name, P.patient_first_name;
The right DDL makes the DML easy.
And then you turn out some really crappy code. First of all. Grouping just by name = stupid. Its unlikely to be unique. So you have just bunched up several patients to one. Considering your ranting... thats weak/sloppy.
Second problem is that that code can really kill performance (as much as my OUTER APPLY actually) with the wrong data. So why would you as an "expert" use such a code?
There are many more things i would want to point out... however at this point... ignoring you is so much more effective.... ah crap... no ignore button 🙁
/T
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply