Find next date after a given date

Question

Find next date after a given date

NineIron

SSChampion

Points: 12529
More actions
September 22, 2016 at 9:35 am

#330443

I'm trying to find the next appointment, if any, after a patient "no shows". Here is the code to create a table of no show appointments and another table of future appointments for those patients. How can I list the no show appointments along with the next future appointment? There may be more that one no show per patient.
create table #NoShow
(
MRN varchar(10),
ApptDt datetime
)
insert into #NoShow(MRN, ApptDt) values('128772','2015-08-21 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('128848','2016-03-15 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('128848','2016-04-22 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('220598','2016-01-28 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('220598','2016-08-18 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('226109','2016-03-10 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('226109','2016-04-07 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('226109','2016-07-26 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('229759','2015-01-06 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('229759','2015-03-30 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('229759','2015-04-30 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('229759','2015-11-04 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('229786','2016-07-28 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('270108','2015-11-27 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('271379','2015-06-01 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('271379','2015-06-08 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('271379','2015-08-10 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('271379','2016-05-19 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('271379','2016-06-30 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('279601','2015-06-04 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('300723','2016-08-22 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('300807','2015-04-13 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('300807','2015-05-19 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('300807','2015-11-16 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('308127','2016-09-09 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('308488','2015-04-06 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('308696','2015-01-29 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('321916','2015-07-01 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('321916','2016-05-20 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('325441','2016-04-20 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('325441','2016-07-08 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('325464','2016-05-12 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('326249','2015-08-24 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('326926','2015-06-25 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('326926','2015-08-27 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('327118','2016-06-10 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('328701','2015-05-08 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('328701','2016-07-21 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('329453','2016-02-10 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('330229','2015-12-28 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('330421','2015-03-06 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('330421','2015-06-02 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('331253','2016-02-17 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('331974','2015-07-30 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('331974','2015-08-21 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('332994','2016-04-14 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('332994','2016-06-27 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('334579','2016-07-21 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('335418','2016-04-25 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('336320','2015-05-29 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('336323','2016-09-06 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('337635','2015-02-13 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('337635','2015-12-31 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('337721','2015-08-31 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('339406','2015-09-14 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('341847','2015-01-19 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('341847','2015-04-13 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('341847','2015-11-12 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('344057','2015-04-17 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('344057','2015-06-18 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('344057','2015-10-15 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('344057','2016-01-13 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('344057','2016-04-21 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('344057','2016-04-25 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('344057','2016-05-23 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('344057','2016-07-11 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('344057','2016-08-05 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('344977','2015-11-30 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('344977','2016-02-11 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('345001','2015-03-11 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('345001','2015-03-18 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('345001','2015-07-14 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('345001','2016-04-11 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('345001','2016-05-12 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('345026','2015-06-09 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('345026','2015-07-14 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('345026','2015-12-01 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('345188','2015-01-12 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('345188','2016-03-25 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('345188','2016-08-22 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('349976','2016-07-20 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('350298','2015-12-21 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('350298','2016-05-02 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('350315','2015-03-27 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('350315','2016-09-19 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('350346','2016-08-05 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('350460','2015-06-04 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('350460','2015-09-04 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('350460','2015-09-18 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('351124','2015-05-19 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('351212','2016-08-15 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('352145','2016-04-06 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('352759','2015-02-19 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('352759','2015-08-27 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('352759','2015-09-03 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('352759','2016-02-18 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('352858','2015-01-19 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('352858','2015-02-24 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('352858','2015-03-13 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('352864','2016-02-23 00:00:00.000')
create table #FutureAppts
(
MRN varchar(10),
ApptDt datetime
)
insert into #FutureAppts(MRN, ApptDt) values('128772','2015-11-10 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('128772','2015-12-04 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('128772','2016-04-11 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('128848','2016-09-20 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('220598','2016-02-03 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('220598','2016-04-25 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('220598','2016-05-31 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('226109','2016-06-22 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('229759','2015-09-09 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('229759','2015-10-07 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('229759','2015-11-24 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('229759','2016-01-20 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('229759','2016-03-11 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('229759','2016-08-04 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('229786','2016-09-09 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('270108','2015-12-01 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('270108','2016-03-11 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('270108','2016-04-01 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('270108','2016-06-29 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('270108','2016-07-18 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('270108','2016-08-31 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('270108','2016-09-21 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('271379','2015-06-22 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('271379','2015-08-12 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('271379','2015-08-17 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('271379','2015-08-21 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('271379','2015-12-29 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('271379','2016-03-22 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('271379','2016-06-13 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('279601','2016-03-18 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('279601','2016-04-15 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('279601','2016-08-16 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('300807','2015-07-13 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('308488','2015-11-16 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('308696','2015-07-30 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('308696','2016-09-02 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('321916','2015-08-31 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('325441','2016-07-06 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('325441','2016-07-25 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('325441','2016-08-12 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('325441','2016-09-12 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('325464','2016-07-20 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('325464','2016-08-05 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('326926','2016-02-23 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('326926','2016-04-28 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('328701','2015-06-11 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('328701','2015-06-25 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('328701','2016-06-14 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('329453','2016-09-13 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('330229','2016-07-21 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('330421','2015-03-25 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('330421','2016-04-18 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('331253','2016-05-24 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('331974','2016-07-25 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('336320','2015-06-03 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('336320','2015-10-16 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337635','2015-02-18 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337635','2015-02-23 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337635','2015-08-11 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337635','2015-09-30 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337635','2016-01-14 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337635','2016-01-28 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337635','2016-02-10 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337635','2016-07-11 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337635','2016-08-10 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337721','2015-11-04 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337721','2015-11-17 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337721','2015-11-19 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337721','2016-03-02 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('339406','2015-09-24 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('339406','2016-05-13 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('341847','2015-04-06 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('341847','2015-04-28 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('341847','2015-06-22 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('341847','2015-10-29 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('341847','2016-01-04 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('341847','2016-02-18 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('341847','2016-06-16 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('344057','2015-10-27 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('344057','2015-11-03 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('344057','2015-12-14 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('344057','2016-03-24 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('344057','2016-05-05 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('344057','2016-06-09 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('344057','2016-08-19 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('344057','2016-08-26 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('344977','2016-04-08 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('344977','2016-04-25 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('344977','2016-04-28 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('345001','2015-04-01 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('345001','2015-08-11 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('345001','2015-10-06 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('345001','2016-01-12 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('345001','2016-02-11 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('345001','2016-06-08 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('345001','2016-08-29 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('345026','2015-11-16 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('345026','2015-11-27 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('345026','2016-01-05 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('345026','2016-04-14 00:00:00.000')

