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 24 total)

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