Query to find monthly hours for each employee based on location?

Question

This is the table from which i have to find working hours of each employee on monthly basis with their location too and mainly to fetch the details based on their start and end date as input in transdate.

startdate   enddate       Empno transdate   Location hours
03/07/2017  06/09/2018  11  02/04/2018  BAN 8
03/07/2017  06/09/2018  11  03/04/2018  BAN 8
03/07/2017  06/09/2018  11  04/04/2018  BAN 8
03/07/2017  06/09/2018  11  05/04/2018  BAN 8
03/07/2017  06/09/2018  11  06/04/2018  BAN 8
03/07/2017  06/09/2018  11  09/04/2018  BAN 8
03/07/2017  06/09/2018  11  10/04/2018  BAN 8
03/07/2017  06/09/2018  11  11/04/2018  BAN 8
03/07/2017  06/09/2018  11  12/04/2018  BAN 8

I wrote a correlated subquery but which fetches the total working hours based on their start and end date.

Expected results:

Location Empno  Month   Year    Hours
BAN  11 Apr 2018    176
BAN  11 May 2018    176
BAN  11 Jun 2018    168
BAN  11 Jul 2018    176
BAN  11 Aug 2018    176
BAN  11 Sep 2018    176
BAN  11 Oct 2018    24

You could see the last value in Hours is only 24 because the employee left the firm in that month working only for three days.

score 0 · Accepted Answer · answered Sep 04 '19 at 06:37

We can handle this via using a calendar table to cover all dates which may have been worked, combined with a rollup at the end by year, month, location, and employee:

WITH dates AS (
    SELECT '2018-01-01' AS dt UNION ALL
    SELECT '2018-01-02' UNION ALL
    SELECT '2018-01-03' UNION ALL
    ...
    SELECT '2018-12-31'
)

SELECT
    t.Location,
    t.Empno,
    CONVERT(varchar(7), d.dt, 120),
    COUNT(t.startdate) AS Hours
FROM dates d
LEFT JOIN yourTable t
    ON d.dt BETWEEN t.startdate AND t.enddate
GROUP BY
    t.Location,
    t.Empno,
    CONVERT(varchar(7), d.dt, 120);

Note that instead of using a CTE to hold the dates of the calendar table, as I have done for convenience only, you might want to generate a bona-fide table to hold the dates. Read How to create a Calendar table for 100 years in Sql for more information.

Thanks.. It actually helped me to get the results. I modifed it a bit by adding location and working hours in CTE. But i did not create a calendar bcoz i had all the dates in my table. — ssvbalan, Sep 04 '19 at 08:40

Query to find monthly hours for each employee based on location?

1 Answers1