adding minutes

Destramic · June 20

hey guys,

I'm trying to gather data over a date range, but im failing to add all the working minutes for all the dates as seen here.

as the dates range from 2024-06-17 - 2024-06-24 im wanting the working minutes to increase on each day ie.

2024-06-17 Monday 450

2024-06-18 Tuesday 900

2024-06-19 Wednesday 1350

2024-06-20 Thursday 1800

2024-06-21 Friday 2250 (not working, no minutes to be added)

2024-06-22 Saturday 2250 (not working, no minutes to be added)

2024-06-23 Sunday 2250

2024-06-24 Monday 2700

this way i can put a clause in the query like "HAVING working_minutes = 1500" which will only bring 4 days up

SQL

SELECT @break_minutes := 30
	  , u.username
      , d.date
      , @day_name := DAYNAME(d.date)  AS `day`
	  , @start_time := CASE @day_name
        WHEN 'Monday' THEN monday_start_time
        WHEN 'Tuesday' THEN tuesday_start_time
        WHEN 'Wednesday' THEN wednesday_start_time
        WHEN 'Thursday' THEN thursday_start_time
        WHEN 'Friday' THEN friday_start_time
        WHEN 'Saturday' THEN saturday_start_time
        WHEN 'Sunday' THEN sunday_start_time
        END AS `start_time`
	  , @finish_time := CASE @day_name
        WHEN 'Monday' THEN monday_finish_time
        WHEN 'Tuesday' THEN tuesday_finish_time
        WHEN 'Wednesday' THEN wednesday_finish_time
        WHEN 'Thursday' THEN thursday_finish_time
        WHEN 'Friday' THEN friday_finish_time
        WHEN 'Saturday' THEN saturday_finish_time
        WHEN 'Sunday' THEN sunday_finish_time
        END AS `finish_time`
	 , @finish_time_minus_break := DATE_FORMAT(DATE_SUB(CONCAT(CURDATE(), ' ', @finish_time), INTERVAL @break_minutes MINUTE), '%H:%i')
	 , @working_seconds := TIME_TO_SEC(TIMEDIFF(@finish_time_minus_break, @start_time))
	 , CAST(@working_hours / (60 * 60) AS DECIMAL(10, 1)) AS `working_hours`
	 , @working_minutes := FORMAT(@working_seconds / 60, 0) AS `working_minutes`
      , FORMAT(SUM(@working_minutes), 0) AS `working_minutes`
      , CONCAT('[{"fullname" : "', u.username, '", "trades" : "', '[Plumber, Gas Engineer]', '", "assignments" : {"date" : "', d.date, '", "start_time" : "', start_time, '"', ', "finish_time" : "', finish_time, '"', ', "tasks" : [', GROUP_CONCAT('{ "start_time" : "', ut.start_time, '"', ', "finish_time" : "', ut.finish_time, '"', ', "esitmated_finish_time" : "', ut.estimated_start_time, '"', ', "estimated_finish_time" : "', ut.estimated_finish_time, '"', '}'), '], "appointments" : [], "absences" : []}}]') AS `tasks`
FROM dates d
INNER JOIN users_shifts us
LEFT JOIN users u ON u.user_id = us.user_id
LEFT JOIN users_tasks ut ON ut.user_id = u.user_id AND ut.date = d.date
GROUP BY d.date

any help on how to achive this and where im going wrong would be appreciated, thank you

requinix · June 20

Your schema is causing me physical pain.

edit: Oh, you mean you want a running total? Then wouldn't you need a condition in here that only totals the hours if the date is a working date?

Destramic · June 20

Sorry for the headache

Ok, when cycling through the dates I'm calculating the work minutes for that day, which is how many minutes the user is in work for, for that particular day

But what I'd like is for the working minutes to increase on each row by adding the previous rows working minutes for the user

And be able to stop the query where the working hours have calculated to say 1500 minutes

requinix · June 20

Now I've lost track of what you're doing. Do you want a running total of working minutes that doesn't count non-working days (even though it seems they have working minutes being counted?) or is that a second problem?

But I don't see the running total already in here. Is the SUM(@working_minutes) able to do that? If you have it, I would expect a HAVING(the effective column name) <= 1500 would work.

