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

Question

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

Answer 1

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  

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.

Answer 2

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