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

Tamayo, Ashley 121 Reputation points
2020-10-20T22:24:06.71+00:00
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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,294 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,733 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,519 questions
{count} votes

2 answers

Sort by: Most helpful
  1. EchoLiu-MSFT 14,571 Reputation points
    2020-10-21T02:24:39.267+00:00

    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.


  2. Ronen Ariely 15,081 Reputation points
    2020-10-26T10:29:42.843+00:00

    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