Leaderboard

It depends - can people have more than one occupation and/or title in your system? If so, create a couple junction tables for that data. Other than that I'd consider splitting name into first and last just to make searching easier, but the rest looks fine.

The method of counting the dates not present between the logout dates has a major flaw - it does not take into account those days when they should not be present (ie weekends and holidays) and just counts them as absent on those days. The method I usually use in these situations is to create a temporary table (workdays) which contains all the weekday dates for the required period. In the logout data I have also the data for several users during this period (Oct 1 to Oct 24 2019). CODE TO GENERATE workday DATES $startdate = '2019-10-01'; $enddate = '2019-10-25'; $interval = DateInterval::createFromDateString('next weekday'); $dateperiod = new DatePeriod( new DateTime($startdate), $interval, new DateTime($enddate)); $db->exec("CREATE TEMPORARY TABLE workday (day DATE not null primary key)"); foreach ($dateperiod as $d) { $data[] = "('{$d->format('Y-m-d')}')"; } $db->exec("INSERT INTO workday VALUES " . join(',', $data)); I also have a "holiday" table. In this example, Oct 14th is designated a holiday. So the data I now have is... TABLE: ajoo_log TABLE: ajoo_holiday +-------+---------------------+--------+ +------------+------------+-------------+ | recno | timeoflogout | userid | | holiday_id | hol_date | hol_name | +-------+---------------------+--------+ +------------+------------+-------------+ | 3 | 2019-09-30 13:13:15 | 3 | | 1 | 2019-10-14 | October Day | | 4 | 2019-09-30 13:13:15 | 4 | +------------+------------+-------------+ | 5 | 2019-09-30 13:13:15 | 5 | | 9 | 2019-10-01 07:47:35 | 4 | TABLE: ajoo_user TEMP TABLE: workday | 10 | 2019-10-01 07:47:35 | 5 | +--------+-------+ +------------+ | 14 | 2019-10-03 13:28:17 | 4 | | userid | name | | day | | 15 | 2019-10-03 13:28:17 | 5 | +--------+-------+ +------------+ | 18 | 2019-10-04 21:41:17 | 3 | | 3 | Curly | | 2019-10-01 | Tue | 19 | 2019-10-04 21:41:17 | 4 | | 4 | Larry | | 2019-10-02 | | 20 | 2019-10-04 21:41:17 | 5 | | 5 | Mo | | 2019-10-03 | | 23 | 2019-10-05 11:18:18 | 3 | +--------+-------+ | 2019-10-04 | | 24 | 2019-10-05 11:18:18 | 4 | | 2019-10-07 | Mon | 25 | 2019-10-05 11:18:18 | 5 | | 2019-10-08 | | 28 | 2019-10-08 16:56:55 | 3 | | 2019-10-09 | | 29 | 2019-10-10 16:56:55 | 4 | | 2019-10-10 | | 30 | 2019-10-16 16:56:55 | 5 | | 2019-10-11 | | 33 | 2019-10-18 16:37:43 | 3 | | 2019-10-14 | Mon | 34 | 2019-10-18 16:37:43 | 4 | | 2019-10-15 | | 35 | 2019-10-18 16:37:43 | 5 | | 2019-10-16 | | 38 | 2019-10-19 12:31:23 | 3 | | 2019-10-17 | | 40 | 2019-10-19 12:31:23 | 5 | | 2019-10-18 | | 43 | 2019-10-21 15:23:54 | 3 | | 2019-10-21 | Mon | 44 | 2019-10-21 15:23:54 | 4 | | 2019-10-22 | | 45 | 2019-10-21 15:23:54 | 5 | | 2019-10-23 | | 48 | 2019-10-24 15:37:35 | 3 | | 2019-10-24 | | 49 | 2019-10-24 16:37:35 | 4 | +------------+ | 50 | 2019-10-24 17:37:35 | 5 | +-------+---------------------+--------+ Now to calculate the days absent. A cross join of the user table with workday table gives me a date for each user when they were expected to attend. Left joining to the log table and the holiday tables tells me when they were absent (no matching logout and no matching holiday) SELECT u.name , SUM(ISNULL(timeoflogout) AND ISNULL(hol_date)) as total_absent FROM ajoo_user u CROSS JOIN workday w LEFT JOIN ajoo_log l ON u.userid = l.userid AND w.day = DATE(l.timeoflogout) LEFT JOIN ajoo_holiday h ON w.day = h.hol_date GROUP BY u.userid Comparison of original method with this new method ORIGINAL RESULTS NEW RESULTS +----------+--------------+ +-------+--------------+ | username | total_absent | | name | total_absent | +----------+--------------+ +-------+--------------+ | Curly | 14 | | Curly | 12 | | Larry | 16 | | Larry | 10 | | Mo | 15 | | Mo | 10 | +----------+--------------+ +-------+--------------+