Get all dates between provided dates

Question

I have this table and sample data. I want to get the entire month's or specific dates attendance and information like hours he worked or days he was absent.

CREATE TABLE Attendance
(
    [EmpCode] int,
    [TimeIn] datetime,
    [TimeOut] datetime
)

INSERT INTO Attendance VALUES (12, '2018-08-01 09:00:00', '2018-08-01 17:36:00');
INSERT INTO Attendance VALUES (12, '2018-08-02 09:00:00', '2018-08-02 18:10:00');
INSERT INTO Attendance VALUES (12, '2018-08-03 09:25:00', '2018-08-03 16:56:00');
INSERT INTO Attendance VALUES (12, '2018-08-04 09:13:00', '2018-08-05 18:09:00');
INSERT INTO Attendance VALUES (12, '2018-08-06 09:00:00', '2018-08-07 18:15:00');
INSERT INTO Attendance VALUES (12, '2018-08-07 09:27:00', '2018-08-08 17:36:00');
INSERT INTO Attendance VALUES (12, '2018-08-08 09:35:00', '2018-08-09 17:21:00');
INSERT INTO Attendance VALUES (12, '2018-08-10 09:00:00', '2018-08-10 17:45:00');
INSERT INTO Attendance VALUES (12, '2018-08-11 09:50:00', '2018-08-11 17:31:00');
INSERT INTO Attendance VALUES (12, '2018-08-13 09:23:00', '2018-08-13 17:19:00');
INSERT INTO Attendance VALUES (12, '2018-08-15 09:21:00', '2018-08-15 17:36:00');
INSERT INTO Attendance VALUES (12, '2018-08-16 09:00:00', '2018-08-16 17:09:00');
INSERT INTO Attendance VALUES (12, '2018-08-17 09:34:00', '2018-08-17 17:29:00');
INSERT INTO Attendance VALUES (12, '2018-08-18 09:00:00', '2018-08-18 17:10:00');
INSERT INTO Attendance VALUES (12, '2018-08-20 09:34:00', '2018-08-20 17:12:00');
INSERT INTO Attendance VALUES (12, '2018-08-21 09:20:00', '2018-08-21 17:15:00');
INSERT INTO Attendance VALUES (12, '2018-08-22 09:12:00', '2018-08-22 17:19:00');
INSERT INTO Attendance VALUES (12, '2018-08-23 09:05:00', '2018-08-23 17:21:00');
INSERT INTO Attendance VALUES (12, '2018-08-24 09:07:00', '2018-08-24 17:09:00');
INSERT INTO Attendance VALUES (12, '2018-08-25 09:12:00', '2018-08-25 17:05:00');
INSERT INTO Attendance VALUES (12, '2018-08-27 09:21:00', '2018-08-27 17:46:00');
INSERT INTO Attendance VALUES (12, '2018-08-28 09:17:00', '2018-08-28 17:12:00');
INSERT INTO Attendance VALUES (12, '2018-08-29 09:00:00', '2018-08-29 17:36:00');
INSERT INTO Attendance VALUES (12, '2018-08-30 09:12:00', '2018-08-30 17:24:00');

I have a query that tells how many hours employee have worked, but it is only showing days on which data was present in table. I want to show all dates between provided dates and in case there is no data it should NULL in columns.

Here is the query:

SELECT
    [EmpCode],
    FirstIN    = CAST(MIN([TimeIn]) AS TIME), 
    LastOUT    = CAST(MAX([TimeOut]) AS TIME), 
    CONVERT(VARCHAR(6), Datediff(second, CAST(MIN([TimeIn]) AS TIME), CAST(MAX([TimeOut]) AS TIME))/3600) 
       + ':'
       + RIGHT('0' + CONVERT(VARCHAR(2), (Datediff(second, CAST(MIN([TimeIn]) AS TIME), CAST(MAX([TimeOut]) AS TIME)) % 3600) / 60), 2) 
       + ':' 
       + RIGHT('0' + CONVERT(VARCHAR(2), Datediff(second, CAST(MIN([TimeIn]) AS TIME), CAST(MAX([TimeOut]) AS TIME)) % 60) , 2 ) AS HoursSpent,
    CAST(COALESCE(TimeIn, TimeOut) AS DATE) [Date]    
