July 16, 2018 at 3:11 am
I"m calculating shifts time between two date range. the following query works but sometimes it doesn't work if the end date is before 12:00 midday. The last date entry is not being calculated.
CREATE TABLE #dates
(
Code CHAR(6)
,PeriodStart DATETIME
,PeriodEnd DATETIME
);
INSERT INTO #dates
VALUES ('DA6557', '2017-11-03 13:47:54.383', '2017-11-07 08:30:00.000');
DECLARE @MorningShiftStart TIME;
SET @MorningShiftStart = '08:00';
DECLARE @AfterNonShiftEnd TIME;
SET @AfterNonShiftEnd = '17:00';
WITH Numbers
AS ( -- add more numbers here to increase your tolerance for down time
SELECT n
FROM ( VALUES ( 0), ( 1), ( 2), ( 3), ( 4), ( 5), ( 6) ) AS V (n)
),
Shifts
AS (SELECT @MorningShiftStart AS ShiftStart
,@AfterNonShiftEnd AS ShiftEnd
),
DayShifted
AS (SELECT ShiftStart
,ShiftEnd
,CASE WHEN ShiftStart >= ShiftEnd THEN 1
ELSE 0 -- we will add 1 if the shift ends the next day
END AS DayShifted
FROM Shifts
)
SELECT d.Code
,CASE -- takes the later of period start time and shift start time
WHEN d.PeriodStart > CAST(CAST(d.PeriodStart AS DATE) AS DATETIME)
+ v.n + CAST(f.ShiftStart AS DATETIME)
THEN d.PeriodStart
ELSE CAST(CAST(d.PeriodStart AS DATE) AS DATETIME) + v.n
+ CAST(f.ShiftStart AS DATETIME)
END AS MyStart
,CASE -- takes the earlier of period end time and shift end time
WHEN d.PeriodEnd < CAST(CAST(d.PeriodStart AS DATE) AS DATETIME)
+ v.n + CAST(f.ShiftEnd AS DATETIME) + f.DayShifted
THEN d.PeriodEnd
ELSE CAST(CAST(d.PeriodStart AS DATE) AS DATETIME) + v.n
+ CAST(f.ShiftEnd AS DATETIME) + f.DayShifted
END AS MyEnd
FROM #dates d
JOIN Numbers v ON d.PeriodEnd >= d.PeriodStart + v.n
JOIN DayShifted f ON CASE WHEN d.PeriodStart > CAST(CAST(d.PeriodStart AS DATE) AS DATETIME)
+ v.n
+ CAST(f.ShiftStart AS DATETIME)
THEN d.PeriodStart
ELSE CAST(CAST(d.PeriodStart AS DATE) AS DATETIME)
+ v.n
+ CAST(f.ShiftStart AS DATETIME)
END >= d.PeriodStart
AND CASE -- also need to check we haven't gone past the period end
WHEN d.PeriodStart > CAST(CAST(d.PeriodStart AS DATE) AS DATETIME)
+ v.n
+ CAST(f.ShiftStart AS DATETIME)
THEN d.PeriodStart
ELSE CAST(CAST(d.PeriodStart AS DATE) AS DATETIME)
+ v.n
+ CAST(f.ShiftStart AS DATETIME)
END <= d.PeriodEnd
AND CASE WHEN d.PeriodEnd < CAST(CAST(d.PeriodStart AS DATE) AS DATETIME)
+ v.n
+ CAST(f.ShiftEnd AS DATETIME)
+ f.DayShifted THEN d.PeriodEnd
ELSE CAST(CAST(d.PeriodStart AS DATE) AS DATETIME)
+ v.n
+ CAST(f.ShiftEnd AS DATETIME)
+ f.DayShifted
END <= d.PeriodEnd;
July 16, 2018 at 6:49 am
I changed the join to numbers to cast the values as dates and it returns the 5th line I believe you are looking for. Also, I changed the data to Use DateAdd. I'm not sure what your expected outcome was since you didn't mention it.
Drop TABLE if exists #dates
go
CREATE TABLE #dates
(
code CHAR(6),
periodstart DATETIME,
periodend DATETIME
);
INSERT INTO #dates
VALUES ('DA6557',
'2017-11-03 13:47:54.383',
'2017-11-07 08:30:00.000');
DECLARE @MorningShiftStart TIME;
SET @MorningShiftStart = '08:00';
DECLARE @AfterNonShiftEnd TIME;
SET @AfterNonShiftEnd = '17:00';
WITH numbers
AS (-- add more numbers here to increase your tolerance for down time
SELECT n
FROM ( VALUES ( 0),
( 1),
( 2),
( 3),
( 4),
( 5),
( 6),
(7) ) AS V (n)),
shifts
AS (SELECT @MorningShiftStart AS ShiftStart,
@AfterNonShiftEnd AS ShiftEnd),
dayshifted
AS (SELECT shiftstart,
shiftend,
CASE
WHEN shiftstart >= shiftend THEN 1
ELSE 0 -- we will add 1 if the shift ends the next day
END AS DayShifted
FROM shifts)
SELECT d.code,
CASE -- takes the later of period start time and shift start time
WHEN d.periodstart > Cast(DateAdd(day, v.n, Cast(d.periodstart AS DATE)) AS DATETIME) + Cast(f.shiftstart AS DATETIME)
THEN d.periodstart
ELSE Cast(DateAdd(day, v.n, Cast(d.periodstart AS DATE)) AS DATETIME) + Cast(f.shiftstart AS DATETIME)
END AS MyStart,
CASE -- takes the earlier of period end time and shift end time
WHEN d.periodend < Cast(DateAdd(day, v.n, Cast(d.periodstart AS DATE))AS DATETIME) + Cast(f.shiftend AS DATETIME) + f.dayshifted
THEN d.periodend
ELSE Cast(DateAdd(day, v.n, Cast(d.periodstart AS DATE)) AS DATETIME) + Cast(f.shiftend AS DATETIME) + f.dayshifted
END AS MyEnd
FROM #dates d
left join numbers v
ON cast(d.periodend as date) >= DateAdd(day, v.n, cast(d.periodstart as date))
left join dayshifted f
ON CASE
WHEN d.periodstart > Cast(DateAdd(day, v.n, Cast(d.periodstart AS DATE)) AS DATETIME) + Cast(f.shiftstart AS DATETIME) THEN
d.periodstart
ELSE Cast(DateAdd(day, v.n, Cast(d.periodstart AS DATE)) AS DATETIME) + Cast(f.shiftstart AS DATETIME)
END >= d.periodstart
AND CASE -- also need to check we haven't gone past the period end
WHEN d.periodstart > Cast(DateAdd(day, v.n, Cast(d.periodstart AS DATE)) AS DATETIME) + Cast(f.shiftstart AS DATETIME)
THEN
d.periodstart
ELSE Cast(DateAdd(day, v.n, Cast(d.periodstart AS DATE)) AS DATETIME) + Cast(f.shiftstart AS DATETIME)
END <= d.periodend
AND CASE
WHEN d.periodend < Cast(DateAdd(day, v.n, Cast(d.periodstart AS DATE)) AS DATETIME) + Cast(f.shiftend AS DATETIME)
+ f.dayshifted THEN d.periodend
ELSE Cast(DateAdd(day, v.n, Cast(d.periodstart AS DATE)) AS DATETIME) + Cast(f.shiftend AS DATETIME) + f.dayshifted
END <= d.periodend;
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply