Find last appointment before admission to hospital

Question

Find last appointment before admission to hospital

NineIron

SSChampion

Points: 12536
More actions
October 14, 2014 at 7:23 am

#397500

Below is the code to create a table of hospital admissions along with the patient's appointments with their PCP. I need to find the date of the last appointment before the AdmitDateTime then, the first appointment date after the DischargeDateTime. Any thoughts?
create table TEST
(
UnitNumber varchar(6),
AdmitDateTime datetime,
DischargeDateTime datetime,
AppointmentDateTime datetime
)
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2009-03-06 09:15:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2009-07-06 09:00:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2009-08-05 13:30:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2009-09-15 12:00:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2009-11-02 13:45:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2010-01-15 09:15:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2010-07-15 08:45:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2010-09-21 12:30:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2011-01-03 09:30:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2011-07-07 09:00:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2011-07-19 13:00:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2011-10-06 13:20:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2012-01-10 13:10:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2012-02-13 12:10:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2012-07-10 14:10:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2012-08-17 10:30:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2012-09-17 12:30:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2012-11-20 14:20:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2012-12-13 11:15:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2013-05-20 10:35:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2013-07-10 09:20:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2013-09-10 09:15:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2014-01-30 12:05:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2014-06-03 12:00:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2014-07-16 08:35:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2014-08-22 11:20:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '800325','2014-08-06 16:03:00.000','2014-08-07 15:30:00.000','2014-09-05 09:35:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '751771','2014-08-14 08:11:00.000','2014-08-15 17:25:00.000','2013-06-12 11:35:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '690789','2014-08-20 16:19:00.000','2014-08-21 12:30:00.000','2009-09-01 13:30:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '690789','2014-08-20 16:19:00.000','2014-08-21 12:30:00.000','2010-07-13 11:45:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '690789','2014-08-20 16:19:00.000','2014-08-21 12:30:00.000','2011-10-24 13:30:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '690789','2014-08-20 16:19:00.000','2014-08-21 12:30:00.000','2011-11-09 14:00:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '690789','2014-08-20 16:19:00.000','2014-08-21 12:30:00.000','2011-11-18 09:50:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '690789','2014-08-20 16:19:00.000','2014-08-21 12:30:00.000','2012-10-15 10:20:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '690789','2014-08-20 16:19:00.000','2014-08-21 12:30:00.000','2013-02-20 14:40:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '690789','2014-08-20 16:19:00.000','2014-08-21 12:30:00.000','2013-02-25 08:50:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '690789','2014-08-20 16:19:00.000','2014-08-21 12:30:00.000','2013-04-11 13:20:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '690789','2014-08-20 16:19:00.000','2014-08-21 12:30:00.000','2013-05-08 11:05:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '690789','2014-08-20 16:19:00.000','2014-08-21 12:30:00.000','2013-11-13 13:20:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '690789','2014-08-20 16:19:00.000','2014-08-21 12:30:00.000','2014-01-13 15:35:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '690789','2014-08-20 16:19:00.000','2014-08-21 12:30:00.000','2014-04-11 11:05:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '690789','2014-08-20 16:19:00.000','2014-08-21 12:30:00.000','2014-04-24 12:30:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '690789','2014-08-20 16:19:00.000','2014-08-21 12:30:00.000','2014-06-27 10:05:00.000')
insert into TEST(UnitNumber, AdmitDateTime, DischargeDateTime, AppointmentDateTime) values( '690789','2014-08-20 16:19:00.000','2014-08-21 12:30:00.000','2014-09-15 13:40:00.000')

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

It seems weird that you have appointment dates in the same table as your admissions. I hope that your real data doesn't looks like that.

Here's an option based on what you posted.

SELECT UnitNumber,

AdmitDateTime,

DischargeDateTime,

MAX(CASE WHEN AppointmentDateTime < AdmitDateTime THEN AppointmentDateTime END) LastBeforeAdmit,

MIN(CASE WHEN AppointmentDateTime > DischargeDateTime THEN AppointmentDateTime END) FirstAfterDischarge

FROM TEST

GROUP BY UnitNumber, AdmitDateTime, DischargeDateTime;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

NineIron SSChampion Points: 12536 More actions · Answer 2

This works fine. The admission data and appointment data come from different databases. I pulled that information together then, asked you to help with the final step.

Thanx again.