Combine/Compress data into another table

I’m trying to make an event that runs an an interval I just don’t know how to compress everyone’s stuff down into one line across each day.

Currently I have a maintenance_tasks table where it stores the time spent on certain maintenance tasks logged by the employee. An employee may have multiple maintenance task in a day.

I am trying to create another table containing each employees name for each day (at 6 AM) together with total time spent on tasks per day .

Below is an example of what I’m trying to do:

Combine/Compress data into another table

my current event query looks like this:

CREATE EVENT IF NOT EXISTS fetch_24hr_data
    ON SCHEDULE
        EVERY 1 DAY
        STARTS( TIMESTAMP(CURRENT DATE) + INTERVAL 1 DAY + INTERVAL 6 HOUR)
    DO

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

If you want that result this should do it for you. You will need to do a little research on GROUP BY to understand whats going on here

SELECT DATE(TIMESTAMP) as DATE, name, SUM(`TIME SPENT`) as `TOTAL HOURS LOGGED`
FROM `SINGULAR TASK TABLE`
GROUP BY DATE(TIMESTAMP), name
ORDER BY name;


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x