How to get a report for an Employee for each day of the date range, also for those he has not inserted any data?

Question

Currently, I'm building an app (API) for employee management with Laravel! I will try and explain the process step by step so you can understand what I want much easier!

When you create/register an Employee you have to assign how many hours will work during the week.

Example:

Id: 100
Name: Joe Doe
Working hours: 40 hours per week (Monday - Tuesday 9 hours per day, and Friday 4 hours)

After the employee is created he can start writing the hours he has worked daily

The database table is called "hours"

Example:

user_id	date	start	end
100	17.10.22	09:00	18:00
100	18.10.22	09:00	18:00
100	20.10.22	09:00	18:00
100	21.10.22	09:00	14:00
100	24.10.22	09:00	20:00

I have this function function raportEmployee($employee_id, $date_start, $date_end) and I call this with these parameters

$employee_id = 100;
$date_start = 17.10.22
$date_end = 24.10.22

This has to return each day of the week even those where the employee has not put any data and show if he is working less or more each day

The result I want (this will return JSON):

Employee	date	start	end	total hours
Joe Doe	17.10.22	09:00	18:00	0
Joe Doe	18.10.22	09:00	18:00	0
Joe Doe	19.10.22	null	null	-9
Joe Doe	20.10.22	09:00	18:00	-9
Joe Doe	21.10.22	09:00	14:00	-9
Joe Doe	22.10.22	null	null	-9
Joe Doe	23.10.22	null	null	-9
Joe Doe	24.10.22	09:00	21:00	-6

So 19.10 the employee is -9 hours because has not written any hours on this date. Then Saturday (22.10) and Sunday(23.10) even though he has not written any hours, the total hours are still -9 because based on "Working hours" is "obligated" to work only during weekdays. Then on Monday (24.10) has worked from 09:00 - 21:00 (12hr; 3hr more than planned) the total hours now is -6.

How can I achieve this I have some ideas but are not very well in terms of speed and optimization because imagine where we want a range from 01-01-2022 to 31-12-2022, each day will be displayed and has to constantly calculate "total hours" (if is in positive, negative or as planned)

Sorry for this long post but I thought this was the best way to explain the problem. If you have any questions please let me know.

Thank you!

Answer 1

The most important thing is to fetch all the data in one query. The trick is that your table does not contain the zero days. So you need something to loop through the missing days.

The fastest way to do this is in the application. You'd run:

SELECT
    hours.user_id, 
    hours.[date],
    SUM(DATEDIFF(HH,hours.start,hours.end)) [WorkedHours]
FROM        hours
WHERE       hours.user_id IN (@UserIds)
AND         hours.[date] BETWEEN @Start and @End
GROUP BY    hours.user_id, 
            hours.[date]

Then you could dump this into a Dictionary<string,Dictionary<date,double>> in your application. This lets you check the worked hours for an employee/date combination without havgin to go back to SQL. From there you just loop through every date in your range and add a line for every employee with 9 minus whatever's in the dictionary.

You can also do this in the SQL itself using a tally table (aka numbers table). There are many ways to do that. Supposing you have a tally table that starts at 0 and counts up by 1 for each day between @Start and @End, your query would look like this:

SELECT
    employees.user_id, 
    DateTable.[date],
    SUM(DATEDIFF(HH,hours.start,hours.end)) [WorkedHours]
FROM        (   SELECT  DATEADD(DAY,TallyTable.Tally,@Start) [date]
                FROM    TallyTable
            ) DateTable
LEFT JOIN   employees
ON          employees.user_id IN (@UserIds)
LEFT JOIN   hours
ON          hours.[date] = DateTable.[date]
AND         hours.[date] BETWEEN @Start and @End
GROUP BY    employees.user_id, 
            DateTable.[date]

Notice I'm converting the tallys into dates and I'm also making sure I grab employees regardless of whether there exists any hours at all for that employee.

This will be fast in the database itself, but I mention that this is slower than adding the dead days in your application becuase this will send more data back from SQL than if it just sends the employee/day rows that do exist.

The performance difference may be negligible though so if you're using a framework that prefers to get all of its rows directly from the database as opposed to letting your application create arbitrary rows as it loops through the dates the tally table is probably the way to go.

Notice also this lets you run for multiple employees. Your question asked how to run the report for a single employee, but If you do need to get it for more than one, I would do as I've shown here instead of executing a separate database request for each employee. If you definitely don't ever have to do that, then you can simplify these queries a bit.