Using Mod to Generate Work Shifts

  • Comments posted to this topic are about the item Using Mod to Generate Work Shifts

  • SET @compareDate = DATEADD(HOUR, -7, @ShiftDate)

    SELECT CASE ABS(DATEDIFF(DD, @compareDate, @baseDate)) % 3

    WHEN 0 .....

  • 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.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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;

  • 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!

  • 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.

  • I've rarely used MOD but seems like it should have some good uses.

  • 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)

  • 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.

  • 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.

  • 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.

  • Demonstrates good knowledge of ASCII 😉

  • 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.

  • 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.

  • 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) "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".

Viewing 15 posts - 1 through 15 (of 25 total)

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