...that being if you want days that don't go over the total (up to your example's Wednesday). If you want days before that total and then one more day after (the Thursday) then stagger a variable/column to use as a flag: determine if the running total at that row is <=1500 but before updating it for the current row's data. The effect is that the flag flips on the row after it goes over the total. Then add a HAVING to exclude rows after that point.

Barand · June 20

Example...

DATA

mysql> select * from worked_hours;
+----+---------+---------------------+---------------------+
| id | user_id | start_time          | finish_time         |
+----+---------+---------------------+---------------------+
|  1 |       1 | 2024-06-17 09:00:00 | 2024-06-17 17:26:00 |
|  2 |       1 | 2024-06-18 09:00:00 | 2024-06-18 17:31:00 |
|  3 |       1 | 2024-06-19 09:00:00 | 2024-06-19 17:12:00 |
|  4 |       1 | 2024-06-20 09:00:00 | 2024-06-20 17:40:00 |
|  5 |       1 | 2024-06-21 09:00:00 | 2024-06-21 17:01:00 |
|  6 |       1 | 2024-06-22 09:00:00 | 2024-06-22 17:36:00 |
|  7 |       1 | 2024-06-23 09:00:00 | 2024-06-23 17:47:00 |
|  8 |       1 | 2024-06-24 09:00:00 | 2024-06-24 17:19:00 |
|  9 |       2 | 2024-06-17 09:00:00 | 2024-06-17 17:20:00 |
| 10 |       2 | 2024-06-18 09:00:00 | 2024-06-18 17:43:00 |
| 11 |       2 | 2024-06-19 09:00:00 | 2024-06-19 17:45:00 |
| 12 |       2 | 2024-06-20 09:00:00 | 2024-06-20 17:35:00 |
| 13 |       2 | 2024-06-21 09:00:00 | 2024-06-21 17:26:00 |
| 14 |       2 | 2024-06-22 09:00:00 | 2024-06-22 17:14:00 |
| 15 |       2 | 2024-06-23 09:00:00 | 2024-06-23 17:55:00 |
| 16 |       2 | 2024-06-24 09:00:00 | 2024-06-24 17:15:00 |
| 17 |       3 | 2024-06-17 09:00:00 | 2024-06-17 17:40:00 |
| 18 |       3 | 2024-06-18 09:00:00 | 2024-06-18 17:15:00 |
| 19 |       3 | 2024-06-19 09:00:00 | 2024-06-19 17:35:00 |
| 20 |       3 | 2024-06-20 09:00:00 | 2024-06-20 17:26:00 |
| 21 |       3 | 2024-06-21 09:00:00 | 2024-06-21 17:38:00 |
| 22 |       3 | 2024-06-22 09:00:00 | 2024-06-22 17:41:00 |
| 23 |       3 | 2024-06-23 09:00:00 | 2024-06-23 17:00:00 |
| 24 |       3 | 2024-06-24 09:00:00 | 2024-06-24 17:04:00 |
+----+---------+---------------------+---------------------+

QUERY

WITH hrs as (
		SELECT user_id
			 , DAYNAME(start_time) as day
			 , TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30 as mins
			 , SUM(TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30) over w1 as cum
			 , SUM(TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30) over w1 - TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30 <= 1500 as include
		FROM worked_hours
		WINDOW w1 as (PARTITION BY user_id ORDER BY start_time)
	)
SELECT user_id
     , day
     , mins
     , cum
FROM hrs    
WHERE include;

RESULTS

image.png.db1e293139a75be6a09d6e0504d13511.png

Destramic · June 21

Now my brain hurts 🤣 it's looks kinda complex, thank you barand for your help and effort, calculated this minutes is exactly what I was after

I'll be on my computer tomorrow and I'm excited to run the query

I don't suppose you recall when you introduced me adding a table to my DB that holds just dates of the year, which I'd have to include into this query

That being so that when there's days that the user has no shift it'll still put a row in for that date and continue the working minutes calculation

Thank you again, I'll message back when I've ran it tomorrow 😁

Barand · June 21

With MySQLv8 or MariaDBv11 you can easily generate the required dates on the fly using WITH RECURSIVE. eg to creates a 1 week's dates

