SELECT语句显示丢失的记录(简单的问题)

我需要一些T-SQL,将显示丢失的记录。

下面是一些示例数据:

Emp 1 01/01/2010 02/01/2010 04/01/2010 06/01/2010 Emp 2 02/01/2010 04/01/2010 05/01/2010 etc...

我得知道

Emp 1 is missing 03/01/2010 05/01/2010 Emp 2 is missing 01/01/2010 03/01/2010 06/01/2010

范围检查将开始与今天的日期,并回到6个月。

在这个例子中,可以说,今天的日期是2010年6月12日因此范围将是01/01/2010直通06/01/2010。

这一天总是会在数据中的第一个。

感谢一大堆。 :)

格哈德·魏斯
大湖地区的.NET用户组的秘书
GANG近期会议| GANG LinkedIn集团

--------------解决方案-------------

尝试这个:

DECLARE @Employees table (DateOf datetime, EmployeeID int)
INSERT @Employees VALUES ('01/01/2010',1)
INSERT @Employees VALUES ('02/01/2010',1)
INSERT @Employees VALUES ('04/01/2010',1)
INSERT @Employees VALUES ('06/01/2010',1)
INSERT @Employees VALUES ('02/01/2010',2)
INSERT @Employees VALUES ('04/01/2010',2)
INSERT @Employees VALUES ('05/01/2010',2)

--I was unsure of the data in the question
--this gives first day of each month for last six months
DECLARE @StartDate datetime
,@EndDate datetime
SELECT @StartDate=DATEADD(month,-6,DATEADD(month,DATEDIFF(month,0,GETDATE()),0) )
,@EndDate=GETDATE()

;with AllDates AS
(
SELECT @StartDate AS DateOf
UNION ALL
SELECT DateAdd(month,1,DateOf)
FROM AllDates
WHERE DateOf<@EndDate
)
SELECT
dt.DateOf,dt.EmployeeID
FROM (SELECT DISTINCT
a.DateOf,e.EmployeeID
FROM AllDates a
CROSS JOIN (SELECT DISTINCT EmployeeID FROM @Employees) e
) dt
LEFT OUTER JOIN @Employees ee ON dt.EmployeeID=ee.EmployeeID AND dt.DateOf=ee.DateOf
WHERE ee.EmployeeID IS NULL
ORDER BY dt.EmployeeID,dt.DateOf

输出:

DateOf EmployeeID
----------------------- -----------
2009-10-01 00:00:00.000 1
2009-11-01 00:00:00.000 1
2009-12-01 00:00:00.000 1
2010-03-01 00:00:00.000 1
2010-05-01 00:00:00.000 1
2009-10-01 00:00:00.000 2
2009-11-01 00:00:00.000 2
2009-12-01 00:00:00.000 2
2010-01-01 00:00:00.000 2
2010-03-01 00:00:00.000 2

(10 row(s) affected)

这将每天做的最后六个月内,只是将这一在上面,如果这是你想要的:

DECLARE @StartDate datetime
,@EndDate datetime
SELECT @StartDate=DATEADD(month,-6,GETDATE())
,@EndDate=GETDATE()
;with AllDates AS
(
SELECT @StartDate AS DateOf
UNION ALL
SELECT DateOf+1
FROM AllDates
WHERE DateOf<@EndDate
)
SELECT * FROM AllDates
--OPTION (MAXRECURSION 500) --uncomment and increase if the date range needs more rows

填写临时表的日期范围和外部连接的临时表到你的Emp *表,只返回有*的Emp表的相应行空从临时表中的记录

如果你只打算回一个固定的#个月,就可以precalc那些“首月”日期和离开加入到你的员工数据:

SELECT d.DT, CASE WHEN e.DT IS NULL THEN 1 ELSE 0 END AS IsMissing
FROM (
SELECT DATEADD(m, DATEDIFF(m, 0, CURRENT_TIMESTAMP), 0) AS DT
UNION
SELECT DATEADD(m, DATEDIFF(m, 0, CURRENT_TIMESTAMP) - 1, 0)
UNION
SELECT DATEADD(m, DATEDIFF(m, 0, CURRENT_TIMESTAMP) - 2, 0)
UNION
SELECT DATEADD(m, DATEDIFF(m, 0, CURRENT_TIMESTAMP) - 3, 0)
UNION
SELECT DATEADD(m, DATEDIFF(m, 0, CURRENT_TIMESTAMP) - 4, 0)
UNION
SELECT DATEADD(m, DATEDIFF(m, 0, CURRENT_TIMESTAMP) - 5, 0)
) AS d
LEFT JOIN EmployeeDates e ON d.DT = e.DT AND e.EmpID = 1

分类:SQL服务器 时间:2015-03-15 人气:6
本文关键词: SQL Server中,TSQL
分享到:

相关文章

Copyright (C) 55228885.com, All Rights Reserved.

55228885 版权所有 京ICP备15002868号

processed in 0.960 (s). 10 q(s)