Using Mod to Generate Work Shifts

• joe 97933

  SSC Enthusiast

  Points: 139

  More actions

  December 21, 2016 at 10:37 pm

  #333087

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

• Carlo Romagnano

  SSC-Insane

  Points: 22772

  More actions

  December 22, 2016 at 1:06 am

  #1919268

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

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

  WHEN 0 .....

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  December 22, 2016 at 2:05 am

  #1919273

  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

• Dohsan

  SSCrazy

  Points: 2972

  More actions

  December 22, 2016 at 2:09 am

  #1919274

  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;

• Carlo Romagnano

  SSC-Insane

  Points: 22772

  More actions

  December 22, 2016 at 3:26 am

  #1919286

  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!

• BarneyL

  Ten Centuries

  Points: 1044

  More actions

  December 22, 2016 at 5:02 am

  #1919299

  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.

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  December 22, 2016 at 6:05 am

  #1919314

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

• mlandry

  SSC Veteran

  Points: 283

  More actions

  December 22, 2016 at 9:22 am

  #1919372

  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)

• mlandry

  SSC Veteran

  Points: 283

  More actions

  December 22, 2016 at 9:29 am

  #1919375

  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.

• Carlo Romagnano

  SSC-Insane

  Points: 22772

  More actions

  December 22, 2016 at 9:49 am

  #1919382

  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.

• Nick Ryan

  SSCrazy

  Points: 2237

  More actions

  December 22, 2016 at 9:51 am

  #1919383

  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.

• mlandry

  SSC Veteran

  Points: 283

  More actions

  December 22, 2016 at 10:21 am

  #1919392

  Demonstrates good knowledge of ASCII

• jwmott

  SSC Eights!

  Points: 979

  More actions

  December 22, 2016 at 1:26 pm

  #1919421

  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.

• mlandry

  SSC Veteran

  Points: 283

  More actions

  December 22, 2016 at 2:36 pm

  #1919434

  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.

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  December 23, 2016 at 9:48 am

  #1919533

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