question

TamayoAshley-9406 avatar image
0 Votes"
TamayoAshley-9406 asked EchoLiu-msft commented

How to assign label to dates that will alternate every two weeks in MS SQL

 DECLARE @Ashift DATETIME;
    
 SET @Ashift = '2019-01-01 08:00';
    
 WITH Shifts AS (
 SELECT DISTINCT
 CASE 
 WHEN ((DATEDIFF(HH,@Ashift,GETDATE()) / 24) % 3) + 1 = 1 THEN 'A'
 WHEN ((DATEDIFF(HH,@Ashift,GETDATE()) / 24) % 3) + 1 = 2 THEN 'B'
 WHEN ((DATEDIFF(HH,@Ashift,GETDATE()) / 24) % 3) + 1 = 3 THEN 'C' 
 END AS [Shift]
 )
 SELECT *
 FROM Shifts
 WHERE [Shift] IS NOT NULL


I have the above SQL for designating a letter to the corresponding day on a 3 day rotation. Is it possible to do something similar for 4 letters where 2 letters occur on one day (A) 0700-1900/ (B)1900-0700 and the other 2 occur another day (C) 0700-1900/ (D)1900-0700. The only problem is these 4 letters would need to repeat on a 2 on 2 off 3 on 2 off 2 on 3 off rotation. Below I've written out a full two weeks of how the letters would alternate. Times are depicted in military time. Thank you!

10/19/2020 0700-1900 = D
10/19/2020 1900-0700 = C
10/20/2020 0700-1900 = D
10/20/2020 1900-0700 = C
10/21/2020 0700-1900 = B
10/21/2020 1900-0700 = A
10/22/2020 0700-1900 = B
10/22/2020 1900-0700 = A
10/23/2020 0700-1900 = D
10/23/2020 1900-0700 = C
10/24/2020 0700-1900 = D
10/24/2020 1900-0700 = C
10/25/2020 0700-1900 = D
10/25/2020 1900-0700 = C

10/26/2020 0700-1900 = B
10/26/2020 1900-0700 = A
10/27/2020 0700-1900 = B
10/27/2020 1900-0700 = A
10/28/2020 0700-1900 = D
10/28/2020 1900-0700 = C
10/29/2020 0700-1900 = D
10/29/2020 1900-0700 = C
10/30/2020 0700-1900 = B
10/30/2020 1900-0700 = A
10/31/2020 0700-1900 = B
10/31/2020 1900-0700 = A
11/01/2020 0700-1900 = B
11/01/2020 1900-0700 = A

sql-server-generalsql-server-transact-sqlsql-server-reporting-services
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Regarding TSQL questions, I suggest you post on the sql-server-transact-sql forum.

Echo

1 Vote 1 ·

Regarding TSQL questions, I suggest you post on the sql-server-transact-sql forum.

What do your mean by "post on the sql-server-transact-sql forum"

All that is needed is to add tag and not to post anything more 🙂 No reason for duplicate threads

0 Votes 0 ·

Do you have any updates?
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered IanBates-4416 commented

Hi anonymous user

