补全日期范围并查询

最新推荐文章于 2021-04-03 18:51:56 发布

吉普赛的歌

最新推荐文章于 2021-04-03 18:51:56 发布

阅读量423

点赞数

分类专栏： SQL Server - 练习

本文链接：https://blog.csdn.net/yenange/article/details/78193430

版权

SQL Server - 练习专栏收录该内容

49 篇文章 1 订阅

订阅专栏

原贴：点击打开链接

/*
ID   产品ID      计划开始日期      开始班次       计划结束日期     结束班次
1     P111          2017-10-10          早班              2017-10-12         晚班

2     P112          2017-10-10          晚班              2017-10-12         早班

希望得到结果：
比如 2017-10-10 早班的记录：
ID   产品ID  
1     P111  

比如 2017-10-10 晚班的记录：
ID   产品ID  
1     P111  
2     P112 

比如 2017-10-12 晚班的记录：
ID   产品ID  
1     P111 
*/
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
SET NOCOUNT ON
CREATE TABLE t(Id INT,productId VARCHAR(20),planBeginTime DATETIME,beginFlag NCHAR(2),planEndTime DATETIME,endFlag NCHAR(2))
INSERT INTO t VALUES (1,'P111','2017-10-10','早班','2017-10-12','晚班')
INSERT INTO t VALUES (2,'P112','2017-10-10','晚班','2017-10-12','早班')

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
;WITH cte(Id,productId,planTime,flag ) AS (
	SELECT Id,productId,DATEADD(DAY,sv.number,planBeginTime),tmp.flag 
	FROM t CROSS APPLY MASTER.dbo.spt_values AS sv 
	CROSS APPLY (
		SELECT '早班' as flag 
		UNION ALL
		SELECT '晚班' as flag 
	) AS tmp
	WHERE sv.[type]='P' AND sv.number BETWEEN 0 AND DATEDIFF(DAY,t.planBeginTime,t.planEndTime)
)
SELECT 
* 
INTO #tmp
FROM cte a WHERE NOT EXISTS (
	SELECT * FROM t b WHERE b.id=a.id AND b.planBeginTime=a.planTime AND b.beginFlag='晚班' AND a.flag='早班'
) AND NOT EXISTS (
	SELECT * FROM t c WHERE c.id=a.id AND c.planEndTime=a.planTime AND c.endFlag='早班' AND a.flag='晚班'
)
/*
Id          productId            planTime                flag
----------- -------------------- ----------------------- ----
1           P111                 2017-10-10 00:00:00.000 早班
1           P111                 2017-10-10 00:00:00.000 晚班
1           P111                 2017-10-11 00:00:00.000 早班
1           P111                 2017-10-11 00:00:00.000 晚班
1           P111                 2017-10-12 00:00:00.000 早班
1           P111                 2017-10-12 00:00:00.000 晚班
2           P112                 2017-10-10 00:00:00.000 晚班
2           P112                 2017-10-11 00:00:00.000 早班
2           P112                 2017-10-11 00:00:00.000 晚班
2           P112                 2017-10-12 00:00:00.000 早班
*/
SELECT Id,productId FROM #tmp WHERE planTime='2017-10-10' AND flag='早班'
/*
Id          productId
----------- --------------------
1           P111
*/
SELECT Id,productId FROM #tmp WHERE planTime='2017-10-10' AND flag='晚班'
/*
Id          productId
----------- --------------------
1           P111
2           P112
*/
SELECT Id,productId FROM #tmp WHERE planTime='2017-10-12' AND flag='晚班'
/*
Id          productId
----------- --------------------
1           P111
*/
DROP TABLE #tmp