Find absent dates of employee and one date before & after present

Question

I have the following sample data:

--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');

--Now I will create View of these tables.

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;

Expected Output:

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

About the output: Display absent dates of Employee and display emp data of one date before and after those dates(employee not exists dates).

My try:

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;

Query 1:

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;

Edit:

Query 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

Squirrel · Accepted Answer

this uses window function LAG() and LEAD() to find previous and next ColDate based on ColDate ordering.

The first query returns the before and after row when a discontinued date is encounter. The second query returns the date range of the discontinued date.

; with 
tbl_Emp as
(
    select  tbl = 1, EmpID, ColDate from tbl_Emp_1  
    union all
    select  tbl = 2, EmpID, ColDate from tbl_Emp_2  
    union all
    select  tbl = 3, EmpID, ColDate from tbl_Emp_3
),
cte as
(
    select  *,
            prevColDate = LAG(ColDate) over (partition by tbl order by ColDate),
            nextColDate = LEAD(ColDate) over (partition by tbl order by ColDate)
    from    tbl_Emp
)
-- first query
select  c.tbl, 
        c.EmpID, 
        c.ColDate, 
        EndDate     = NULL
from    cte c
where   c.ColDate   <> dateadd(day, +1, prevColDate)
or      c.ColDate   <> dateadd(day, -1, nextColDate)

union all

-- second query
select  c.tbl, 
        EmpID       = NULL,
        ColDate     = dateadd(day, 1, c.ColDate),
        EndDate     = dateadd(day, -1, nextColDate)
from    cte c
where   c.ColDate   <> dateadd(day, -1, nextColDate)

order by tbl, ColDate;

Note : i didn't concatenate the ColDate and EndDate as what you have shown in your expected result.

Find absent dates of employee and one date before & after present

Answers (2)

Related Questions

Find absent dates of employee and one date before &amp; after present

Answers (2)

Related Questions

Find absent dates of employee and one date before & after present