Please refer to:

 declare @test table (col1 date,col2 char(25))
 insert into @test values
 ('10/19/2020', '0700-1900'), 
 ('10/19/2020', '1900-0700'), 
 ('10/20/2020', '0700-1900'),
 ('10/20/2020', '1900-0700'),
 ('10/21/2020', '0700-1900'),
 ('10/21/2020', '1900-0700'),
 ('10/22/2020', '0700-1900'),
 ('10/22/2020', '1900-0700'),
 ('10/23/2020', '0700-1900'),
 ('10/23/2020', '1900-0700'),
 ('10/24/2020', '0700-1900'),
 ('10/24/2020', '1900-0700'),
 ('10/25/2020', '0700-1900'),
 ('10/25/2020', '1900-0700'),
 ('10/26/2020', '0700-1900'), 
 ('10/26/2020','1900-0700') ,
 ('10/27/2020', '0700-1900'), 
 ('10/27/2020', '1900-0700'), 
 ('10/28/2020', '0700-1900'), 
 ('10/28/2020', '1900-0700'), 
 ('10/29/2020','0700-1900'), 
 ('10/29/2020', '1900-0700'), 
 ('10/30/2020', '0700-1900'), 
 ('10/30/2020', '1900-0700'), 
 ('10/31/2020', '0700-1900'), 
 ('10/31/2020', '1900-0700'), 
 ('11/01/2020', '0700-1900'), 
 ('11/01/2020', '1900-0700'), 
    
 ('11/02/2020', '0700-1900'), 
 ('11/02/2020', '1900-0700'), 
 ('11/03/2020', '0700-1900'),
 ('11/03/2020', '1900-0700'),
 ('11/04/2020', '0700-1900'),
 ('11/04/2020', '1900-0700'),
 ('11/05/2020', '0700-1900'),
 ('11/05/2020', '1900-0700'),
 ('11/06/2020', '0700-1900'),
 ('11/06/2020', '1900-0700'),
 ('11/07/2020', '0700-1900'),
 ('11/07/2020', '1900-0700'),
 ('11/08/2020', '0700-1900'),
 ('11/08/2020', '1900-0700'),
 ('11/09/2020', '0700-1900'), 
 ('11/09/2020','1900-0700') ,
 ('11/10/2020', '0700-1900'), 
 ('11/10/2020', '1900-0700'), 
 ('11/11/2020', '0700-1900'), 
 ('11/11/2020', '1900-0700'), 
 ('11/12/2020','0700-1900'), 
 ('11/12/2020', '1900-0700'), 
 ('11/13/2020', '0700-1900'), 
 ('11/13/2020', '1900-0700'), 
 ('11/14/2020', '0700-1900'), 
 ('11/14/2020', '1900-0700'), 
 ('11/15/2020', '0700-1900'), 
 ('11/15/2020', '1900-0700') 
    
 ;with cte 
 as(select *,row_number() over(order by col1) rn from @test)
    
 select col1,col2,case when rn=1 or (rn-1)%28=0   then 'D'
                       when rn=2 or (rn-2)%28=0 then 'C'
    when rn=3 or (rn-3)%28=0  then 'D'
    when rn=4 or (rn-4)%28=0  then 'C'
    when rn=5 or(rn-5)%28=0   then 'B'
    when rn=6 or (rn-6)%28=0  then 'A'
    when rn=7 or (rn-7)%28=0  then 'B'
    when rn=8 or (rn-8)%28=0  then 'A'
    when rn=9 or (rn-9)%28=0  then 'D'
    when rn=10 or (rn-10)%28=0  then 'C'
    when rn=11 or (rn-11)%28=0  then 'D'
    when rn=12 or (rn-12)%28=0  then 'C'
    when rn=13 or (rn-13)%28=0  then 'D'
    when rn=14 or (rn-14)%28=0  then 'C'
    
        when rn=15 or (rn-15)%28=0  then 'B'
                       when rn=16 or (rn-16)%28=0  then 'A'
    when rn=17 or (rn-17)%28=0  then 'B'
    when rn=18 or (rn-18)%28=0 then 'A'
    when rn=19 or (rn-19)%28=0  then 'D'
    when rn=20 or (rn-20)%28=0  then 'C'
    when rn=21 or (rn-21)%28=0  then 'D'
    when rn=22 or (rn-22)%28=0 then 'C'
    when rn=23 or (rn-23)%28=0 then 'B'  
    when rn=24 or (rn-24)%28=0 then 'A'
    when rn=25 or (rn-25)%28=0 then 'B'
    when rn=26 or (rn-26)%28=0 then 'A'
    when rn=27 or (rn-27)%28=0 then 'B'
    when rn=28 or (rn-28)%28=0 then 'A' end tag
 from cte

33886-image.png33923-image.png

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



image.png (25.8 KiB)
image.png (27.1 KiB)
· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you! My goal is for the SQL to recognize what letter corresponds to the current day and time when the report is generated. When I run the query I provided it tells me today is C shift. I'm looking for an option that would provide a similar result but with the alternating schedule of 2 on 2 off 3 on 2 off 2 on 3 off rotation. If it simplifies things it is ok to say A/B and C/D are for the whole day instead of dividing into two different time periods per day.

0 Votes 0 ·

Could you provide an example of what the desired output would look like?

0 Votes 0 ·

![34365-shift-example.jpg][1] [1]: /answers/storage/temp/34365-shift-example.jpg I'm fairly new to SQL and I am not sure if this is even possible. I'm attaching the results I obtain when I currently run the syntax I have. I'd like to know if there is a way to modify it so that I will receive what shift is on today based on the 2 on 2 off 3 on 2 off 2 on 3 off rotation. I want to return a single result like currently happens on the 3 day rotation. i have an idea but I am unsure how to execute it. With the syntax in the case statement I provided above WHEN ((DATEDIFF(HH,@Ashift,GETDATE()) / 24) % 3)+1 = 1 THEN 'A' would it be possible write out the calculation for the two week period and instead of /24) % 3) + 1 = 1 write it to say it occurs for 24 hours every other week? 1/1/2020 = A/B 1/2/2020 = A/B 1/3/2020 = C/D 1/4/2020 = C/D 1/5/2020 = C/D 1/6/2020 = A/B 1/7/2020 = A/B 1/8/2020 = C/D 1/9/2020 = C/D 1/10/2020 = A/B 1/11/2020 = A/B 1/12/2020 = A/B

