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
這是我寫的查詢。
我想根據此螢屏截圖根據日期安排 3 個班次：
uj5u.com熱心網友回復：
您可以00:00:00使用遞回 cte生成日期串列（時間為），然后將join其生成到班次資訊（開始和結束時間）以產生所需的結果
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]
uj5u.com熱心網友回復：
只是為了好玩，這是我的類似方法：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/380124.html
標籤：sql sql-server 查询语句