WITH RECURSIVE dates(n, dt) AS (
        SELECT 1, CAST('2024-06-17' as DATE)
        UNION ALL
        SELECT n+1, dt + INTERVAL 1 DAY
        FROM dates WHERE n < 7
    )
    SELECT n 
         , dt
         , DAYNAME(dt) as day 
    FROM dates;

+------+------------+-----------+
| n    | dt         | day       |
+------+------------+-----------+
|    1 | 2024-06-17 | Monday    |
|    2 | 2024-06-18 | Tuesday   |
|    3 | 2024-06-19 | Wednesday |
|    4 | 2024-06-20 | Thursday  |
|    5 | 2024-06-21 | Friday    |
|    6 | 2024-06-22 | Saturday  |
|    7 | 2024-06-23 | Sunday    |
+------+------------+-----------+

I have created a user table with 3 users and also altered the test data

user #2 reches 1500 mins in 3 days
user #3 has no records for 18th/19th so now doesn't reach 1500 until saturday

Revised data

+----+---------+---------------------+---------------------+
| id | user_id | start_time          | finish_time         |
+----+---------+---------------------+---------------------+
|  1 |       1 | 2024-06-17 09:00:00 | 2024-06-17 17:26:00 |
|  2 |       1 | 2024-06-18 09:00:00 | 2024-06-18 17:31:00 |
|  3 |       1 | 2024-06-19 09:00:00 | 2024-06-19 17:12:00 |
|  4 |       1 | 2024-06-20 09:00:00 | 2024-06-20 17:40:00 |
|  5 |       1 | 2024-06-21 09:00:00 | 2024-06-21 17:01:00 |
|  6 |       1 | 2024-06-22 09:00:00 | 2024-06-22 17:36:00 |
|  7 |       1 | 2024-06-23 09:00:00 | 2024-06-23 17:47:00 |
|  8 |       1 | 2024-06-24 09:00:00 | 2024-06-24 17:19:00 |
|  9 |       2 | 2024-06-17 09:00:00 | 2024-06-17 17:20:00 |
| 10 |       2 | 2024-06-18 09:00:00 | 2024-06-18 18:43:00 |
| 11 |       2 | 2024-06-19 09:00:00 | 2024-06-19 17:45:00 |
| 12 |       2 | 2024-06-20 09:00:00 | 2024-06-20 17:35:00 |
| 13 |       2 | 2024-06-21 09:00:00 | 2024-06-21 17:26:00 |
| 14 |       2 | 2024-06-22 09:00:00 | 2024-06-22 17:14:00 |
| 15 |       2 | 2024-06-23 09:00:00 | 2024-06-23 17:55:00 |
| 16 |       2 | 2024-06-24 09:00:00 | 2024-06-24 17:15:00 |
| 17 |       3 | 2024-06-17 09:00:00 | 2024-06-17 17:40:00 |  no data for user #3 on 18th/19th
| 20 |       3 | 2024-06-20 09:00:00 | 2024-06-20 17:26:00 |
| 21 |       3 | 2024-06-21 09:00:00 | 2024-06-21 17:38:00 |
| 22 |       3 | 2024-06-22 09:00:00 | 2024-06-22 17:41:00 |
| 23 |       3 | 2024-06-23 09:00:00 | 2024-06-23 17:00:00 |
| 24 |       3 | 2024-06-24 09:00:00 | 2024-06-24 17:04:00 |
+----+---------+---------------------+---------------------+

The rewritten query...

WITH RECURSIVE dates(n, dt) AS (
		SELECT 1, '2024-06-17'
		UNION ALL
		SELECT n+1, dt+INTERVAL 1 DAY
		FROM dates WHERE n < 7
	)
SELECT u.id
     , dayname(d.dt) as day
     , dt
     , mins
     , cum
FROM dates d CROSS JOIN user u
     LEFT JOIN (
				SELECT user_id
					 , start_time
					 , TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30 as mins
					 , SUM(TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30) over w1 as cum
				FROM worked_hours 
				WINDOW w1 as (PARTITION BY user_id ORDER BY start_time)
     ) hrs ON d.dt = DATE(hrs.start_time) AND u.id = hrs.user_id
HAVING cum - mins < 1500
ORDER BY u.id, start_time;