0 Votes 0 ·
Show more comments

Well, I am still in the fog. It is best to provide a minimal example (including related tables and inserted data) and expected output results. Otherwise we will keep playing the guessing game.
Echo

0 Votes 0 ·
pituach avatar image
0 Votes"
pituach answered EchoLiu-msft commented

Good day,

Your best option is to create a table in advance with all the marks according to your template. Make sure that you have index that fit the filter and then all you need is a simple select

Here is a full demo, but I have no idea what is the first date which start this template the you can change the value. I am using '2015-01-01 07:00' to start the circle which mean that this will be the first "D"

So, step one is a one-time job to create the table with the values

Step one: create accessories table



 DROP TABLE IF EXISTS T
 GO
 Create table T (StartDateTime Datetime2, EndDateTime Datetime2, Mark CHAR(1))
 GO
    
 DECLARE @StartDateTime DATETIME2, @EndDateTime DATETIME2
 SET @StartDateTime = CONVERT(DATETIME2, '2015-01-01 07:00', 120) -- Choose the first start date for the template where the circle start
 SET @EndDateTime   = CONVERT(DATETIME2, '2015-01-01 19:00', 120) -- Choose the first end date for the template
 ;With MyCTE as (
  SELECT TOP 2000 RN = (ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) )
  FROM SYS.all_objects t1
  CROSS JOIN sys.all_objects t2
 ),
 MyCTE02 as (
  SELECT 
  StartDateTime = DATEADD(DAY, (RN), @StartDateTime),
  EndDateTime = DATEADD(DAY, (RN), @EndDateTime),
  Mark = (((RN * 2) - 1)-1)%28
  FROM MyCTE
  UNION ALL
  SELECT 
  StartDateTime = DATEADD(DAY, (RN), @EndDateTime),
  EndDateTime = DATEADD(DAY, (RN), DATEADD(DAY,1,@StartDateTime)),
  Mark = ((RN * 2)-1)%28
  FROM MyCTE
 )
 -- SELECT * FROM MyCTE02
 -- ORDER BY StartDateTime
 INSERT T (StartDateTime, EndDateTime, Mark)
  SELECT StartDateTime, EndDateTime, 
  ---------------------------------------------------------------------
   -- Template: dcdcbabadcdcdcbabadcdcbababa
   -- Total of 28 in each circle
   -- Therefore, we need %28
  Mark = CASE
  WHEN Mark = 0  then 'd'
  WHEN Mark = 1  then 'c'
  WHEN Mark = 2  then 'd'
  WHEN Mark = 3  then 'c'
  WHEN Mark = 4  then 'b'
  WHEN Mark = 5  then 'a'
  WHEN Mark = 6  then 'b'
  WHEN Mark = 7  then 'a'
  WHEN Mark = 8  then 'd'
  WHEN Mark = 9  then 'c'
  WHEN Mark = 10 then 'd'
  WHEN Mark = 11 then 'c'
  WHEN Mark = 12 then 'd'
  WHEN Mark = 13 then 'c'
  WHEN Mark = 14 then 'b'
  WHEN Mark = 15 then 'a'
  WHEN Mark = 16 then 'b'
  WHEN Mark = 17 then 'a'
  WHEN Mark = 18 then 'd'
  WHEN Mark = 19 then 'c'
  WHEN Mark = 20 then 'd'
  WHEN Mark = 21 then 'c'
  WHEN Mark = 22 then 'b'
  WHEN Mark = 23 then 'a'
  WHEN Mark = 24 then 'b'
  WHEN Mark = 25 then 'a'
  WHEN Mark = 26 then 'b'
  WHEN Mark = 27 then 'a'
  END
  FROM MyCTE02
  --ORDER BY StartDateTime
 GO 
    
 select * from T
 ORDER BY StartDateTime
 GO

Note! you should add index on the table according to the filter!



We are ready to use the accessories table : notice how simple the query now


 DECLARE @Ashift DATETIME
 SET @Ashift = CONVERT(DATETIME2, '2020-01-01 08:00', 120)
 SELECT mark from  T where StartDateTime <= @Ashift and EndDateTime >= @Ashift
 GO


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for this! I need to confirm with the company that maintains our software/server that I have the authority to create a table. I suspect I don't though. Once I am able to get that clarified I will update here.

0 Votes 0 ·

I'm glad you got useful suggestions and hope your problem is solved!

Echo

0 Votes 0 ·