September 9, 2011 at 3:59 am
Hi
I have been assigned a task at work in a hospital. I have to look at patients who atttend accident and emergency and then attend again within 7 days. Unfortunatly the attendance ID's do not run in any order so the only data items i have are the patient_id and their attendance_date.
sample data
-------------------------------------------------------------------------------------
CREATE TABLE PS_TestForOnline
(
patient_id NVARCHAR (20),
attendance_date datetime ,
);
INSERT INTO PS_TestForOnline
VALUES('joe bloggs','2011-04-01 00:00' );
INSERT INTO PS_TestForOnline
VALUES('joe bloggs','2011-04-02 00:00' );
INSERT INTO PS_TestForOnline
VALUES('joe bloggs','2011-04-03 00:00');
INSERT INTO PS_TestForOnline
VALUES('joe bloggs','2011-04-25 00:00' );
INSERT INTO PS_TestForOnline
VALUES('frank smith','2011-04-01 00:00' );
INSERT INTO PS_TestForOnline
VALUES('frank smith','2011-04-08 00:00' );
select * from PS_TestForOnline
drop table PS_TestForOnline
-------------------------------------------------------------------------------------
Ideally i would like to be able to see the rows where a ptient has attended again within 7 days along with the previous attendance date
-------------------------------------------------------------------------------------
--Expected results--
CREATE TABLE PS_TestForOnline_expected_answer
(
patient_id NVARCHAR (20),
attendance_date datetime ,
Previous_attendance_date datetime ,
);
INSERT INTO PS_TestForOnline_expected_answer
VALUES('joe bloggs','2011-04-02 00:00' ,'2011-04-01 00:00' );
INSERT INTO PS_TestForOnline_expected_answer
VALUES('joe bloggs','2011-04-03 00:00','2011-04-03 00:00' );
INSERT INTO PS_TestForOnline_expected_answer
VALUES('frank smith','2011-04-08 00:00','2011-04-01 00:00' );
select * from PS_TestForOnline_expected_answer
drop table PS_TestForOnline_expected_answer
Thanks in advance for any advice and/or help.
September 9, 2011 at 4:16 am
I've written the query to represent how I think you've explained your task rather than your expected result-set that you provided.
;WITH CTE AS (
SELECT patient_id, attendance_date,
ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY attendance_date DESC) AS rn
FROM PS_TestForOnline)
SELECT a.patient_id, a.attendance_date, b.attendance_date
FROM CTE a
LEFT OUTER JOIN CTE b ON a.patient_id = b.patient_id AND a.rn=b.rn-1
WHERE b.patient_id IS NOT NULL
This returns the following: -
patient_id attendance_date attendance_date
-------------------- ----------------------- -----------------------
frank smith 2011-04-08 00:00:00.000 2011-04-01 00:00:00.000
joe bloggs 2011-04-25 00:00:00.000 2011-04-03 00:00:00.000
joe bloggs 2011-04-03 00:00:00.000 2011-04-02 00:00:00.000
joe bloggs 2011-04-02 00:00:00.000 2011-04-01 00:00:00.000
If that's not right, can you explain your result-set please?
I don't understand why the attendance date "2011-04-03" has a second attendance date of "2011-04-03" (see below for what your code produces as the expected result set)
patient_id attendance_date Previous_attendance_date
-------------------- ----------------------- ------------------------
joe bloggs 2011-04-02 00:00:00.000 2011-04-01 00:00:00.000
joe bloggs 2011-04-03 00:00:00.000 2011-04-03 00:00:00.000
frank smith 2011-04-08 00:00:00.000 2011-04-01 00:00:00.000
September 9, 2011 at 4:22 am
My mistake, I have put the wrong date in the expected reuslts.
should read as follows.
your solution does the job but i need to exclude any rows where the datediff bewtween the 2 dates is > 7 days??
Thanks for your speedy reply
--Expected results--
CREATE TABLE PS_TestForOnline_expected_answer
(
patient_id NVARCHAR (20),
attendance_date datetime ,
Previous_attendance_date datetime ,
);
INSERT INTO PS_TestForOnline_expected_answer
VALUES('joe bloggs','2011-04-02 00:00' ,'2011-04-01 00:00' );
INSERT INTO PS_TestForOnline_expected_answer
VALUES('joe bloggs','2011-04-03 00:00','2011-04-02 00:00' );
INSERT INTO PS_TestForOnline_expected_answer
VALUES('frank smith','2011-04-08 00:00','2011-04-01 00:00' );
select * from PS_TestForOnline_expected_answer
drop table PS_TestForOnline_expected_answer
September 9, 2011 at 4:28 am
;WITH CTE AS (
SELECT patient_id, attendance_date,
ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY attendance_date DESC) AS rn
FROM PS_TestForOnline)
SELECT a.patient_id, a.attendance_date, b.attendance_date
FROM CTE a
LEFT OUTER JOIN CTE b ON a.patient_id = b.patient_id AND a.rn=b.rn-1
WHERE b.patient_id IS NOT NULL
AND DATEDIFF(DAY,b.attendance_date,a.attendance_date) <=7
September 9, 2011 at 4:28 am
;WITH OrderedData AS (
SELECT
patient_id,
attendance_date,
rn = ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY attendance_date)
FROM PS_TestForOnline
)
SELECT *
FROM OrderedData v1 -- first visit
LEFT JOIN OrderedData v2
ON v2.patient_id = v1.patient_id
AND v2.rn = 2 -- second visit
AND v2.attendance_date < v1.attendance_date+7 -- check this
WHERE v1.rn = 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 9, 2011 at 4:38 am
Many, Many Thanks
Works a treat.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply