June 16, 2015 at 12:27 pm
I need to identify or flag the records that have an ApptDate that is <=7 days from the DischargeDate. Any help is greatly appreciated.
create table dbo.TEST
(MRN varchar(10),
DischargeDate datetime,
ApptDate datetime
)
insert into TEST(MRN, DischargeDate, ApptDate) values( '00541736','2014-08-23','2014-09-11')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00541736','2014-08-23','2014-09-11')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00541736','2014-08-24','2014-09-11')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00541736','2014-08-28','2014-09-11')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00541736','2014-12-09','2015-03-12')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00541736','2015-01-20','2015-03-12')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00541736','2015-04-18','2015-04-20')
insert into TEST(MRN, DischargeDate, ApptDate) values( '807628','2015-04-14','2015-05-29')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00005536','2015-04-18','2015-04-24')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00005536','2015-05-21','2015-06-08')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00565410','2014-07-09','2014-08-13')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00565410','2014-09-26','2015-02-06')
insert into TEST(MRN, DischargeDate, ApptDate) values( '698576','2015-03-24','2015-03-26')
insert into TEST(MRN, DischargeDate, ApptDate) values( '698576','2015-03-26','2015-04-01')
insert into TEST(MRN, DischargeDate, ApptDate) values( '782821','2014-10-04','2014-10-07')
insert into TEST(MRN, DischargeDate, ApptDate) values( '782821','2015-02-01','2015-02-06')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00030896','2014-08-16','2014-09-23')
insert into TEST(MRN, DischargeDate, ApptDate) values( '804816','2015-05-09','2015-05-18')
insert into TEST(MRN, DischargeDate, ApptDate) values( '644383','2015-01-02','2015-01-20')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00035146','2015-03-07','2015-03-09')
insert into TEST(MRN, DischargeDate, ApptDate) values( '761226','2014-10-17','2014-12-05')
insert into TEST(MRN, DischargeDate, ApptDate) values( '861174','2015-04-06','2015-04-16')
insert into TEST(MRN, DischargeDate, ApptDate) values( '825804','2015-03-05','2015-03-30')
insert into TEST(MRN, DischargeDate, ApptDate) values( '825804','2015-03-29','2015-03-30')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00021041','2014-07-04','2014-07-25')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00021041','2014-10-20','2014-11-14')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00069423','2014-07-03','2014-07-07')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00033580','2014-07-20','2014-08-06')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00033580','2015-01-03','2015-01-07')
insert into TEST(MRN, DischargeDate, ApptDate) values( '813773','2014-08-07','2014-08-15')
insert into TEST(MRN, DischargeDate, ApptDate) values( '813773','2014-08-10','2014-08-15')
insert into TEST(MRN, DischargeDate, ApptDate) values( '813773','2014-10-06','2014-10-09')
insert into TEST(MRN, DischargeDate, ApptDate) values( '813773','2015-01-11','2015-01-15')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00116412','2015-05-05','2015-05-07')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00116412','2015-06-09','2015-06-11')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00117851','2015-03-18','2015-03-19')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00623724','2015-03-18','2015-03-23')
insert into TEST(MRN, DischargeDate, ApptDate) values( '641815','2014-11-27','2015-05-13')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00561772','2014-12-08','2015-02-27')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00031759','2014-10-20','2014-11-07')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00031759','2015-05-06','2015-05-12')
insert into TEST(MRN, DischargeDate, ApptDate) values( '732977','2015-04-28','2015-05-04')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00035079','2015-02-12','2015-02-19')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00053661','2015-05-06','2015-05-15')
insert into TEST(MRN, DischargeDate, ApptDate) values( '745788','2014-08-08','2014-08-14')
insert into TEST(MRN, DischargeDate, ApptDate) values( '745788','2014-08-11','2014-08-14')
insert into TEST(MRN, DischargeDate, ApptDate) values( '331419','2014-08-09','2014-08-20')
insert into TEST(MRN, DischargeDate, ApptDate) values( '331419','2014-08-31','2014-09-03')
insert into TEST(MRN, DischargeDate, ApptDate) values( '813564','2014-10-19','2014-10-22')
insert into TEST(MRN, DischargeDate, ApptDate) values( '813564','2015-01-12','2015-01-21')
insert into TEST(MRN, DischargeDate, ApptDate) values( '742531','2014-09-02','2014-09-11')
insert into TEST(MRN, DischargeDate, ApptDate) values( '705681','2015-03-19','2015-03-24')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00518298','2014-08-01','2014-12-15')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00044670','2014-08-01','2014-08-20')
insert into TEST(MRN, DischargeDate, ApptDate) values( '797836','2014-07-07','2014-07-10')
insert into TEST(MRN, DischargeDate, ApptDate) values( '826410','2015-04-22','2015-05-04')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00051299','2015-02-08','2015-02-11')
insert into TEST(MRN, DischargeDate, ApptDate) values( '826438','2015-01-25','2015-03-27')
insert into TEST(MRN, DischargeDate, ApptDate) values( '729175','2015-01-02','2015-03-05')
insert into TEST(MRN, DischargeDate, ApptDate) values( '663192','2014-08-13','2014-11-06')
insert into TEST(MRN, DischargeDate, ApptDate) values( '663192','2014-09-04','2014-11-06')
insert into TEST(MRN, DischargeDate, ApptDate) values( '663192','2014-12-14','2014-12-17')
insert into TEST(MRN, DischargeDate, ApptDate) values( '663192','2015-02-10','2015-04-29')
insert into TEST(MRN, DischargeDate, ApptDate) values( '324853','2014-11-08','2015-02-17')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00568853','2014-11-30','2014-12-02')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00047415','2014-10-06','2014-10-29')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00047415','2015-03-18','2015-04-13')
insert into TEST(MRN, DischargeDate, ApptDate) values( '327973','2014-09-28','2014-09-30')
insert into TEST(MRN, DischargeDate, ApptDate) values( '327973','2015-03-04','2015-03-05')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00509336','2014-10-14','2014-10-29')
insert into TEST(MRN, DischargeDate, ApptDate) values( '806779','2014-08-19','2014-09-16')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00563367','2014-07-31','2014-08-01')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00563367','2015-04-13','2015-04-16')
insert into TEST(MRN, DischargeDate, ApptDate) values( '281589','2014-07-07','2014-10-21')
insert into TEST(MRN, DischargeDate, ApptDate) values( '281589','2014-11-03','2014-11-11')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00016247','2014-07-22','2014-10-20')
insert into TEST(MRN, DischargeDate, ApptDate) values( '715513','2014-12-28','2015-05-11')
insert into TEST(MRN, DischargeDate, ApptDate) values( '734159','2014-09-17','2014-09-29')
insert into TEST(MRN, DischargeDate, ApptDate) values( '734159','2015-01-01','2015-01-09')
insert into TEST(MRN, DischargeDate, ApptDate) values( '699114','2014-12-15','2015-01-07')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00517510','2014-08-27','2014-08-29')
insert into TEST(MRN, DischargeDate, ApptDate) values( '826459','2015-03-08','2015-03-16')
insert into TEST(MRN, DischargeDate, ApptDate) values( '696365','2014-09-27','2014-12-03')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00095282','2014-08-31','2014-09-15')
insert into TEST(MRN, DischargeDate, ApptDate) values( '794119','2014-07-12','2014-07-16')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00041569','2014-08-28','2014-09-05')
insert into TEST(MRN, DischargeDate, ApptDate) values( '709295','2015-05-16','2015-05-20')
insert into TEST(MRN, DischargeDate, ApptDate) values( '772467','2014-09-29','2014-11-07')
insert into TEST(MRN, DischargeDate, ApptDate) values( '335685','2014-07-19','2014-07-23')
insert into TEST(MRN, DischargeDate, ApptDate) values( '335685','2014-10-07','2014-10-15')
insert into TEST(MRN, DischargeDate, ApptDate) values( '335685','2014-11-15','2014-11-19')
insert into TEST(MRN, DischargeDate, ApptDate) values( '335685','2014-12-30','2015-02-24')
insert into TEST(MRN, DischargeDate, ApptDate) values( '335685','2015-01-07','2015-02-24')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00518987','2015-04-06','2015-04-08')
insert into TEST(MRN, DischargeDate, ApptDate) values( '741815','2014-09-14','2015-04-09')
insert into TEST(MRN, DischargeDate, ApptDate) values( '741815','2015-03-18','2015-04-09')
insert into TEST(MRN, DischargeDate, ApptDate) values( '797001','2015-06-06','2015-06-12')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00089221','2015-05-27','2015-06-09')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00503505','2014-07-20','2014-08-21')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00622557','2014-07-14','2014-07-23')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00622557','2014-09-12','2014-09-17')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00622557','2014-12-01','2014-12-09')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00622557','2015-04-26','2015-06-09')
insert into TEST(MRN, DischargeDate, ApptDate) values( '806455','2014-10-16','2014-10-22')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00077344','2014-07-16','2014-08-01')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00077344','2015-01-09','2015-02-13')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00077344','2015-01-24','2015-02-13')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00077344','2015-04-16','2015-05-04')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00041364','2015-02-07','2015-03-13')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00041364','2015-02-08','2015-03-13')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00631851','2015-01-28','2015-02-23')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00017011','2014-07-24','2014-09-15')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00017011','2014-08-19','2014-09-15')
insert into TEST(MRN, DischargeDate, ApptDate) values( '826698','2014-10-16','2014-12-02')
insert into TEST(MRN, DischargeDate, ApptDate) values( '764378','2014-11-13','2014-12-19')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00008365','2015-05-04','2015-06-01')
insert into TEST(MRN, DischargeDate, ApptDate) values( '332175','2014-11-19','2014-12-30')
insert into TEST(MRN, DischargeDate, ApptDate) values( '779905','2014-11-29','2014-12-04')
insert into TEST(MRN, DischargeDate, ApptDate) values( '811522','2015-04-22','2015-04-23')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00035881','2014-08-02','2014-11-24')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00035881','2014-08-09','2014-11-24')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00035881','2014-08-09','2014-11-24')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00035881','2014-08-14','2014-11-24')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00035881','2014-10-19','2014-11-24')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00035881','2014-12-04','2014-12-23')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00035881','2014-12-07','2014-12-23')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00035881','2015-02-03','2015-02-24')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00035881','2015-02-07','2015-02-24')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00035881','2015-04-26','2015-04-28')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00035881','2015-05-21','2015-05-27')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00035881','2015-05-23','2015-05-27')
insert into TEST(MRN, DischargeDate, ApptDate) values( '759798','2014-07-10','2014-10-02')
insert into TEST(MRN, DischargeDate, ApptDate) values( '759798','2015-03-12','2015-03-26')
insert into TEST(MRN, DischargeDate, ApptDate) values( '759798','2015-03-21','2015-03-26')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00505383','2015-04-29','2015-05-07')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00119119','2015-06-07','2015-06-11')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00401458','2015-01-06','2015-01-21')
insert into TEST(MRN, DischargeDate, ApptDate) values( '872133','2014-07-11','2014-08-05')
insert into TEST(MRN, DischargeDate, ApptDate) values( '706960','2014-07-31','2014-09-04')
insert into TEST(MRN, DischargeDate, ApptDate) values( '706960','2015-03-23','2015-04-10')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00623818','2015-05-03','2015-05-28')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00580124','2014-09-19','2014-09-24')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00096130','2015-05-31','2015-06-15')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00088855','2014-08-08','2014-09-17')
insert into TEST(MRN, DischargeDate, ApptDate) values( '861418','2014-08-18','2014-08-22')
insert into TEST(MRN, DischargeDate, ApptDate) values( '331390','2015-03-18','2015-06-08')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00028463','2014-09-05','2014-09-08')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00574472','2014-07-17','2014-07-28')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00055035','2014-12-23','2015-01-26')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00055035','2015-01-23','2015-01-26')
insert into TEST(MRN, DischargeDate, ApptDate) values( '730171','2015-05-19','2015-05-22')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00622162','2014-11-17','2014-12-02')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00046288','2014-08-05','2014-09-03')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00046288','2014-09-01','2014-09-03')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00046288','2014-09-02','2014-09-03')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00552690','2014-12-30','2015-01-07')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00596490','2015-02-23','2015-03-03')
insert into TEST(MRN, DischargeDate, ApptDate) values( '813178','2014-10-21','2014-11-10')
insert into TEST(MRN, DischargeDate, ApptDate) values( '813178','2014-11-24','2014-12-03')
insert into TEST(MRN, DischargeDate, ApptDate) values( '813178','2015-06-06','2015-06-15')
insert into TEST(MRN, DischargeDate, ApptDate) values( '806590','2014-11-15','2014-12-05')
insert into TEST(MRN, DischargeDate, ApptDate) values( '810388','2014-09-04','2014-09-15')
insert into TEST(MRN, DischargeDate, ApptDate) values( '810388','2015-01-09','2015-04-13')
insert into TEST(MRN, DischargeDate, ApptDate) values( '806868','2015-04-04','2015-04-17')
insert into TEST(MRN, DischargeDate, ApptDate) values( '325358','2014-10-16','2014-10-28')
insert into TEST(MRN, DischargeDate, ApptDate) values( '842505','2015-04-28','2015-06-03')
insert into TEST(MRN, DischargeDate, ApptDate) values( '842505','2015-05-23','2015-06-03')
insert into TEST(MRN, DischargeDate, ApptDate) values( '720178','2015-02-21','2015-03-03')
insert into TEST(MRN, DischargeDate, ApptDate) values( '668197','2015-05-28','2015-06-10')
insert into TEST(MRN, DischargeDate, ApptDate) values( '668197','2015-05-28','2015-06-10')
insert into TEST(MRN, DischargeDate, ApptDate) values( '668197','2015-05-29','2015-06-10')
insert into TEST(MRN, DischargeDate, ApptDate) values( '668197','2015-05-30','2015-06-10')
insert into TEST(MRN, DischargeDate, ApptDate) values( '668197','2015-05-31','2015-06-10')
insert into TEST(MRN, DischargeDate, ApptDate) values( '668197','2015-05-31','2015-06-10')
insert into TEST(MRN, DischargeDate, ApptDate) values( '668197','2015-06-03','2015-06-10')
insert into TEST(MRN, DischargeDate, ApptDate) values( '324486','2015-02-03','2015-03-02')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00552437','2015-05-10','2015-05-11')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00549508','2014-12-22','2015-01-14')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00633479','2015-04-18','2015-04-23')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00520541','2014-07-17','2014-07-21')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00520541','2014-07-19','2014-07-21')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00556424','2014-07-09','2014-07-25')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00580063','2015-04-20','2015-05-01')
insert into TEST(MRN, DischargeDate, ApptDate) values( '804757','2014-07-24','2014-08-07')
insert into TEST(MRN, DischargeDate, ApptDate) values( '804757','2014-10-02','2014-10-06')
insert into TEST(MRN, DischargeDate, ApptDate) values( '332891','2014-09-09','2014-09-12')
insert into TEST(MRN, DischargeDate, ApptDate) values( '332731','2015-03-17','2015-05-07')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00084255','2014-10-29','2014-11-04')
insert into TEST(MRN, DischargeDate, ApptDate) values( '826498','2015-02-21','2015-05-07')
insert into TEST(MRN, DischargeDate, ApptDate) values( '750006','2015-03-08','2015-03-13')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00056652','2014-08-31','2014-09-08')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00631801','2015-02-23','2015-03-12')
insert into TEST(MRN, DischargeDate, ApptDate) values( '714007','2015-04-02','2015-04-09')
insert into TEST(MRN, DischargeDate, ApptDate) values( '00509897','2014-07-21','2014-08-06')
insert into TEST(MRN, DischargeDate, ApptDate) values( '329907','2014-10-01','2014-11-20')
insert into TEST(MRN, DischargeDate, ApptDate) values( '727661','2014-12-27','2015-01-12')
insert into TEST(MRN, DischargeDate, ApptDate) values( '812229','2014-10-16','2015-02-25')
insert into TEST(MRN, DischargeDate, ApptDate) values( '644334','2014-10-17','2014-11-12')
insert into TEST(MRN, DischargeDate, ApptDate) values( '644334','2015-01-08','2015-01-23')
June 16, 2015 at 12:38 pm
Fairly straightforward, try this:
SELECT
MRN,
DischargeDate,
ApptDate,
DATEDIFF(dd,DischargeDate,ApptDate) AS DayDifference
From
Test
WHERE
DATEDIFF(dd,DischargeDate,ApptDate) <= 7
LOL, I feel like I'm answering a question for a former employer. (I used to be in Healthcare)
June 16, 2015 at 1:17 pm
Using Jun 1 and Jun 8 as your dates, DATEDIFF would meet 7 day criteria but includes weekends. Something like this might work?
DECLARE @StartDate DATE = '20150601'
,@EndDate DATE = '20150608'
SELECT DATEDIFF(DAY,@StartDate,@EndDate);
WITH Dates AS (
-- virtual tally table logic found here:
-- http://stackoverflow.com/questions/7824831/generate-dates-between-date-ranges
SELECT Dates = DATEADD(DAY, nbr - 1, @StartDate)
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
FROM sys.columns c
) nbrs
WHERE nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)
)
SELECTCOUNT(*)
FROMDates
WHEREDATEPART(WEEKDAY,Dates) NOT IN (1, 7); -- 1=SUN,7=SAT
_____________________________________________________________________
- Nate
June 16, 2015 at 2:17 pm
RP_DBA (6/16/2015)
Using Jun 1 and Jun 8 as your dates, DATEDIFF would meet 7 day criteria but includes weekends. Something like this might work?
DECLARE @StartDate DATE = '20150601',@EndDate DATE = '20150608'
SELECT DATEDIFF(DAY,@StartDate,@EndDate);
WITH Dates AS (
-- virtual tally table logic found here:
-- http://stackoverflow.com/questions/7824831/generate-dates-between-date-ranges
SELECT Dates = DATEADD(DAY, nbr - 1, @StartDate)
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
FROM sys.columns c
) nbrs
WHERE nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)
)
SELECTCOUNT(*)
FROMDates
WHEREDATEPART(WEEKDAY,Dates) NOT IN (1, 7); -- 1=SUN,7=SAT
Good Catch...at first I was wondering why you thought weekends even mattered until I re-read the Post Title. :crazy:
June 16, 2015 at 3:44 pm
I don't like to use WEEKDAY as it has dependencies on @@DATEFIRST. Maybe this setting-independent method instead:
SELECT *
FROM Test
WHERE DATEDIFF(DAY, DischargeDate, ApptDate) <= ( 8 +
CASE DATEDIFF(DAY, 0, DischargeDate) % 7
WHEN 4 THEN 2 --Fri
WHEN 5 THEN 2 --Sat
WHEN 6 THEN 1 --Sun
ELSE 0 END )
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 17, 2015 at 4:31 am
Thanx to all for your input. Scott's solution is the easiest to follow for me. What does the %7 do in the equation?
June 17, 2015 at 4:38 am
Pardon my ignorance but, what if it were 2 days? How would this solution read?
June 17, 2015 at 5:50 am
Would you mind explaining how this works? I've separated the different "components" but can't figure it out. I know that it is dividing the number of days since 1/1/1900 by 7, then returning the remainder. How does this effect the rest? How does the where clause work in this case?
June 17, 2015 at 7:09 am
NineIron (6/17/2015)
Would you mind explaining how this works? I've separated the different "components" but can't figure it out. I know that it is dividing the number of days since 1/1/1900 by 7, then returning the remainder. How does this effect the rest? How does the where clause work in this case?
The % operator is an example of modulo https://msdn.microsoft.com/en-us/library/ms190279.aspx
It's the remainder a number divided by another
Example below with comparison to division
SELECTN,
N / 7,
N / 7.0,
N % 7
FROM(VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14)) AS A(N)
In terms of its use here 1st Jan 1900 is a Monday (0), so you can use this to figure out the day
SELECTDATEDIFF(DAY,0,GETDATE()) % 7, --Today
DATEDIFF(DAY,0,'20150615') % 7 --Monday
June 17, 2015 at 7:41 am
Thanx.
June 17, 2015 at 9:23 am
Original code, for easy reference:
SELECT *
FROM Test
WHERE DATEDIFF(DAY, DischargeDate, ApptDate) <= ( 8 +
CASE DATEDIFF(DAY, 0, DischargeDate) % 7
WHEN 4 THEN 2 --Fri
WHEN 5 THEN 2 --Sat
WHEN 6 THEN 1 --Sun
ELSE 0 END )
As stated, the % 7 from a known/"base" Monday tells you the number of days past Monday that the DischargeDate is. If Discharge is a Monday, 0 will be returned (since that date is 0 days past Monday); for Tues, 1 will be returned; for Wed, 2 will be returned; etc.. Notice, though, that value will never change, regardless of DATEFIRST and/or language settings.
The 8 + ... is because you wanted to include only things within 7 non-weekend dates. If you just pop up the calendar on your phone/PC, you'll see that for Mon, that would be through the next Tues, for Tues, thru the next Wed, etc.. That works out to a datediff of 8 days. The adjustment is because if the starting date is a Fri, Sat or Sun, then weekend days will be in the date range, so the max allowed days must be increased to include those weekend days in the total days.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 17, 2015 at 9:33 am
Wonderful. I don't know how you folks come up with these solutions with so little code.
Can this be tweaked to include things within only 2 non-weekend days?
June 17, 2015 at 9:38 am
Sure. 8 works for 7 days, and 1 should work for 2 days. The weekend adjustment is the same.
SELECT *
FROM Test
WHERE DATEDIFF(DAY, DischargeDate, ApptDate) <= ( 1 +
CASE DATEDIFF(DAY, 0, DischargeDate) % 7
WHEN 4 THEN 2 --Fri
WHEN 5 THEN 2 --Sat
WHEN 6 THEN 1 --Sun
ELSE 0 END )
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 17, 2015 at 9:38 am
NineIron (6/17/2015)
Wonderful. I don't know how you folks come up with these solutions with so little code.Can this be tweaked to include things within only 2 non-weekend days?
Sounds like an exercise for you to try. Best way to learn the tricks is to see what happens when you start making changes. Any questions while doing it, just ask.
June 17, 2015 at 10:24 am
Thanx again.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply