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