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

Populate Calendar table and utilize it.

IVNSTN
– IVNSTN

2018-08-30 11:21:30 +00:00
Commented Aug 30, 2018 at 11:21 — IVNSTN
– IVNSTN, Commented Aug 30, 2018 at 11:21
The Calendar Table

Thom A
– Thom A ♦

2018-08-30 11:22:47 +00:00
Commented Aug 30, 2018 at 11:22 — Thom A
– Thom A ♦, Commented Aug 30, 2018 at 11:22

Yogesh Sharma · Accepted Answer · 2018-08-30 11:28:49Z

3

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.

edited Aug 30, 2018 at 11:28

answered Aug 30, 2018 at 11:23

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thom A Over a year ago

I'd personally go with a Tally if you can. If the OP is doing a much larger date range, then it'll have better performance than a rCTE. it also means there's no need for the the inclusion of OPTION (MAXRECURSION 0)

Yogesh Sharma Over a year ago

@Larnu. . . I totally agreed with your hint, but sometime OP doesn't have create/alter rights to do these things. So, this would help as an alternative.

Thom A Over a year ago

You can do a tally inside the query as well. Having a VIEW is nice, but not a requirement.

Jayasurya Satheesh · Accepted Answer · 2018-08-30 11:41:24Z

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

Thom A · Accepted Answer · 2018-08-30 11:48:56Z

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;

Collectives™ on Stack Overflow

Get all dates between provided dates

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related