2nd highest date in data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

2nd highest date in data

Author

Topic

sqlnovice1
Starting Member

34 Posts

Posted - 2011-09-09 : 06:11:34

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.

Paul

Kristen
Test

22859 Posts

Posted - 2011-09-09 : 06:19:17

[code]

SELECT patient_id
FROM PS_TestForOnline AS T1
WHERE EXISTS
(
SELECT *
FROM PS_TestForOnline AS T2
WHERE T2.attendance_date > T1.attendance_date
AND DATEDIFF(Day, T1.attendance_date, T2.attendance_date) <= 7
)
[/code]
If someone comes in twice in a week they will appear twice in the list ... you can use SELECT DISTINCT to ignore the duplicates

Kristen
Test

22859 Posts

Posted - 2011-09-09 : 06:25:01

Sorry, that's not the output you wanted, this should be:


SELECT	patient_id , T1.attendance_date, T2.attendance_date AS [Previous_attendance_date]
FROM	PS_TestForOnline AS T1
	JOIN PS_TestForOnline AS T2
		 ON T2.attendance_date > T1.attendance_date
		AND DATEDIFF(Day, T1.attendance_date, T2.attendance_date) <= 7

sqlnovice1
Starting Member

34 Posts

Posted - 2011-09-09 : 06:31:57

Many Thanks for that speedy reply, how do i get the results to show the att_date and previous_att_date as per expected results.

PS there is a typo on the secong date for Joe Bloggs, should read 2011-04-03 00:00:00.000 and 2011-04-02 00:00:00.000

Should read as follows

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-02 00:00:00.000
frank smith 2011-04-08 00:00:00.000 2011-04-01 00:00:00.000

Kristen
Test

22859 Posts

Posted - 2011-09-09 : 06:40:34

"how do i get the results to show the att_date and previous_att_date as per expected results."

As per my second post? or is that not working for you?

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2011-09-09 : 06:41:30

--are u looking for this...

with pre(patient_id,attendance_date)
AS
(
select patient_id,attendance_date from #PS_TestForOnline
)
select a.patient_id,b.attendance_date,max(a.attendance_date) from #PS_TestForOnline a
join
pre b on a.patient_id = b.patient_id and a.attendance_date < b.attendance_date
and DATEDIFF(DD,a.attendance_date ,b.attendance_date) <=7
group by a.patient_id,b.attendance_date

Karthik
http://karthik4identity.blogspot.com/

sqlnovice1
Starting Member

34 Posts

Posted - 2011-09-09 : 06:45:07

Sorry Kristen, wrote reply before seeing your second reply.

works fine thank you

Thanks for everyuones help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources