Jump to content

adding minutes


Destramic
Go to solution Solved by Barand,

Recommended Posts

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.

 

sql1.png

 

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Solution

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

  • Great Answer 1
Link to comment
Share on other sites

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 😁

Link to comment
Share on other sites

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 |
+----+-----------+------------+------+------+

 

Link to comment
Share on other sites

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 |
+----+-----------+------------+------+------+

 

Link to comment
Share on other sites

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:

sql2.png

 

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

Link to comment
Share on other sites

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;

 

Link to comment
Share on other sites

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

sql2.png

 

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

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.