时间戳

首页 > 解决方案 > 查找员工的缺勤日期和当前前后的一个日期

sql-server - 查找员工的缺勤日期和当前前后的一个日期

问题描述

我有以下示例数据：

--Table 1:
CREATE TABLE tbl_Emp_1
(
    EmpID INT,
    ColDate DATE
);

INSERT INTO tbl_Emp_1 VALUES(1,'2019-11-01');
INSERT INTO tbl_Emp_1 VALUES(2,'2019-11-02');
INSERT INTO tbl_Emp_1 VALUES(3,'2019-11-11');
INSERT INTO tbl_Emp_1 VALUES(4,'2019-11-12');
INSERT INTO tbl_Emp_1 VALUES(9,'2019-11-13');
INSERT INTO tbl_Emp_1 VALUES(6,'2019-11-16');
INSERT INTO tbl_Emp_1 VALUES(408,'2019-11-25');

--Table 2:
CREATE TABLE tbl_Emp_2
(
    EmpID INT,
    ColDate DATE
);

INSERT INTO tbl_Emp_2 VALUES(11,'2019-11-02');
INSERT INTO tbl_Emp_2 VALUES(22,'2019-11-06');
INSERT INTO tbl_Emp_2 VALUES(22,'2019-11-08');
INSERT INTO tbl_Emp_2 VALUES(33,'2019-11-10');
INSERT INTO tbl_Emp_2 VALUES(44,'2019-11-15');

--Table 3:
CREATE TABLE tbl_Emp_3
(
    EmpID INT,
    ColDate DATE
);

INSERT INTO tbl_Emp_3 VALUES(111,'2019-11-12');
INSERT INTO tbl_Emp_3 VALUES(222,'2019-11-16');
INSERT INTO tbl_Emp_3 VALUES(333,'2019-11-17');
INSERT INTO tbl_Emp_3 VALUES(444,'2019-11-19');
INSERT INTO tbl_Emp_3 VALUES(5,'2019-11-22');

--现在我将创建这些表的视图。

CREATE VIEW vw_Emp AS
SELECT *,1 AS TableID FROM tbl_Emp_1
UNION ALL
SELECT *,2 AS TableID FROM tbl_Emp_2
UNION ALL
SELECT *,3 AS TableID FROM tbl_Emp_3;

预期输出：

EmpID   ColDate     
--------------------------------
2       2019-11-02          ---TABLE 1 Starts
NULL    2019-11-03 - 2019-11-10
3       2019-11-11
9       2019-11-13
NULL    2019-11-14 - 2019-11-15
6       2019-11-16
NULL    2019-11-17 - 2019-11-24
408     2019-11-25
11      2019-11-02          ---TABLE 2 Data Starts
NULL    2019-11-03 - 2019-11-05
22      2019-11-06  
NULL    2019-11-07
22      2019-11-08
NULL    2019-11-09
33      2019-11-10
NULL    2019-11-11 - 2019-11-14
44      2019-11-15
111     2019-11-12      ---TABLE 3 Data Starts
NULL    2019-11-13 - 2019-11-15
222     2019-11-16 
333     2019-11-17
NULL    2019-11-18
444     2019-11-19  
NULL    2019-11-20 - 2019-11-21
5       2019-11-22

关于输出：显示员工的缺勤日期并显示这些日期之前和之后一个日期的emp数据（员工不存在日期）。

我的尝试：

DECLARE @TableID INT,
        @MinDate DATE,
        @MaxDate DATE;
DECLARE Cur_Get_MinMax1 CURSOR FOR 
SELECT TableID,
       (SELECT MIN(ColDate) FROM vw_Emp WHERE TableID = v1.TableID),
       (SELECT MAX(ColDate) FROM vw_Emp WHERE TableID = v1.TableID)
FROM vw_Emp v1
GROUP BY TableID;

IF OBJECT_ID('tempdb..#TempEmpData') IS NOT NULL 
DROP TABLE #TempEmpData;

CREATE TABLE #TempEmpData
(
    Dates DATE,
    TableID int
);

OPEN Cur_Get_MinMax1;

FETCH NEXT FROM Cur_Get_MinMax1 INTO 
@TableID, 
@MinDate,
@MaxDate;

WHILE @@FETCH_STATUS = 0
BEGIN

    PRINT(@TableID);
    PRINT(@MinDate);
    PRINT(@MaxDate);

    INSERT INTO #TempEmpData 
    SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
            Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate),
            @TableID
    FROM sys.all_objects a
    CROSS JOIN sys.all_objects b

    FETCH NEXT FROM Cur_Get_MinMax1 INTO 
    @TableID, 
    @MinDate,
    @MaxDate;

END;
CLOSE Cur_Get_MinMax1;
DEALLOCATE Cur_Get_MinMax1;

查询一： 

SELECT v.EmpID,t.Dates
FROM #TempEmpData t 
LEFT JOIN vw_Emp v ON v.ColDate = t.Dates AND v.TableID = t.TableID
ORDER BY t.TableID,t.Dates;

编辑：

查询 2：

;WITH CTE AS
(
    SELECT  DISTINCT TableID,Dates,EmpID,
            coalesce(stuff((select distinct CAST(MIN(Dates) as varchar(10))+'~'+ CAST(MAX(Dates) as varchar(10)) from #TempEmpData t1 where a.rr = 1 AND t1.Dates=a.Dates for xml path('')),1,0,''),cast(Dates as varchar(10))) Coldate
    FROM 
    (
        SELECT  v.EmpID, 
                t.Dates,
                t.TableID,
                RANK() OVER(ORDER BY v.EmpID) rr
        FROM vw_Emp v  
        RIGHT JOIN #TempEmpData t ON v.ColDate = t.Dates  AND v.TableID = t.TableID
        GROUP BY t.TableID,v.EmpID,t.Dates,v.TableID
    ) a
)
SELECT EmpID,ColDate 
FROM CTE
ORDER BY TableID,Dates

标签： sql-servertsqlsql-server-2012

解决方案

    SELECT CAST(NULL AS INT) AS EmpId, DATEADD(day, 1, PreviousDate) AS StartDate, DATEADD(day, -1, ColDate) AS EndDate
    FROM
    (
        SELECT ColDate, LAG(ColDate) OVER(ORDER BY ColDate) AS PreviousDate, LEAD(ColDate) OVER(ORDER BY ColDate) AS NextDate
        FROM (SELECT DISTINCT ColDate FROM dbo.tbl_Emp_1) AS src
    ) AS thedates
    WHERE ColDate <> DATEADD(day, 1, PreviousDate)



    SELECT CAST(NULL AS INT) AS EmpId, StartDate, EndDate
    FROM
    (
        SELECT DATEADD(day, 1, sd.StartDate) AS StartDate, DATEADD(day, -1, MIN(ed.EndDate)) AS EndDate
        FROM 
        (
            --start dates of missing ranges
            SELECT ColDate AS StartDate
            FROM dbo.tbl_Emp_1 as a
            WHERE NOT EXISTS(SELECT * FROM dbo.tbl_Emp_1 AS b WHERE b.ColDate = DATEADD(day, 1, a.ColDate))
        ) AS sd
        JOIN
        (
            --end dates of missing ranges
            SELECT ColDate AS EndDate
            FROM dbo.tbl_Emp_1 as a
            WHERE NOT EXISTS(SELECT * FROM dbo.tbl_Emp_1 AS b WHERE b.ColDate = DATEADD(day, -1, a.ColDate))
        ) AS ed ON sd.StartDate < ed.EndDate
        GROUP BY sd.StartDate
    ) AS emptyperiods

推荐阅读