FROM Attendance
WHERE CAST(COALESCE(TimeIn, TimeOut) AS DATE) BETWEEN '2018-08-01' AND '2018-08-25'    
GROUP BY EmpCode, TimeIn, TimeOut

Answer 1

For that you need to use recursive way to generate possible dates :

with t as (
     select '2018-08-01' as startdt
     union all
     select dateadd(day, 1, startdt)
     from t
     where startdt < '2018-08-25'
)

select . . . 
from t left join
     Attendance at
     on cast(coalesce(at.TimeIn, at.TimeOut) as date) = t.startdt; 

Just make sure to use date from t instead of Attendance table in SELECT statement.

Note : If you have a large no of date period, then don't forgot to use Query hint OPTION (MAXRECURSION 0), By defalut it has 100 recursion levels.

Answer 2

You May Try Recursive CTE to populate the Dates and Then Join With that to Get the Interval

DECLARE @From DATETIME = '2018-08-01' ,@To DATETIME= '2018-08-25'

;WITH CTE
AS
(
    SELECT
        [EmpCode] EmpId,
        MyDate = @From
        FROM Attendance A
    UNION ALL
    SELECT
        EmpId,
        MyDate = DATEADD(DAY,1,MyDate)
        FROM CTE
            WHERE MyDate < @To
)
SELECT
[EmpCode] = CTE.EmpId,
CTE.MyDate,
FirstIN    = CAST(MIN([TimeIn]) AS TIME), 
LastOUT    = CAST(MAX([TimeOut]) AS TIME), 
CONVERT(VARCHAR(6), Datediff(second, CAST(MIN([TimeIn]) AS TIME), CAST(MAX([TimeOut]) AS TIME))/3600) 
       + ':'
       + RIGHT('0' + CONVERT(VARCHAR(2), (Datediff(second, CAST(MIN([TimeIn]) AS TIME), CAST(MAX([TimeOut]) AS TIME)) % 3600) / 60), 2) 
       + ':' 
       + RIGHT('0' + CONVERT(VARCHAR(2), Datediff(second, CAST(MIN([TimeIn]) AS TIME), CAST(MAX([TimeOut]) AS TIME)) % 60) , 2 ) 

       AS HoursSpent,
CAST(CTE.MyDate AS DATE) [Date]

FROM CTE
    LEFT JOIN Attendance A
        ON A.EmpCode = CTE.EmpId
            AND CAST(CTE.MyDate AS DATE) = CAST(COALESCE(TimeIn, TimeOut) AS DATE)
GROUP BY CTE.EmpId, TimeIn, TimeOut,CTE.MyDate
    ORDER BY 6

Answer 3

A different method, using a Tally Table. The advantage here is that an rCTE is a form of RBAR. The idea of a Tally table isn't as obvious, but is quicker, and also, won't need the OPTION (MAXRECURSION 0) added if you have more than 100 days. in fact, this example handles up to 10,000 days, which shuold be more than enough:

DECLARE @EmpCode int = 12;

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) N(N)),
Tally AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I
    FROM N N1 --10
         CROSS JOIN N N2 --100
         CROSS JOIN N N3 --1000
         CROSS JOIN N N4 --10000
),
Dates AS(
    SELECT DATEADD(DAY, T.I, TT.MinTimeIn) AS CalendarDate,
           @EmpCode AS EmpCode
    FROM Tally T
         CROSS APPLY (SELECT MIN(CONVERT(date,TimeIn)) AS MinTimeIn,
                             MAX(CONVERT(date,TimeOut)) AS MaxTimeOut
                      FROM Attendance
                      WHERE EmpCode = @EmpCode) TT
     WHERE DATEADD(DAY, T.I, TT.MinTimeIn) <= CONVERT(date, TT.MaxTimeOut))
SELECT CalendarDate
       EmpCode,
       TimeIn,
       TimeOut
FROM Dates D
     LEFT JOIN Attendance A ON D.CalendarDate = CONVERT(date,A.TimeIn)
                           AND D.EmpCode = A.EmpCode;