+----+-----------+------------+------+------+
| id | day       | dt         | mins | cum  |
+----+-----------+------------+------+------+
|  1 | Monday    | 2024-06-17 |  476 |  476 |
|  1 | Tuesday   | 2024-06-18 |  481 |  957 |
|  1 | Wednesday | 2024-06-19 |  462 | 1419 |
|  1 | Thursday  | 2024-06-20 |  490 | 1909 |
|  2 | Monday    | 2024-06-17 |  470 |  470 |
|  2 | Tuesday   | 2024-06-18 |  553 | 1023 |
|  2 | Wednesday | 2024-06-19 |  495 | 1518 |
|  3 | Monday    | 2024-06-17 |  490 |  490 |
|  3 | Thursday  | 2024-06-20 |  476 |  966 |
|  3 | Friday    | 2024-06-21 |  488 | 1454 |
|  3 | Saturday  | 2024-06-22 |  491 | 1945 |
+----+-----------+------------+------+------+

Barand · June 22

PS - If you want to see the non-work days...

WITH RECURSIVE dates(n, dt) AS (
        SELECT 1, '2024-06-17'
        UNION ALL
        SELECT n+1, dt+INTERVAL 1 DAY
        FROM dates WHERE n < 7
    )
SELECT u.id
     , dayname(d.dt) as day
     , dt
     , coalesce(mins, 0) as mins
     , coalesce(cum, '-') as cum
FROM dates d CROSS JOIN user u
     LEFT JOIN (
                SELECT user_id
                     , start_time
                     , TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30 as mins
                     , SUM(TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30) over w1 as cum
                FROM worked_hours 
                WINDOW w1 as (PARTITION BY user_id ORDER BY start_time)
     ) hrs ON d.dt = DATE(hrs.start_time) AND u.id = hrs.user_id
HAVING cum - mins < 1500 OR mins = 0
ORDER BY u.id, dt;

+----+-----------+------------+------+------+
| id | day       | dt         | mins | cum  |
+----+-----------+------------+------+------+
|  1 | Monday    | 2024-06-17 |  476 | 476  |
|  1 | Tuesday   | 2024-06-18 |  481 | 957  |
|  1 | Wednesday | 2024-06-19 |  462 | 1419 |
|  1 | Thursday  | 2024-06-20 |  490 | 1909 |
|  2 | Monday    | 2024-06-17 |  470 | 470  |
|  2 | Tuesday   | 2024-06-18 |  553 | 1023 |
|  2 | Wednesday | 2024-06-19 |  495 | 1518 |
|  3 | Monday    | 2024-06-17 |  490 | 490  |
|  3 | Tuesday   | 2024-06-18 |    0 | -    |
|  3 | Wednesday | 2024-06-19 |    0 | -    |
|  3 | Thursday  | 2024-06-20 |  476 | 966  |
|  3 | Friday    | 2024-06-21 |  488 | 1454 |
|  3 | Saturday  | 2024-06-22 |  491 | 1945 |
+----+-----------+------------+------+------+

Destramic · June 22

i've never seen a window function before, i've had to do some reading to underdtand what it is you've dont exactly

i managed to have a fiddle late last night, i love the RECURSIVE dates... idea, but it wont work in this case as i don't know how many days i'll require, the days all depend on when the cum value is satisfied and depending on the employees work hour, some may not hit that value for more or less days than others (work hours related)

so unfortunaly i'll have to continue to use a table for just date 2024-06-20, 2024-06-21, 2024-06-22....

so i've been tweeking your first query my schema and this what i've got, which is 99% there

WITH hrs as (
	SELECT us.user_id
		 , d.date
		, @day_name := DAYNAME(d.date)  AS `day`
		, @start_time := CASE @day_name
			WHEN 'Monday' THEN monday_start_time
			WHEN 'Tuesday' THEN tuesday_start_time
			WHEN 'Wednesday' THEN wednesday_start_time
			WHEN 'Thursday' THEN thursday_start_time
			WHEN 'Friday' THEN friday_start_time
			WHEN 'Saturday' THEN saturday_start_time
			WHEN 'Sunday' THEN sunday_start_time
		END AS `start_time`
		, @finish_time := CASE @day_name
			WHEN 'Monday' THEN monday_finish_time
			WHEN 'Tuesday' THEN tuesday_finish_time
			WHEN 'Wednesday' THEN wednesday_finish_time
			WHEN 'Thursday' THEN thursday_finish_time
			WHEN 'Friday' THEN friday_finish_time
			WHEN 'Saturday' THEN saturday_finish_time
			WHEN 'Sunday' THEN sunday_finish_time
		END AS `finish_time`
        #, @start_time := IF (@start_time AND CURDATE() = date AND CURRENT_TIME() > @start_time, CURRENT_TIME(), IF(@start_time, @start_time, NULL))
         , TIMESTAMPDIFF(MINUTE, CONCAT(CURDATE(), ' ', @start_time), CONCAT(CURDATE(), ' ', @finish_time)) -30 AS mins
	     , SUM(TIMESTAMPDIFF(MINUTE, CONCAT(CURDATE(), ' ', @start_time), CONCAT(CURDATE(), ' ', @finish_time)) -30) OVER w1 as cum
        , SUM(TIMESTAMPDIFF(MINUTE, CONCAT(CURDATE(), ' ', @start_time), CONCAT(CURDATE(), ' ', @finish_time)) -30) OVER w1 - IF(@start_time AND @finish_time, TIMESTAMPDIFF(MINUTE, CONCAT(CURDATE(), ' ', @start_time), CONCAT(CURDATE(), ' ', @finish_time)) -30, 0) <= 2800 AS include 
	FROM dates d  
	INNER JOIN users_shifts us
    LEFT JOIN users u ON u.user_id = us.user_id
	WINDOW w1 as (PARTITION BY user_id ORDER BY date)
)
SELECT user_id
     , date
     , day
     , start_time
     , finish_time
     , mins
     , cum
FROM hrs    
WHERE include
#and date >= ''

results:

the issue i have is that the saturday and sunday which have no mins, but have a cum value of 2700, which i believe should be 2250 until monday where the cum value is actually 2700

can i please get your further input and help please barand

thank you again for your efforts, i'd never of gotten these results otherwise

Destramic · June 22

The image doesn't seem to show all the results 😕 I'll upload an image when I'm back home 😩

Barand · June 22

1 hour ago, Destramic said:

so unfortunaly i'll have to continue to use a table for just date 2024-06-20, 2024-06-21, 2024-06-22....

If you're unsure, you can cover the date range in the table with...

WITH RECURSIVE dates(n, dt) AS (
        SELECT 1, MIN(DATE(dateAdded)) FROM user_shifts
        UNION ALL
        SELECT n+1, dt + INTERVAL 1 DAY
        FROM dates WHERE dt < (SELECT MAX(dateAdded) FROM user_shifts)
    )
SELECT n, dt, DAYNAME(dt) as day FROM dates;

Destramic · June 22

now that would work brillantly, saves entering dates into a table wooooh!

heres the results from your query that ive alter to fit in to my schema, but as i said

the issue i have is that the saturday and sunday which have no mins, but have a cum value of 2700, which i believe should be 2250 until monday where the cum value is actually 2700

thanks

Barand · June 22

If you want to comment out a line of SQL, use "--" followed by a space, not "#"

EG...

-- This is a sql comment

Barand · June 22

3 hours ago, Destramic said:

TIMESTAMPDIFF(MINUTE, CONCAT(CURDATE(), ' ', @start_time), CONCAT(CURDATE(), ' ', @finish_time)) -30 AS mins

A slightly shorter alternative with TIME type columns is

 TIME_TO_SEC(TIMEDIFF(finish_time, start_time)) DIV 60 - 30 AS mins

Destramic · June 23

I've made the adjustment you suggested barand 😀

I had a good fiddle with it all again last night, and it works awesome

Thanks again for you help and efforts

Barand · June 23

Did you solve the problem of those cumulative values when there were no minutes for the day?

Was it anything to do with your use of @ variables?

Destramic · June 24

unfortunaltey i couldnt get the query to work exactly how i wanted it, i came to the conculsion that im maybe asking too much and that i would work just work with the query outside of sql

just a note, your working hours table is different to mine, mine is just the day with a start time and finish time, so theres no timestamp ie. wednesday 08:00 09:00

but there was two things i wasnt able to do with the query, which probably down to me or just as i said im asking too much.

1. i wasnt able to put get this clause to work, it would always show results if the date is less than

WHERE date >= CURDATE()

2. and ofcoure i couldnt get the result where the days off would just carry on the minutes calculation from the previous day. I can to the same consultion the @ vars are probably stroring the previous start, finish times

so this is what i've ended up with

WITH hrs as (
    WITH RECURSIVE dates(i, date) AS (
		SELECT 1, '2024-06-14'
		UNION ALL
		SELECT i + 1
			 , date + INTERVAL 1 DAY
		FROM dates WHERE date < (CURDATE() + INTERVAL 60 DAY) 
	)
	SELECT us.user_id
		 , d.date
		 , @day_name := DAYNAME(d.date)  AS `day`
		 , @start_time := CASE @day_name
			WHEN 'Monday' THEN monday_start_time
			WHEN 'Tuesday' THEN tuesday_start_time
			WHEN 'Wednesday' THEN wednesday_start_time
			WHEN 'Thursday' THEN thursday_start_time
			WHEN 'Friday' THEN friday_start_time
			WHEN 'Saturday' THEN saturday_start_time
			WHEN 'Sunday' THEN sunday_start_time
		 END AS `start_time`
		 , @finish_time := CASE @day_name
			WHEN 'Monday' THEN monday_finish_time
			WHEN 'Tuesday' THEN tuesday_finish_time
			WHEN 'Wednesday' THEN wednesday_finish_time
			WHEN 'Thursday' THEN thursday_finish_time
			WHEN 'Friday' THEN friday_finish_time
			WHEN 'Saturday' THEN saturday_finish_time
			WHEN 'Sunday' THEN sunday_finish_time
		 END AS `finish_time`
		 , @start_time := IF (@start_time AND CURDATE() = d.date AND CURRENT_TIME() > @start_time, CURRENT_TIME(), IF(@start_time, @start_time, NULL))
         , (TIME_TO_SEC(TIMEDIFF(@finish_time, @start_time)) DIV 60 - 30) AS minutes
	     , SUM((TIME_TO_SEC(TIMEDIFF(@finish_time, @start_time)) DIV 60 - 30)) OVER w1 AS calculated_minutes
	FROM dates d
    INNER JOIN users_shifts us
    LEFT JOIN users u ON u.user_id = us.user_id
    LEFT JOIN users_tasks ut on ut.user_id = u.user_id
	WINDOW w1 as (PARTITION BY user_id ORDER BY d.date)
)
SELECT user_id
     , date
     , day
     , start_time
     , finish_time
     , minutes
     , calculated_minutes
FROM hrs    
-- GROUP BY user_id
HAVING calculated_minutes - minutes < 2400

resulting:

1	2024-06-14	Friday	    08:00:00  16:00:00	450	  450
1	2024-06-17	Monday	    08:00:00  16:00:00	450	 900
1	2024-06-18	Tuesday	    08:00:00   16:00:00	450	  1350
1	2024-06-19	Wednesday   08:00:00   16:00:00	450  	1800
1	2024-06-20	Thursday    08:00:00   16:00:00	450	  2250
1	2024-06-21	Friday	    08:00:00   16:00:00	450   	2700
1	2024-06-24	Monday	    08:00:00   16:00:00	 203	3150   
1	2024-06-25	Tuesday	    08:00:00    16:00:00  450	3353
1	2024-06-26	Wednesday	08:00:00	16:00:00  450	3803
1	2024-06-27	Thursday	08:00:00	16:00:00  450	4253
1	2024-06-28	Friday	    08:00:00	16:00:00 450	4703

in a perfect world it would be nice to have the saturday and sundays in here too eg.

1	2024-06-14	Friday	    08:00:00  16:00:00	450	  450
1	2024-06-17	Monday	    08:00:00  16:00:00	450	 900
1	2024-06-18	Tuesday	    08:00:00   16:00:00	450	  1350
1	2024-06-19	Wednesday   08:00:00   16:00:00	450  	1800
1	2024-06-20	Thursday    08:00:00   16:00:00	450	  2250
1	2024-06-21	Friday	    08:00:00   16:00:00	450   	2700
                         sat                             0     2700
                         sun                             0     270
1	2024-06-24	Monday	    08:00:00   16:00:00	 203	3150   
1	2024-06-25	Tuesday	    08:00:00    16:00:00  450	3353
1	2024-06-26	Wednesday	08:00:00	16:00:00  450	3803
1	2024-06-27	Thursday	08:00:00	16:00:00  450	4253
1	2024-06-28	Friday	    08:00:00	16:00:00 450	4703

Barand · June 24

Without your actual data and structure I can't do more than give abstract examples.

Barand · June 24

Having had a closer look at your query, I am wondering why you have these lines...

    LEFT JOIN users u ON u.user_id = us.user_id
    LEFT JOIN users_tasks ut on ut.user_id = u.user_id

They are both LEFT-JOINED, so you don't need them to be there, and you never reference any columns from them (so you aren't checking if they aren't there or using anything from them). The LEFT JOINS will slow down the query though.

Barand · June 24

Does this fit the bill?

WITH minutes as (
	WITH RECURSIVE dates(n, dt) AS (
			SELECT 1, '2024-06-14'
			UNION ALL
			SELECT n+1, dt+INTERVAL 1 DAY
			FROM dates WHERE dt < CURDATE() + INTERVAL 60 DAY
		)
	SELECT user_id
		, DAYNAME(dt)
		, dt
		, COALESCE(CASE WEEKDAY(dt)
			WHEN 0 THEN TIMESTAMPDIFF(MINUTE, monday_start_time, monday_finish_time)-30
			WHEN 1 THEN TIMESTAMPDIFF(MINUTE, tuesday_start_time, tuesday_finish_time)-30
			WHEN 2 THEN TIMESTAMPDIFF(MINUTE, wednesday_start_time, wednesday_finish_time)-30
			WHEN 3 THEN TIMESTAMPDIFF(MINUTE, thursday_start_time, thursday_finish_time)-30
			WHEN 4 THEN TIMESTAMPDIFF(MINUTE, friday_start_time, friday_finish_time)-30
			WHEN 5 THEN TIMESTAMPDIFF(MINUTE, saturday_start_time, saturday_finish_time)-30
			WHEN 6 THEN TIMESTAMPDIFF(MINUTE, sunday_start_time, sunday_finish_time)-30
			ELSE 0
			END, 0) as mins
		,  SUM(COALESCE(CASE WEEKDAY(dt)
			WHEN 0 THEN TIMESTAMPDIFF(MINUTE, monday_start_time, monday_finish_time)-30
			WHEN 1 THEN TIMESTAMPDIFF(MINUTE, tuesday_start_time, tuesday_finish_time)-30
			WHEN 2 THEN TIMESTAMPDIFF(MINUTE, wednesday_start_time, wednesday_finish_time)-30
			WHEN 3 THEN TIMESTAMPDIFF(MINUTE, thursday_start_time, thursday_finish_time)-30
			WHEN 4 THEN TIMESTAMPDIFF(MINUTE, friday_start_time, friday_finish_time)-30
			WHEN 5 THEN TIMESTAMPDIFF(MINUTE, saturday_start_time, saturday_finish_time)-30
			WHEN 6 THEN TIMESTAMPDIFF(MINUTE, sunday_start_time, sunday_finish_time)-30
			ELSE 0
			END, 0)) OVER (PARTITION BY user_id ORDER BY dt) as cum   
	FROM dates CROSS JOIN users_shifts       
) 
SELECT *        
FROM minutes        
WHERE cum - mins < 2400
ORDER BY user_id, dt;

+---------+-------------+------------+------+------+
| user_id | DAYNAME(dt) | dt         | mins | cum  |
+---------+-------------+------------+------+------+
|       1 | Friday      | 2024-06-14 |  450 |  450 |
|       1 | Saturday    | 2024-06-15 |    0 |  450 |
|       1 | Sunday      | 2024-06-16 |    0 |  450 |
|       1 | Monday      | 2024-06-17 |  450 |  900 |
|       1 | Tuesday     | 2024-06-18 |  450 | 1350 |
|       1 | Wednesday   | 2024-06-19 |  450 | 1800 |
|       1 | Thursday    | 2024-06-20 |  450 | 2250 |
|       1 | Friday      | 2024-06-21 |  450 | 2700 |
|       2 | Friday      | 2024-06-14 |  450 |  450 |
|       2 | Saturday    | 2024-06-15 |  450 |  900 |
|       2 | Sunday      | 2024-06-16 |  450 | 1350 |
|       2 | Monday      | 2024-06-17 |    0 | 1350 |
|       2 | Tuesday     | 2024-06-18 |    0 | 1350 |
|       2 | Wednesday   | 2024-06-19 |  450 | 1800 |
|       2 | Thursday    | 2024-06-20 |  450 | 2250 |
|       2 | Friday      | 2024-06-21 |  450 | 2700 |
+---------+-------------+------------+------+------+

