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