Viewing 12 posts - 1 through 11 (of 11 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

NineIron (9/22/2016)
I'm trying to find the next appointment, if any, after a patient "no shows". Here is the code to create a table of no show appointments and another table of future appointments for those patients. How can I list the no show appointments along with the next future appointment? There may be more that one no show per patient.
create table #NoShow
(
MRN varchar(10),
ApptDt datetime
)
insert into #NoShow(MRN, ApptDt) values('128772','2015-08-21 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('128848','2016-03-15 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('128848','2016-04-22 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('220598','2016-01-28 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('220598','2016-08-18 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('226109','2016-03-10 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('226109','2016-04-07 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('226109','2016-07-26 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('229759','2015-01-06 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('229759','2015-03-30 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('229759','2015-04-30 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('229759','2015-11-04 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('229786','2016-07-28 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('270108','2015-11-27 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('271379','2015-06-01 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('271379','2015-06-08 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('271379','2015-08-10 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('271379','2016-05-19 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('271379','2016-06-30 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('279601','2015-06-04 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('300723','2016-08-22 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('300807','2015-04-13 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('300807','2015-05-19 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('300807','2015-11-16 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('308127','2016-09-09 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('308488','2015-04-06 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('308696','2015-01-29 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('321916','2015-07-01 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('321916','2016-05-20 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('325441','2016-04-20 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('325441','2016-07-08 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('325464','2016-05-12 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('326249','2015-08-24 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('326926','2015-06-25 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('326926','2015-08-27 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('327118','2016-06-10 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('328701','2015-05-08 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('328701','2016-07-21 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('329453','2016-02-10 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('330229','2015-12-28 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('330421','2015-03-06 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('330421','2015-06-02 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('331253','2016-02-17 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('331974','2015-07-30 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('331974','2015-08-21 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('332994','2016-04-14 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('332994','2016-06-27 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('334579','2016-07-21 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('335418','2016-04-25 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('336320','2015-05-29 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('336323','2016-09-06 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('337635','2015-02-13 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('337635','2015-12-31 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('337721','2015-08-31 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('339406','2015-09-14 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('341847','2015-01-19 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('341847','2015-04-13 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('341847','2015-11-12 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('344057','2015-04-17 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('344057','2015-06-18 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('344057','2015-10-15 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('344057','2016-01-13 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('344057','2016-04-21 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('344057','2016-04-25 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('344057','2016-05-23 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('344057','2016-07-11 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('344057','2016-08-05 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('344977','2015-11-30 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('344977','2016-02-11 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('345001','2015-03-11 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('345001','2015-03-18 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('345001','2015-07-14 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('345001','2016-04-11 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('345001','2016-05-12 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('345026','2015-06-09 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('345026','2015-07-14 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('345026','2015-12-01 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('345188','2015-01-12 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('345188','2016-03-25 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('345188','2016-08-22 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('349976','2016-07-20 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('350298','2015-12-21 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('350298','2016-05-02 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('350315','2015-03-27 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('350315','2016-09-19 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('350346','2016-08-05 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('350460','2015-06-04 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('350460','2015-09-04 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('350460','2015-09-18 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('351124','2015-05-19 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('351212','2016-08-15 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('352145','2016-04-06 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('352759','2015-02-19 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('352759','2015-08-27 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('352759','2015-09-03 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('352759','2016-02-18 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('352858','2015-01-19 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('352858','2015-02-24 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('352858','2015-03-13 00:00:00.000')
insert into #NoShow(MRN, ApptDt) values('352864','2016-02-23 00:00:00.000')
create table #FutureAppts
(
MRN varchar(10),
ApptDt datetime
)
insert into #FutureAppts(MRN, ApptDt) values('128772','2015-11-10 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('128772','2015-12-04 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('128772','2016-04-11 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('128848','2016-09-20 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('220598','2016-02-03 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('220598','2016-04-25 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('220598','2016-05-31 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('226109','2016-06-22 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('229759','2015-09-09 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('229759','2015-10-07 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('229759','2015-11-24 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('229759','2016-01-20 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('229759','2016-03-11 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('229759','2016-08-04 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('229786','2016-09-09 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('270108','2015-12-01 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('270108','2016-03-11 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('270108','2016-04-01 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('270108','2016-06-29 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('270108','2016-07-18 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('270108','2016-08-31 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('270108','2016-09-21 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('271379','2015-06-22 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('271379','2015-08-12 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('271379','2015-08-17 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('271379','2015-08-21 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('271379','2015-12-29 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('271379','2016-03-22 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('271379','2016-06-13 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('279601','2016-03-18 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('279601','2016-04-15 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('279601','2016-08-16 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('300807','2015-07-13 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('308488','2015-11-16 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('308696','2015-07-30 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('308696','2016-09-02 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('321916','2015-08-31 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('325441','2016-07-06 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('325441','2016-07-25 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('325441','2016-08-12 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('325441','2016-09-12 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('325464','2016-07-20 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('325464','2016-08-05 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('326926','2016-02-23 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('326926','2016-04-28 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('328701','2015-06-11 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('328701','2015-06-25 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('328701','2016-06-14 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('329453','2016-09-13 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('330229','2016-07-21 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('330421','2015-03-25 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('330421','2016-04-18 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('331253','2016-05-24 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('331974','2016-07-25 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('336320','2015-06-03 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('336320','2015-10-16 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337635','2015-02-18 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337635','2015-02-23 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337635','2015-08-11 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337635','2015-09-30 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337635','2016-01-14 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337635','2016-01-28 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337635','2016-02-10 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337635','2016-07-11 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337635','2016-08-10 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337721','2015-11-04 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337721','2015-11-17 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337721','2015-11-19 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('337721','2016-03-02 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('339406','2015-09-24 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('339406','2016-05-13 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('341847','2015-04-06 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('341847','2015-04-28 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('341847','2015-06-22 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('341847','2015-10-29 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('341847','2016-01-04 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('341847','2016-02-18 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('341847','2016-06-16 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('344057','2015-10-27 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('344057','2015-11-03 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('344057','2015-12-14 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('344057','2016-03-24 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('344057','2016-05-05 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('344057','2016-06-09 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('344057','2016-08-19 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('344057','2016-08-26 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('344977','2016-04-08 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('344977','2016-04-25 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('344977','2016-04-28 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('345001','2015-04-01 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('345001','2015-08-11 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('345001','2015-10-06 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('345001','2016-01-12 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('345001','2016-02-11 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('345001','2016-06-08 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('345001','2016-08-29 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('345026','2015-11-16 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('345026','2015-11-27 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('345026','2016-01-05 00:00:00.000')
insert into #FutureAppts(MRN, ApptDt) values('345026','2016-04-14 00:00:00.000')

What is the expected output based on the sample data provided?

NineIron SSChampion Points: 12529 More actions · Answer 2

For each record in #NoShow I'd like to get the record in #FutureAppts the is the next with a date greater than the No show record

joeroshan SSChampion Points: 10377 More actions · Answer 3

With Appt

as

(

Select F.MRN,N.ApptDt as NoShowDate,F.ApptDt as NextApp,ROW_NUMBER() over (Partition by F.MRN order by F.ApptDt ) Rnm from #NoShow N join #FutureAppts F on N.MRN=F.MRN

and F.ApptDt>N.ApptDt

) select * from Appt Where rnm=1

Does this help you?

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

Here's an option:

SELECT *

FROM #NoShow ns

OUTER APPLY( SELECT TOP 1 fa.ApptDt

FROM #FutureAppts fa

WHERE ns.MRN = fa.MRN

AND ns.ApptDt < fa.ApptDt

ORDER BY fa.ApptDt) x;

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

NineIron (9/22/2016)
For each record in #NoShow I'd like to get the record in #FutureAppts the is the next with a date greater than the No show record

Didn't ask for a description, I asked what is the expected output. Show use what the results should be.

NineIron SSChampion Points: 12529 More actions · Answer 6

The results should look like this..........

MRNNoShowDateNextAppt

8763876/29/20154/29/2016

8763876/15/2016

8763877/21/2016

8596571/11/20161/15/2016

8705832/19/20166/1/2016

8769036/16/20166/29/2016

8919036/6/20167/14/2016

8176159/9/2016

8134158/6/20158/21/2015

8812794/27/2016

8750662/12/20163/24/2016

8750641/29/20164/5/2016

8750642/5/20164/5/2016

8750643/31/20164/5/2016

8760471/14/20161/21/2016

8760472/1/2016

8760475/25/2016

85913212/9/2015

7907319/7/2016

8726524/1/20154/7/2015

8726527/21/2016

8726528/4/2016

8155505/4/2016

8780476/4/20158/4/2015

3503468/5/2016

8831216/16/2016

8820735/11/2016

8742618/26/201510/9/2015

8742611/7/2016

NineIron SSChampion Points: 12529 More actions · Answer 7

Look at MRN=271379. This is what I need to get as results for each record in #NoShow. However, if there is no future appointment for a particular patient then, column NextAppt would be NULL.

MRNNoShowDateNextAppt

2713796/1/20156/22/2015

2713796/8/20156/22/2015

2713798/10/20158/12/2015

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 8

Luis Cazares (9/22/2016)
Here's an option:
SELECT *
FROM #NoShow ns
OUTER APPLY( SELECT TOP 1 fa.ApptDt
FROM #FutureAppts fa
WHERE ns.MRN = fa.MRN
AND ns.ApptDt < fa.ApptDt
ORDER BY fa.ApptDt) x;

why doesnt Luis solution work for you?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

NineIron SSChampion Points: 12529 More actions · Answer 9

To be honest, I got hung up on the other solution. Luis's works out fine.

Thanx.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 10

NineIron (9/23/2016)
To be honest, I got hung up on the other solution. Luis's works out fine.
Thanx.

The other solution should work fine if you make the join an outer join. Depending on your tables and indexes, one solution might be better than the other.

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: 12529 More actions · Answer 11

NineIron

SSChampion

Points: 12529

September 23, 2016 at 7:42 am

#1902777

Thanx.