June 22, 2015 at 4:49 am
I have a list of patient encounter dates ordered by the date. I need to subtract the previous date in order to get the number of days between each date for the same patient. Any thoughts?
create table TEST
(
MRN varchar(10),
EncDTTM datetime,
Sequence int
)
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-01-24','1')
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-02-03','2')
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-10-20','3')
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-10-21','4')
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-10-28','5')
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-11-13','6')
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-11-26','7')
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-12-03','8')
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2015-01-21','9')
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2015-02-13','10')
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2015-03-16','11')
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2015-04-06','12')
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2015-05-11','13')
insert into TEST(MRN, EncDTTM, Sequence) values( '00003436','2014-04-04','1')
insert into TEST(MRN, EncDTTM, Sequence) values( '00003436','2014-05-06','2')
insert into TEST(MRN, EncDTTM, Sequence) values( '00003436','2014-06-02','3')
insert into TEST(MRN, EncDTTM, Sequence) values( '00003436','2014-07-09','4')
insert into TEST(MRN, EncDTTM, Sequence) values( '00003436','2015-03-02','5')
insert into TEST(MRN, EncDTTM, Sequence) values( '00003436','2015-03-09','6')
insert into TEST(MRN, EncDTTM, Sequence) values( '00003436','2015-03-13','7')
insert into TEST(MRN, EncDTTM, Sequence) values( '00003436','2015-03-13','8')
insert into TEST(MRN, EncDTTM, Sequence) values( '00003436','2015-03-16','9')
insert into TEST(MRN, EncDTTM, Sequence) values( '00003436','2015-03-18','10')
insert into TEST(MRN, EncDTTM, Sequence) values( '00003436','2015-03-20','11')
insert into TEST(MRN, EncDTTM, Sequence) values( '00003436','2015-03-27','12')
insert into TEST(MRN, EncDTTM, Sequence) values( '00003436','2015-03-30','13')
insert into TEST(MRN, EncDTTM, Sequence) values( '00003436','2015-04-17','14')
insert into TEST(MRN, EncDTTM, Sequence) values( '00003436','2015-04-20','15')
insert into TEST(MRN, EncDTTM, Sequence) values( '00003436','2015-05-12','16')
insert into TEST(MRN, EncDTTM, Sequence) values( '00003436','2015-05-14','17')
insert into TEST(MRN, EncDTTM, Sequence) values( '00003436','2015-05-27','18')
insert into TEST(MRN, EncDTTM, Sequence) values( '00007828','2014-10-08','1')
insert into TEST(MRN, EncDTTM, Sequence) values( '00007828','2014-10-10','2')
insert into TEST(MRN, EncDTTM, Sequence) values( '00007828','2014-10-15','3')
insert into TEST(MRN, EncDTTM, Sequence) values( '00007828','2014-10-24','4')
insert into TEST(MRN, EncDTTM, Sequence) values( '00007828','2014-11-06','5')
insert into TEST(MRN, EncDTTM, Sequence) values( '00007828','2014-11-07','6')
insert into TEST(MRN, EncDTTM, Sequence) values( '00007828','2014-11-13','7')
insert into TEST(MRN, EncDTTM, Sequence) values( '00007828','2014-12-04','8')
insert into TEST(MRN, EncDTTM, Sequence) values( '00007828','2014-12-22','9')
insert into TEST(MRN, EncDTTM, Sequence) values( '00007828','2015-01-26','10')
insert into TEST(MRN, EncDTTM, Sequence) values( '00007828','2015-01-29','11')
insert into TEST(MRN, EncDTTM, Sequence) values( '00007828','2015-01-29','12')
insert into TEST(MRN, EncDTTM, Sequence) values( '00007828','2015-02-16','13')
insert into TEST(MRN, EncDTTM, Sequence) values( '00007828','2015-03-30','14')
insert into TEST(MRN, EncDTTM, Sequence) values( '00007828','2015-04-07','15')
insert into TEST(MRN, EncDTTM, Sequence) values( '00007828','2015-05-13','16')
insert into TEST(MRN, EncDTTM, Sequence) values( '00014345','2015-01-15','1')
insert into TEST(MRN, EncDTTM, Sequence) values( '00014345','2015-01-15','2')
insert into TEST(MRN, EncDTTM, Sequence) values( '00014345','2015-01-22','3')
insert into TEST(MRN, EncDTTM, Sequence) values( '00014345','2015-01-23','4')
insert into TEST(MRN, EncDTTM, Sequence) values( '00014345','2015-01-26','5')
insert into TEST(MRN, EncDTTM, Sequence) values( '00014345','2015-02-09','6')
insert into TEST(MRN, EncDTTM, Sequence) values( '00014345','2015-02-09','7')
insert into TEST(MRN, EncDTTM, Sequence) values( '00014345','2015-02-10','8')
insert into TEST(MRN, EncDTTM, Sequence) values( '00014345','2015-02-20','9')
insert into TEST(MRN, EncDTTM, Sequence) values( '00014345','2015-03-02','10')
insert into TEST(MRN, EncDTTM, Sequence) values( '00014345','2015-03-18','11')
insert into TEST(MRN, EncDTTM, Sequence) values( '00014345','2015-03-18','12')
insert into TEST(MRN, EncDTTM, Sequence) values( '00014345','2015-03-30','13')
insert into TEST(MRN, EncDTTM, Sequence) values( '00014345','2015-04-07','14')
insert into TEST(MRN, EncDTTM, Sequence) values( '00014345','2015-05-21','15')
insert into TEST(MRN, EncDTTM, Sequence) values( '00014345','2015-06-09','16')
insert into TEST(MRN, EncDTTM, Sequence) values( '00026742','2014-01-07','1')
insert into TEST(MRN, EncDTTM, Sequence) values( '00026742','2014-01-21','2')
insert into TEST(MRN, EncDTTM, Sequence) values( '00026742','2014-01-24','3')
insert into TEST(MRN, EncDTTM, Sequence) values( '00026742','2014-04-23','4')
insert into TEST(MRN, EncDTTM, Sequence) values( '00026742','2014-06-18','5')
insert into TEST(MRN, EncDTTM, Sequence) values( '00026742','2014-07-02','6')
insert into TEST(MRN, EncDTTM, Sequence) values( '00026742','2014-11-14','7')
insert into TEST(MRN, EncDTTM, Sequence) values( '00026742','2014-11-19','8')
insert into TEST(MRN, EncDTTM, Sequence) values( '00026742','2014-11-26','9')
insert into TEST(MRN, EncDTTM, Sequence) values( '00026742','2014-12-04','10')
insert into TEST(MRN, EncDTTM, Sequence) values( '00026742','2014-12-11','11')
insert into TEST(MRN, EncDTTM, Sequence) values( '00026742','2014-12-19','12')
insert into TEST(MRN, EncDTTM, Sequence) values( '00026742','2015-01-08','13')
insert into TEST(MRN, EncDTTM, Sequence) values( '00026742','2015-01-23','14')
insert into TEST(MRN, EncDTTM, Sequence) values( '00026742','2015-02-04','15')
insert into TEST(MRN, EncDTTM, Sequence) values( '00026742','2015-02-23','16')
insert into TEST(MRN, EncDTTM, Sequence) values( '00026742','2015-02-24','17')
insert into TEST(MRN, EncDTTM, Sequence) values( '00026742','2015-03-19','18')
insert into TEST(MRN, EncDTTM, Sequence) values( '00026742','2015-04-09','19')
insert into TEST(MRN, EncDTTM, Sequence) values( '00026742','2015-04-10','20')
insert into TEST(MRN, EncDTTM, Sequence) values( '00035157','2014-11-26','1')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-01-14','1')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-01-16','2')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-01-19','3')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-01-20','4')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-01-21','5')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-01-22','6')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-01-30','7')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-02-02','8')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-02-09','9')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-02-11','10')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-02-23','11')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-02-25','12')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-02-27','13')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-03-11','14')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-03-24','15')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-03-27','16')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-03-31','17')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-04-07','18')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-04-09','19')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-04-10','20')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-04-17','21')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-04-20','22')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-05-08','23')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-05-11','24')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-05-12','25')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-05-13','26')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-05-26','27')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-06-05','28')
insert into TEST(MRN, EncDTTM, Sequence) values( '00040294','2015-06-09','29')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-07-08','1')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-07-14','2')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-07-29','3')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-09-04','4')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-09-17','5')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-10-03','6')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-10-08','7')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-10-14','8')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-10-17','9')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-10-22','10')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-10-28','11')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-11-10','12')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-11-12','13')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-11-12','14')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-11-13','15')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-11-18','16')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-11-19','17')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-11-24','18')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-12-04','19')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-12-05','20')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-12-08','21')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-12-10','22')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-12-17','23')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-12-23','24')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-12-24','25')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2014-12-29','26')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2015-01-09','27')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2015-01-13','28')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2015-02-02','29')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2015-02-06','30')
insert into TEST(MRN, EncDTTM, Sequence) values( '00041558','2015-03-24','31')
insert into TEST(MRN, EncDTTM, Sequence) values( '00043126','2015-01-14','1')
insert into TEST(MRN, EncDTTM, Sequence) values( '00043238','2014-12-02','1')
insert into TEST(MRN, EncDTTM, Sequence) values( '00043238','2014-12-10','2')
insert into TEST(MRN, EncDTTM, Sequence) values( '00043238','2014-12-18','3')
insert into TEST(MRN, EncDTTM, Sequence) values( '00043238','2014-12-29','4')
insert into TEST(MRN, EncDTTM, Sequence) values( '00043238','2015-01-13','5')
insert into TEST(MRN, EncDTTM, Sequence) values( '00043238','2015-01-14','6')
insert into TEST(MRN, EncDTTM, Sequence) values( '00043238','2015-01-21','7')
insert into TEST(MRN, EncDTTM, Sequence) values( '00043238','2015-02-23','8')
insert into TEST(MRN, EncDTTM, Sequence) values( '00045377','2014-03-13','1')
insert into TEST(MRN, EncDTTM, Sequence) values( '00045377','2014-11-24','2')
insert into TEST(MRN, EncDTTM, Sequence) values( '00045377','2014-12-16','3')
insert into TEST(MRN, EncDTTM, Sequence) values( '00045377','2014-12-19','4')
insert into TEST(MRN, EncDTTM, Sequence) values( '00045377','2014-12-23','5')
insert into TEST(MRN, EncDTTM, Sequence) values( '00045377','2014-12-30','6')
insert into TEST(MRN, EncDTTM, Sequence) values( '00045377','2015-02-03','7')
insert into TEST(MRN, EncDTTM, Sequence) values( '00045377','2015-02-06','8')
insert into TEST(MRN, EncDTTM, Sequence) values( '00045377','2015-02-13','9')
insert into TEST(MRN, EncDTTM, Sequence) values( '00045377','2015-02-16','10')
insert into TEST(MRN, EncDTTM, Sequence) values( '00045377','2015-02-19','11')
insert into TEST(MRN, EncDTTM, Sequence) values( '00045377','2015-02-24','12')
insert into TEST(MRN, EncDTTM, Sequence) values( '00045377','2015-03-06','13')
insert into TEST(MRN, EncDTTM, Sequence) values( '00045377','2015-03-26','14')
insert into TEST(MRN, EncDTTM, Sequence) values( '00045377','2015-04-21','15')
insert into TEST(MRN, EncDTTM, Sequence) values( '00048218','2014-09-08','1')
insert into TEST(MRN, EncDTTM, Sequence) values( '00048218','2014-09-08','2')
insert into TEST(MRN, EncDTTM, Sequence) values( '00048218','2014-10-17','3')
insert into TEST(MRN, EncDTTM, Sequence) values( '00048218','2015-02-19','4')
insert into TEST(MRN, EncDTTM, Sequence) values( '00048218','2015-03-26','5')
insert into TEST(MRN, EncDTTM, Sequence) values( '00048218','2015-03-28','6')
insert into TEST(MRN, EncDTTM, Sequence) values( '00048218','2015-03-30','7')
insert into TEST(MRN, EncDTTM, Sequence) values( '00048218','2015-04-03','8')
insert into TEST(MRN, EncDTTM, Sequence) values( '00048218','2015-04-08','9')
insert into TEST(MRN, EncDTTM, Sequence) values( '00048218','2015-04-23','10')
insert into TEST(MRN, EncDTTM, Sequence) values( '00048218','2015-05-01','11')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2014-05-19','1')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2014-05-27','2')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2014-11-10','3')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2014-11-20','4')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2014-11-21','5')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2014-12-04','6')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2015-01-06','7')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2015-01-09','8')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2015-01-14','9')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2015-01-28','10')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2015-01-29','11')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2015-02-03','12')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2015-02-11','13')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2015-02-19','14')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2015-03-03','15')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2015-03-19','16')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2015-03-23','17')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2015-03-24','18')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2015-03-25','19')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2015-03-31','20')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2015-04-13','21')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2015-04-21','22')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2015-04-29','23')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2015-05-14','24')
insert into TEST(MRN, EncDTTM, Sequence) values( '00054372','2015-05-26','25')
June 22, 2015 at 5:23 am
How about this:
SELECTnewer.MRN,
newer.EncDTTM,
newer.Sequence,
DATEDIFF(day,older.EncDTTM, newer.EncDTTM) as DaysApart
FROM TEST as newer
LEFT JOIN TEST As older
on newer.MRN = older.MRN
and newer.sequence = older.sequence + 1
ORDER BY newer.MRN, newer.EncDTTM DESC
MCITP SQL 2005, MCSA SQL 2012
June 22, 2015 at 5:23 am
It looks to me like a running totals problem. Check out Jeff Moden's excellent article at http://www.sqlservercentral.com/articles/T-SQL/68467/. It explores different ways of going about it and the performance implications of each one. Enjoy!
June 22, 2015 at 5:56 am
Thanx. This will work out fine.
June 22, 2015 at 5:57 am
I don't want running total but, the difference between previous rows. Thanx.
June 22, 2015 at 6:04 am
NineIron (6/22/2015)
I don't want running total but, the difference between previous rows. Thanx.
So the difference would be that you subtract instead of add.
June 22, 2015 at 6:06 am
OK. Thanx.
June 22, 2015 at 6:37 am
Ed Wagner (6/22/2015)
NineIron (6/22/2015)
I don't want running total but, the difference between previous rows. Thanx.So the difference would be that you subtract instead of add.
No, because it's a plain subtraction, not a running subtraction.
What he wants is the difference between a date and the date in the previous row only. To be a running total/subtraction he'd have to want the difference between the date in the current row and every other date back to the beginning of the window.
To put it in SQL 2012 terms, what he wants is essentially SomeValue - LAG(SomeValue), not a SUM() ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 22, 2015 at 6:42 am
GilaMonster (6/22/2015)
Ed Wagner (6/22/2015)
NineIron (6/22/2015)
I don't want running total but, the difference between previous rows. Thanx.So the difference would be that you subtract instead of add.
No, because it's a plain subtraction, not a running subtraction.
What he wants is the difference between a date and the date in the previous row only. To be a running total/subtraction he'd have to want the difference between the date in the current row and every other date back to the beginning of the window.
To put it in SQL 2012 terms, what he wants is essentially SomeValue - LAG(SomeValue), not a SUM() ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Touche. I stand corrected.
June 22, 2015 at 11:36 am
I'm not sure if I can add on something to this post or should I create another but, I need to exclude weekends between these dates.
Thoughts?
June 22, 2015 at 12:03 pm
NineIron (6/22/2015)
Thanx. This will work out fine.
Possibly not. There is absolutely nothing in your data that will prevent a missing sequence number due to a rollback or other cause.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 22, 2015 at 12:21 pm
Jeff, Any thoughts on excluding weekends when subtracting the two dates?
June 22, 2015 at 12:42 pm
NineIron (6/22/2015)
Jeff, Any thoughts on excluding weekends when subtracting the two dates?
Absolutely but I don't like to post until after I've tested my code and I won't be able to get to that until tonight after work.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 23, 2015 at 2:59 am
I think this should do it, it also addresses the point Jeff made about gaps in the sequence.
CREATE FUNCTION [dbo].[CalculateNumberOFWorkDays] (@StartDate datetime, @EndDate datetime)
RETURNS int
AS
BEGIN
SET @StartDate = DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0)
SET @EndDate = DATEADD(dd, DATEDIFF(dd, 0, @EndDate), 0)
DECLARE @WORKDAYS INT
SELECT @WORKDAYS = (DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
RETURN @WORKDAYS
END;
WITH CTE_Data AS (
SELECTMRN,
EncDTTM,
ROW_NUMBER() OVER ( PARTITION BY MRN ORDER BY EncDTTM DESC) AS Sequence
FROM TEST as newer)
SELECTnewer.MRN,
newer.EncDTTM,
newer.Sequence,
dbo.CalculateNumberOFWorkDays(older.EncDTTM, newer.EncDTTM) AS NoWorkingDaysApart,
DATEDIFF(day,older.EncDTTM, newer.EncDTTM) as DaysApart
FROM CTE_Data as newer
LEFT JOIN CTE_Data As older
on newer.MRN = older.MRN
and newer.sequence = older.sequence - 1
ORDER BY newer.MRN, newer.EncDTTM DESC
MCITP SQL 2005, MCSA SQL 2012
June 23, 2015 at 7:32 am
I haven't tried that code but it looks right. Heh... I definitely recognize the "workday" code.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply