December 21, 2016 at 10:37 pm
Comments posted to this topic are about the item Using Mod to Generate Work Shifts
December 22, 2016 at 1:06 am
SET @compareDate = DATEADD(HOUR, -7, @ShiftDate)
SELECT CASE ABS(DATEDIFF(DD, @compareDate, @baseDate)) % 3
WHEN 0 .....
December 22, 2016 at 2:05 am
That's not bad, Joe - I think MOD is the way to go with this.
The code can be trimmed down and tuned a little:
SELECT ShiftLabel = CASE WHEN n = 0 THEN 'B' WHEN n = 1 THEN 'A' WHEN n = 2 THEN 'C' END
FROM (SELECT n = DATEDIFF(DAY,0,DATEADD(HOUR,-7,@ShiftDate))%3) d
Also, I'd recommend you convert it to an inline function for performance.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 22, 2016 at 2:09 am
Could possibly remove the case
CREATE FUNCTION [dbo].[ShiftCalc]
(
@ShiftTime DATETIME,
@ShiftStart DATETIME,
@NumOfShifts TINYINT
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
SELECT
OnShift = CHAR(ABS(DATEDIFF(DAY,@ShiftStart,DATEADD(HOUR,-DATEPART(HOUR,@ShiftStart), @ShiftTime))) % @NumOfShifts + 65);
DECLARE @ShiftAStart DATETIME = '2006-01-01T07:00:00';
SELECTD.Incident,
CA1.OnShift
FROM(
VALUES(CAST('2006-01-01T07:00:00' AS DATETIME)),
('2006-01-01T17:00:00'),
('2006-01-02T06:00:00'),
('2006-01-02T07:00:00'),
('2006-01-02T23:00:00'),
('2006-01-03T07:00:00'),
('2006-01-03T09:00:00'),
('2006-01-04T07:00:00')
) AS D(Incident)
CROSS
APPLYdbo.ShiftCalc(D.Incident,@ShiftAStart,3) AS CA1;
December 22, 2016 at 3:26 am
Dohsan (12/22/2016)
Could possibly remove the case
CREATE FUNCTION [dbo].[ShiftCalc]
(
@ShiftTime DATETIME,
@ShiftStart DATETIME,
@NumOfShifts TINYINT
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
SELECT
OnShift = CHAR(ABS(DATEDIFF(DAY,@ShiftStart,DATEADD(HOUR,-DATEPART(HOUR,@ShiftStart), @ShiftTime))) % @NumOfShifts + 65);
GO
DECLARE @ShiftAStart DATETIME = '2006-01-01T07:00:00';
SELECTD.Incident,
CA1.OnShift
FROM(
VALUES(CAST('2006-01-01T07:00:00' AS DATETIME)),
('2006-01-01T17:00:00'),
('2006-01-02T06:00:00'),
('2006-01-02T07:00:00'),
('2006-01-02T23:00:00'),
('2006-01-03T07:00:00'),
('2006-01-03T09:00:00'),
('2006-01-04T07:00:00')
) AS D(Incident)
CROSS
APPLYdbo.ShiftCalc(D.Incident,@ShiftAStart,3) AS CA1;
+1
Perfect!
December 22, 2016 at 5:02 am
This feels a little risky to me as it depends on that shift cycle never changing or slipping out of sequence. Were I running this I'd be more comfortable using the technique to populate a calendar table which could be edited and then reporting from that (of course the great thing about functions is you can change how they work at a later date without breaking code that uses them).
Mod generally is a useful feature any time you need a repeating cycle though and this is a nice example of that.
December 22, 2016 at 6:05 am
I've rarely used MOD but seems like it should have some good uses.
December 22, 2016 at 9:22 am
Replace your last SELECT statement with this little "trick" I learned about 40 years ago (literally) -- loose the CASE entirely --
SELECT SUBSTRING('ABC', ABS(DATEDIFF(DD, @compareDate, @baseDate)) % 3 + 1, 1)
December 22, 2016 at 9:29 am
CHAR(...) + 65 is far too dependent upon the shift labels A, B, C. If they changed to non-contiguous strings, then this is out the window.
December 22, 2016 at 9:49 am
mlandry (12/22/2016)
CHAR(...) + 65 is far too dependent upon the shift labels A, B, C. If they changed to non-contiguous strings, then this is out the window.
Old "C" language trick.
December 22, 2016 at 9:51 am
Just a semi-related note to the original article writer and anybody else reading...
As demonstrated above by Dohsan: always, always, always write date strings in International/Standard format, never use a local representation. Work on the principle that at some point in time your code, even if it's just example code, will be run using on a system with a different date format to yours.
December 22, 2016 at 10:21 am
Demonstrates good knowledge of ASCII 😉
December 22, 2016 at 1:26 pm
Do I have this wrong? if a baseDate of 1/1/2006 at 07:00 represents the beginning of Shift A, then the shift date of 12/31/2005 at 06:00 represents shift B. 12/31/2005 at 07:00 through 1/1/2006 at 6:59 would be shift C, and 12/30/2005 07:00 through 12/31/2005 06:59 would represent shift B.
December 22, 2016 at 2:36 pm
jwmott (12/22/2016)
Do I have this wrong? if a baseDate of 1/1/2006 at 07:00 represents the beginning of Shift A, then the shift date of 12/31/2005 at 06:00 represents shift B.
You're correct. The original code is wrong.
December 23, 2016 at 9:48 am
Here's the in-line code, hopefully it's trivial to convert it to an itvf once its logic has been verified:
DECLARE @baseDate DATETIME = '1/1/2006 07:00:00';
SELECT
@baseDate AS BaseDate,
ShiftDate,
CASE WHEN BaseDays >= ShiftDays
THEN SUBSTRING('ACB', (BaseDays - ShiftDays) % 3 + 1, 1)
ELSE SUBSTRING('ABC', ABS(BaseDays - ShiftDays) % 3 + 1, 1)
END AS ShiftCode
FROM(
VALUES(CAST('2006-01-01T07:00:00' AS DATETIME)),
('2006-01-01T17:00:00'),
('2006-01-02T06:00:00'),
('2006-01-02T07:00:00'),
('2006-01-02T23:00:00'),
('2005-12-31T23:00:00'),
('2006-01-03T07:00:00'),
('2006-01-03T09:00:00'),
('2006-01-04T07:00:00')
) AS D(shiftdate)
CROSS APPLY (
SELECT DATEDIFF(HOUR, '19000101 07:00', @baseDate) / 24 AS BaseDays,
DATEDIFF(HOUR, '19000101 07:00', shiftDate) / 24 AS ShiftDays
) AS AssignAliasNames1
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply