How to return the result based on shift date?-CodePudding

DECLARE @year int = 2021, @month int = 12;

WITH numbers AS
(
    SELECT 1 AS value
    UNION ALL
    SELECT value   1 
    FROM numbers
    WHERE value   1 <= DAY(EOMONTH(DATEFROMPARTS(@year, @month, 1)))
)
SELECT 
    CONVERT(char(10), CAST(DATEFROMPARTS(@year, @month, numbers.value) AS datetime), 126) AS DATE, 
    CONVERT(char(10), CAST(DATEFROMPARTS(@year, @month, numbers.value) AS datetime), 126)   ' 08:00:00:00' AS StartA,
    CONVERT(char(10), CAST(DATEFROMPARTS(@year, @month, numbers.value) AS datetime), 126)   ' 15:59:59:00' AS EndA,
    CONVERT(char(10), CAST(DATEFROMPARTS(@year, @month, numbers.value) AS datetime), 126)   ' 16:00:00:00' AS StartB,
    CONVERT(char(10), CAST(DATEFROMPARTS(@year, @month, numbers.value) AS datetime), 126)   ' 23:59:59:00' AS EndB,
    CONVERT(char(10), CAST(DATEFROMPARTS(@year, @month, numbers.value) AS datetime)   1,126)   ' 00:00:00:00' AS StartC,
    CONVERT(char(10), CAST(DATEFROMPARTS(@year, @month, numbers.value) AS datetime)   1, 126)   ' 07:59:59:00' AS EndC
FROM 
    numbers

This is the query I wrote.

I want to arrange the 3 shifts based on date as per this screenshot:

Need to arrange the shifts like this image

CodePudding user response：

You can generate the list of date (with time as 00:00:00) using recursive cte and then join it to the shift information (start and end time) to produce the required result

DECLARE @year  INT = 2021, 
        @month INT = 12;

WITH 
-- list of dates of the month
dates AS
(
    SELECT  CONVERT(DATETIME, DATEFROMPARTS(@year, @month, 1)) AS [date] 
    UNION ALL
    SELECT  DATEADD(day, 1, CONVERT(DATETIME, [date]))
    FROM    dates
    WHERE   [date]  < EOMONTH(DATEFROMPARTS(@year, @month, 1))
),
-- shift start and end time
shifts AS
(
    SELECT  *
    FROM
    (
        VALUES
        ('A', 0, '08:00:00', '15:59:59'),
        ('B', 0, '16:00:00', '23:59:59'),
        ('C', 1, '00:00:00', '07:59:59')
    ) AS s ([Shift], [day], [Start], [End])
) 
SELECT  d.[date],
        s.[Shift],
        d.[date]   s.[day]   s.[Start] AS [Start],
        d.[date]   s.[day]   s.[End]   AS [End]
FROM    dates d
        CROSS APPLY shifts s
ORDER BY d.[date], s.[Shift]

CodePudding user response：

Just for fun, here's my similar approach:

DECLARE @windowStart DATETIME = '20200101',
        @windowEnd   DATETIME = '20211231';

;WITH shift_dates AS (
        SELECT 
           TOP (DATEDIFF(DAY, @windowStart, @windowEnd)   1)  
               shift_date = DATEADD(DAY, 
                                    ROW_NUMBER() OVER(ORDER BY a.object_id) - 1,
                                    @windowStart) 
          FROM sys.all_objects AS a
    CROSS JOIN sys.all_objects AS b  
),
shift_types AS (
    SELECT *
      FROM (VALUES ('A'),
                   ('B'),
                   ('C')
            ) AS x (shift_type)
),
shift_times AS (
    SELECT *
      FROM (VALUES (0,     28799),
                   (28800, 57599),
                   (57600, 86399)
           ) AS x (shift_start_sec, shift_end_sec)
)
    SELECT shift_date,
           shift_type,
           DATEADD(SECOND, shift_start_sec, shift_date) AS shift_start_time,
           DATEADD(SECOND, shift_end_sec,   shift_date) AS shift_end_time
      FROM shift_dates 
CROSS JOIN shift_types 
CROSS JOIN shift_times
  ORDER BY shift_date, 
           shift_type