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.
[font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]
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';
SELECT D.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
APPLY dbo.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';
SELECT D.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
APPLY dbo.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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply