Destramic

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

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

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

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

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

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

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 😁

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

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

after my last post, i tried text type and got the same issue....varchar it is thank you for your time barand

one lastthing, i want to make user_agent_checksum a unique index, but cant as its a blob, is there away around this...or will i have to make it a text type.

now that is smart way of doing it :), i wouldt even need to create a cookie garbage collector thank you for helping me, again, barand

i suppose these fields, would overall determine if a cookie exists: SELECT DISTINCT(name) , value FROM cookies WHERE user_agent_checksum = '...' AND domain = 'http://mysite.uk' AND path = '/' AND expiry > NOW() ORDER BY created DESC

thank you for your reply barand, but.... cookie id is the only key in the table which isnt present during the insert causing there never to be a duplicate key and just inserts and not update. INSERT INTO cookies (name, value, path, domain, expiry, user_agent, user_agent_checksum, ip_address, ip_address_checksum) VALUES ('test', 1, '/', 'test', DATE_ADD(NOW(), INTERVAL 22 SECOND), 1, 1 , 1, 1) ON DUPLICATE KEY UPDATE value = 1 , expiry = DATE_ADD(NOW(), INTERVAL 22 SECOND) i could just insert a new cookie/row regardless if it cookie exists...and the cookies like so? SELECT DISTINCT(name) , value FROM cookies WHERE expiry > NOW() ORDER BY created DESC unless you have a better way

hey guys im trying to create a cookie storage database...i want to update a cookie/row, but if the cookie doesnt exist i want to insert it a row, like so: UPDATE cookies SET value = 1 , expiry = DATE_ADD(NOW(), INTERVAL 22 SECOND) WHERE user_agent_checksum = 'fffff' AND name = 'test' AND domain = 'test' AND path = '/' AND expiry > NOW() AND NOT EXISTS( INSERT INTO cookies (name, value, path, domain, expiry, user_agent, user_agent_checksum, ip_addess, ip_address_checksum) VALUES ('test', 1, '/', 'test', DATE_ADD(NOW(), INTERVAL 22 SECOND), 1, 1 , 1, 1) ) but i get a error: Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO cookies (name, value, path, domain, expiry, user_agent, user_agent_c' at line 8 so my question is how do i update a cookie/row, but if it doesnt exist then to create a new one please? here is the table structure: CREATE TABLE `cookies` ( `cookie_id` int(11) NOT NULL AUTO_INCREMENT, `name` blob NOT NULL, `value` blob NOT NULL, `path` varchar(45) NOT NULL, `domain` varchar(45) NOT NULL, `ip_address` blob NOT NULL `ip_address_checksum` blob NOT NULL, `user_agent` blob NOT NULL, `user_agent_checksum` blob NOT NULL, `expiry` timestamp NOT NULL, `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`cookie_id`) ) thank you