1

I am having an issue with my time clock query. Basically users clock in/out several times per day (can clock in/out unlimited times per day), and it is counting the time between the first clock in and last clock out and I need it to count time between each in out and add up to provide result.

SELECT 
    user,
    '2020-01-01' as date,
    'Total Hours for Week:' as WeekDay,
    SUM(Minutes) as Minutes
    FROM (SELECT 
            tc.user,
            DATE(tc.timeinout) as date,
            CASE 
                WHEN WEEKDAY(tc.timeinout)=0 THEN 'Monday' 
                WHEN WEEKDAY(tc.timeinout)=1 THEN 'Tuesday'
                WHEN WEEKDAY(tc.timeinout)=2 THEN 'Wednesday'
                WHEN WEEKDAY(tc.timeinout)=3 THEN 'Thursday'
                WHEN WEEKDAY(tc.timeinout)=4 THEN 'Friday'
                WHEN WEEKDAY(tc.timeinout)=5 THEN 'Saturday'
                WHEN WEEKDAY(tc.timeinout)=6 THEN 'Sunday' END AS WeekDay,
            TIMESTAMPDIFF(MINUTE,tcmin.min,tcmax.max) AS Minutes
        FROM timeclock AS tc
        LEFT JOIN (SELECT 
                    user,
                    MIN(timeinout) as min
                  FROM timeclock
                  GROUP BY user,DATE(timeinout)) as tcmin
            ON (tcmin.user=tc.user AND DATE(tcmin.min)=DATE(tc.timeinout))
        LEFT JOIN (SELECT 
                    user,
                    MAX(timeinout) as max
                  FROM timeclock
                  GROUP BY user,DATE(timeinout)) as tcmax
            ON (tcmax.user=tc.user AND DATE(tcmax.max)=DATE(tc.timeinout))
        WHERE 
           tc.timeinout>= :startdate
            AND tc.timeinout< :enddate
            AND tc.user= :user
        GROUP BY tc.user,DATE(tc.timeinout)
        ORDER BY date ASC) AS Total
    UNION ALL
    SELECT 
    tc.user,
    DATE(tc.timeinout) as date,
    CASE 
        WHEN WEEKDAY(tc.timeinout)=0 THEN 'Monday' 
        WHEN WEEKDAY(tc.timeinout)=1 THEN 'Tuesday'
        WHEN WEEKDAY(tc.timeinout)=2 THEN 'Wednesday'
        WHEN WEEKDAY(tc.timeinout)=3 THEN 'Thursday'
        WHEN WEEKDAY(tc.timeinout)=4 THEN 'Friday'
        WHEN WEEKDAY(tc.timeinout)=5 THEN 'Saturday'
        WHEN WEEKDAY(tc.timeinout)=6 THEN 'Sunday' END AS WeekDay,
    TIMESTAMPDIFF(MINUTE,tcmin.min,tcmax.max) AS Minutes
    FROM timeclock AS tc
    LEFT JOIN (SELECT 
            user,
            MIN(timeinout) as min
          FROM timeclock
          GROUP BY user,DATE(timeinout)) as tcmin
    ON (tcmin.user=tc.user AND DATE(tcmin.min)=DATE(tc.timeinout))
    LEFT JOIN (SELECT 
            user,
            MAX(timeinout) as max
          FROM timeclock
          GROUP BY user,DATE(timeinout)) as tcmax
    ON (tcmax.user=tc.user AND DATE(tcmax.max)=DATE(tc.timeinout))
    WHERE 
    tc.timeinout>= :startdate
    AND tc.timeinout< :enddate
    AND tc.user= :user
    GROUP BY tc.user,DATE(tc.timeinout)
    ORDER BY date ASC

user   min                   max                   WeekDay     Minutes
----   -------------------   -------------------   ---------   -------
1      2015-07-27 20:20:32   2015-07-27 20:29:25   Monday      8 
1      2015-07-28 20:32:04   2015-07-28 20:36:28   Tuesday     4 
1      2015-07-29 20:40:00   2015-07-29 20:45:39   Wednesday   5 
1      2015-07-30 20:20:32   2015-07-30 20:20:56   Thursday    0 
1      2015-07-31 20:29:25   2015-07-31 20:32:04   Friday      2 
1      2015-08-01 20:36:28   2015-08-01 20:40:00   Saturday    3 
1      2015-08-02 20:20:32   2015-08-02 20:40:00   Sunday      19
0

What's the question?

If you need to add up the last column, there two ways:

SELECT ... SUM(minutes) ...
    FROM (
          your query
         ) x
    GROUP BY ...

or...

GROUP BY ... WITH ROLLUP

Add WITH ROLLUP to your query and see if that is enough.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.