Level 75
You could GROUP BY date periods. https://www.mysqltutorial.org/mysql-group-by.aspx
Mar 30, 2022
23
Level 5
Break attendance report into pay periods.
I can't seem to figure out the best way to do this. Each employee punches in and out daily. The punch record shows employee_num, date, time in, time out, etc.
Is there an easy way to break the data into pay periods? We start ours the first week of the year and go 14 days.
Currently, I have to get each pay period separately:
$weekOne = $this->getWeekOne($employee, $weekOneStartDate, $weekOneEndDate);
$weekOneTotal = $weekOne->sum('total_time_converted');
$weekOnePto = $weekOne->sum('pto');
$weekOneOt = $weekOne->sum('overtime');
$weekOneHol = $weekOne->sum('holiday');
$weekOneGrandTotal = $weekOneTotal + $weekOnePto + $weekOneOt + $weekOneHol;
$weekTwo = $this->getWeekTwo($employee, $weekTwoStartDate, $weekTwoEndDate);
$weekTwoTotal = $weekTwo->sum('total_time_converted');
$weekTwoPto = $weekTwo->sum('pto');
$weekTwoOt = $weekTwo->sum('overtime');
$weekTwoHol = $weekTwo->sum('holiday');
$weekTwoGrandTotal = $weekTwoTotal + $weekTwoPto + $weekTwoOt + $weekTwoHol;
Level 5
@jlrdw Currently I don't have which pay period the punch belongs to so I don't think that would work for this.
Level 51
Try to group by week number.
mysql> SELECT WEEK('2009-05-18');
+--------------------+
| WEEK('2009-05-18') |
+--------------------+
| 20 |
+--------------------+
1 row in set (0.02 sec)
https://www.w3resource.com/mysql/date-and-time-functions/mysql-week-function.php
Level 5
@MohamedTammam Is there a way to get all the weeks grouped separately instead of just one?
Level 75
@ajsmith_codes have you considered looking for a payroll package that would work with laravel?
Level 5
@jlrdw I haven't only because I'm already pretty far into the coding. Also, I hear it's not a great idea to use too many packages and I'm already using quite a few.
Level 51
@ajsmith_codes I believe if you group by WEEK function, you will get the records for each week at different row.
Level 5
@MohamedTammam I'm trying to apply that to my query in Laravel, but not sure how.
$employeePunches = DB::table('employee_punches')
->where('employee_num', $employee->employee_num)
->get();
Level 51
$employeePunches = DB::table('employee_punches')
->where('employee_num', $employee->employee_num)
->groupBy(DB::raw('WEEK(time_in)'))
->get();
Level 5
@MohamedTammam I get this error:
"SQLSTATE[42000]: Syntax error or access violation: 1055 'laravel.employee_punches.id' isn't in GROUP BY (SQL: select * from `employee_punches` where `employee_num` = 3 group by WEEK(date))"
The punches are by date with a time in and time out, so I changed the group to date.
Level 51
@ajsmith_codes is date a column in your table?
Level 5
@MohamedTammam Yes
Level 51
@ajsmith_codes Please try.
$employeePunches = DB::table('employee_punches')
->select('*', DB::raw('WEEK(date) as week')
->where('employee_num', $employee->employee_num)
->groupBy('week')
->get();
Level 5
@MohamedTammam I am trying that but it's not liking the code. The error for "groupBy" is:
Method "groupBy" not found in \Illuminate\Database\Query\Expression
Level 5
Let's change this a bit. How would we group it or display by week in the view?
Level 75
isn't in GROUP BY (SQL: select * from
employee_puncheswhereemployee_num= 3 group by WEEK(date))"
https://laracasts.com/discuss/channels/guides/turn-off-only-full-group-by-for-just-one-query-guide
Or see:
https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html
And at least look over the earlier link I gave, it is good training on GROUP BY.
Level 5
Finally figured it out. After looking at Stack Overflow, it appears I needed to do this:
return DB::table('employee_punches')
->where('employee_num', $employee->employee_num)
->select(DB::raw('SUM(pto) AS pto'),DB::raw('SUM(overtime) AS overtime'),DB::raw('SUM(total_time_converted) AS total_time_converted'), DB::raw('MONTH(date)'))
->groupBy(DB::raw('MONTH(date)'))
->get();
It's a little different than what was posted before, but everyone was really close.
Level 75
@ajsmith_codes so a GROUP BY worked, good.
Level 5
@jlrdw Yes, but I didn't realize I had to repeat the code in the select and the groupBy.
Now, how do I display it in the view? This doesn't work:
@foreach($punches as $punch)
<td>
{{ $punch }}
</td>
@endforeach
I also tried:
@foreach($punches as $punch)
<td>
{{ $punch }}
</td>
@endforeach
EDIT: These are my results if I return them without using the foreach:
[
{
"pto": "0.00",
"overtime": "16.00",
"total_time_converted": "40.00",
"holiday": "0.00",
"WEEK(date) DIV 2": 0
},
{
"pto": "0.00",
"overtime": "31.00",
"total_time_converted": "80.00",
"holiday": "0.00",
"WEEK(date) DIV 2": 1
},
{
"pto": "1.00",
"overtime": "31.00",
"total_time_converted": "80.00",
"holiday": "0.00",
"WEEK(date) DIV 2": 2
},
]
Level 75
@foreach($punches as $punch)
<td>
{{ $punch->pto }}
</td>
<td>
{{ $punch->overtime }}
</td>
etc
@endforeach
Level 5
@jlrdw I thought I tried that, but I guess not. Thanks!
Level 75
@ajsmith_codes if mine or @MohamedTammam help solved, can you show as solved.
Level 5
@jlrdw That is a tough choice. Both of you helped. I will mark MohammedTammam's because his reply had example code that was very close.
Please or to participate in this conversation.