Updated June 2026. Tested on Laravel 13, PHP 8.4 and MySQL 8.

I built this for an employee time attendance system. The requirement was a monthly sheet with each employee down the side and every day of the month across the top, each cell showing whether they were present, absent, or on leave. The hard part is that the data is stored as individual clock in and clock out rows, but the report needs the days as columns. Turning rows into columns like this is called pivoting, and MySQL has no built in pivot, so we build one.

Why a calendar table

The sheet must show every day of the month, including days an employee has no record, which are the absences. If we group straight off the attendance dates, those empty days simply vanish from the grid. So we keep a small calendar table with one row per date, and build the day columns from it. That guarantees a cell for every day.

CREATE TABLE date_calendar (
    calendar_date date NOT NULL
);
-- seed it with every date in the range you report on

The source tables

The data lives in an employees table and an employee_attendance table, where direction records a clock in or a clock out for a given date.

-- employees: id, emp_code, first_name, last_name
-- employee_attendance: id, emp_code, direction (0 = in, 1 = out), attendance_date, attendance_time

The pivot, in three moves

First, collapse the clock in and clock out rows into one status per employee per day. A CASE decides the status: present, in but not out, out but not in, and so on.

Second, because the day columns are dynamic, generate the list of CASE expressions from the calendar table, one per date, with GROUP_CONCAT.

Third, cross join employees with the calendar so every employee has a cell for every day, left join the statuses in, and run the assembled statement. Keeping it in a stored procedure means the app calls it with two dates.

DELIMITER $$
CREATE PROCEDURE attendance_sheet(IN start_date DATE, IN end_date DATE)
BEGIN
    -- one status row per employee per day
    DROP TEMPORARY TABLE IF EXISTS tmp_status;
    CREATE TEMPORARY TABLE tmp_status AS
        SELECT a.emp_code, a.attendance_date,
               CASE
                 WHEN MAX(a.direction = 0) AND MAX(a.direction = 1) THEN 'present'
                 WHEN MAX(a.direction = 0) THEN 'in-only'
                 WHEN MAX(a.direction = 1) THEN 'out-only'
                 ELSE 'absent'
               END AS status
        FROM employee_attendance a
        WHERE a.attendance_date BETWEEN start_date AND end_date
        GROUP BY a.emp_code, a.attendance_date;

    -- build a column per date
    SET @@group_concat_max_len = 75000;
    SET @cols = NULL;
    SELECT GROUP_CONCAT(DISTINCT
        CONCAT('MAX(CASE WHEN grid.calendar_date = ''', calendar_date,
               ''' THEN COALESCE(s.status, ''absent'') END) AS `', calendar_date, '`')
    ) INTO @cols
    FROM date_calendar
    WHERE calendar_date BETWEEN start_date AND end_date;

    -- assemble and run
    SET @sql = CONCAT(
        'SELECT grid.emp_code, grid.first_name, ', @cols, '
         FROM (
            SELECT e.emp_code, e.first_name, c.calendar_date
            FROM date_calendar c CROSS JOIN employees e
            WHERE c.calendar_date BETWEEN ''', start_date, ''' AND ''', end_date, '''
         ) grid
         LEFT JOIN tmp_status s
            ON grid.emp_code = s.emp_code AND grid.calendar_date = s.attendance_date
         GROUP BY grid.emp_code
         ORDER BY grid.emp_code'
    );

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    DROP TEMPORARY TABLE tmp_status;
END $$
DELIMITER ;

COALESCE(s.status, 'absent') is what fills the empty days, which is the whole reason for the calendar table.

Call it from Laravel

Use DB::select with bound parameters. There is no need for raw PDO handling.

use Illuminate\Support\Facades\DB;

$rows = DB::select('CALL attendance_sheet(?, ?)', [
    $start->toDateString(),
    $end->toDateString(),
]);

Each row is one employee with a property per date, which you loop over to render the cells, colouring each by its status. One note on performance: if you have many employees this query is heavy, so cache the result and refresh it on a schedule rather than running it on every page view.

The lesson, same as any pivot report, is that the work lives in the SQL. Once the calendar table and the dynamic CASE give you the grid, the Laravel side is just calling a procedure and looping the result. Questions welcome in the comments.