补全日期范围并查询

来源：互联网发布：腾讯绿标域名跳转代码编辑：程序博客网时间：2024/05/18 00:33
原贴：点击打开链接
/*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 tempdbGOIF OBJECT_ID('t') IS NOT NULL DROP TABLE tGOSET NOCOUNT ONCREATE 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 ALLSELECT '晚班' as flag ) AS tmpWHERE sv.[type]='P' AND sv.number BETWEEN 0 AND DATEDIFF(DAY,t.planBeginTime,t.planEndTime))SELECT * INTO #tmpFROM 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           P1112           P112*/SELECT Id,productId FROM #tmp WHERE planTime='2017-10-12' AND flag='晚班'/*Id          productId----------- --------------------1           P111*/DROP TABLE #tmp
阅读全文
0 0