I would strongly recommend that you redesign your "users_shifts" so that, instead of a spreadsheet, you have only a single set of start/finish times per row (ie 7 rows per user per week). This will remove the need for the CASE statements (Using dates d JOIN user_shifts us ON weekday(d.dt) = us.dow will match the day of week for you).

CREATE TABLE `user_shifts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `dow` tinyint(4) DEFAULT NULL COMMENT '0 (Mon) - 6 (Sun)',
  `start_time` time DEFAULT NULL,
  `finish_time` time DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_shifts_user_id` (`user_id`, `dow`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

I'll try it out at my end and post the revised query.

Barand · June 24

This code will migrate your data from "users_shifts" to the new format "user_shifts"...

<?php
    $pdo->exec("DROP TABLE IF EXISTS user_shifts");
    $pdo->exec("CREATE TABLE `user_shifts` (
                  `id` int(11) NOT NULL AUTO_INCREMENT,
                  `user_id` int(11) DEFAULT NULL,
                  `dow` tinyint(4) DEFAULT NULL COMMENT '0 (Mon) - 6 (Sun)',
                  `start_time` time DEFAULT NULL,
                  `finish_time` time DEFAULT NULL,
                  PRIMARY KEY (`id`),
                  UNIQUE KEY `idx_user_shifts_user_id` (`user_id`,`dow`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
                ");

    $res = $pdo->query("SELECT users_shift_id
                             , user_id
                             , monday_start_time
                             , monday_finish_time
                             , tuesday_start_time
                             , tuesday_finish_time
                             , wednesday_start_time
                             , wednesday_finish_time
                             , thursday_start_time
                             , thursday_finish_time
                             , friday_start_time
                             , friday_finish_time
                             , saturday_start_time
                             , saturday_finish_time
                             , sunday_start_time
                             , sunday_finish_time
                        FROM users_shifts    
                       ");
                       
    while ($r = $res->fetch(PDO::FETCH_NUM))  {
        $data = [];
        for ($d=0; $d<7; $d++)  {
            if ($r[$d*2+2])  {
                $data[] = sprintf("(%d,%d,'%s','%s')", $r[1], $d, $r[2*$d + 2], $r[2*$d + 3] );
            }
            else  {
                $data[] = sprintf("(%d,%d,null,null)", $r[1], $d );
            }
        }
        
        $pdo->exec("INSERT INTO user_shifts (user_id, dow, start_time, finish_time) VALUES "
                     . join(',', $data)
                  );
    }

    echo "FINISHED";

    
?>

and this is the query revised to use the new format...

WITH minutes as (
	WITH RECURSIVE dates(n, dt) AS (
			SELECT 1, '2024-06-14'
			UNION ALL
			SELECT n+1, dt+INTERVAL 1 DAY
			FROM dates WHERE dt < CURDATE() + INTERVAL 60 DAY
		)
	SELECT user_id
		, DAYNAME(dt) as day
		, dt
		, COALESCE(TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30, 0) as mins
		, SUM(COALESCE(TIMESTAMPDIFF(MINUTE, start_time, finish_time)-30, 0)) 
               OVER (PARTITION BY user_id ORDER BY dt) as cum   
	FROM dates d 
         JOIN user_shifts us ON weekday(d.dt) = us.dow     
) 
SELECT *        
FROM minutes        
WHERE cum - mins < 2400
ORDER BY user_id, dt;

giving

image.png.8ca66e8c6447ab2ae462bf960e16c063.png

Destramic · July 2

Hi barand, I've tested and it works absolutely fantastic, thank you very much ☺️

I'll also take your advice and use the new structure with dow

Thanks again for you time and efforts.

Apologies I hadn't seen your reply sooner

Sign In

adding minutes

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information