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!
Advertisement
Answer
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.