mssql怎么填充剩余时间

[kanmaogou]

一天24小时，已知工作时间，如何得到剩余空闲时间
表A:
姓名 工作开始时间 工作结束时间
张三 07:10 12:20
张三 12:30 20:00
张三 21:00 23:00
李四 07:00 20:00

希望得到数据：
姓名 空闲开始时间 空闲结束时间
张三 00:00 07:09
张三 12:21 12:29
张三 20:01 20:59
张三 23:01 24:00
李四 00:00 06:59
李四 20:01 24:00

谢谢！

[RINK_1]

IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(NAME VARCHAR(10),
 START_TIME TIME,
 END_TIME TIME)

INSERT INTO #T
SELECT 'A','07:10','12:20' UNION ALL
SELECT 'A','12:30','20:00' UNION ALL
SELECT 'A','21:00','23:00' UNION ALL
SELECT 'B','07:00','20:00'

WITH CTE
AS
(SELECT *,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY START_TIME) AS SEQ_1,
COUNT(1) OVER (PARTITION BY NAME) AS QTY
FROM #T)

SELECT *
FROM
(SELECT A.NAME,DATEADD(MINUTE,1,A.END_TIME) AS REST_START,
CASE WHEN A.END_TIME<>'23:59' AND ISNULL(B.NAME,'')='' THEN '23:59' ELSE DATEADD(MINUTE,-1,B.START_TIME) END AS REST_END
FROM CTE A
LEFT JOIN CTE B ON A.NAME=B.NAME AND A.SEQ_1=B.SEQ_1-1
UNION ALL
SELECT NAME,'00:00',DATEADD(MINUTE,-1,START_TIME) FROM CTE WHERE SEQ_1=1 AND START_TIME<>'00:00:00') AS A
ORDER BY NAME,REST_START

[kanmaogou]

引用 1 楼 RINK_1 的回复:

IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(NAME VARCHAR(10),
 START_TIME TIME,
 END_TIME TIME)

INSERT INTO #T
SELECT 'A','07:10','12:20' UNION ALL
SELECT 'A','12:30','20:00' UNION ALL
SELECT 'A','21:00','23:00' UNION ALL
SELECT 'B','07:00','20:00'

WITH CTE
AS
(SELECT *,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY START_TIME) AS SEQ_1,
COUNT(1) OVER (PARTITION BY NAME) AS QTY
FROM #T)

SELECT *
FROM
(SELECT A.NAME,DATEADD(MINUTE,1,A.END_TIME) AS REST_START,
CASE WHEN A.END_TIME<>'23:59' AND ISNULL(B.NAME,'')='' THEN '23:59' ELSE DATEADD(MINUTE,-1,B.START_TIME) END AS REST_END
FROM CTE A
LEFT JOIN CTE B ON A.NAME=B.NAME AND A.SEQ_1=B.SEQ_1-1
UNION ALL
SELECT NAME,'00:00',DATEADD(MINUTE,-1,START_TIME) FROM CTE WHERE SEQ_1=1 AND START_TIME<>'00:00:00') AS A
ORDER BY NAME,REST_START

谢谢大神