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

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

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

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

June 22

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

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

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 😁

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

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

August 14, 2021

after my last post, i tried text type and got the same issue....varchar it is

thank you for your time barand

August 13, 2021

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.

August 13, 2021

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

August 13, 2021

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

August 13, 2021

13 minutes ago, Barand said:
You are doing it backwards.

You need to insert but if it does exist then update instead.
INSERT INTO tablename ( ... ) VALUES (...)
ON DUPLICATE KEY UPDATE ....
https://dev.mysql.com/doc/refman/5.7/en/insert.html

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

August 13, 2021

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

January 15, 2021

I've had numerous amounts of help from this forum for something that I have alot of passion for. Infact it was the first job I wanted to do when I grew up, but now I'm 34 haha!

I played with Geocities when I was 13 then grasped how to write raw. But now I'm confident in many languages and feel I can put things together (not as well as you gurus).

Anyhow, I want a career change and want to follow my kiddish dreams and work as a programmer. Start as a junior for a company, gain necessary qualifications if required and do what it is I love.

Can you guys please give some advise for a rookie?

Thank you 👍

June 27, 2020

baramd this is beyond help, wow, thank you indeed.

such a shame theres no exit statergy with the query, well its late now, so when im back on tomorrow i'll run you code and have a good tinker and get back to you.

once again thank you, you didnt need to go to such efforts, but its greatly appreciated 😊

June 27, 2020

Barand, can i please get some further advise, i've managed to get the end timestamp when the task is more than 2 days.

But i got 2 problems, other than cleaning up the query.

as seen in the picture, https://ibb.co/cbYN6BC

1. the date 24/06/2020 is the first date to subtract the remaining minutes and i don't know why, the first date subtracting minutes should be on the 23/06/2020 (total task time 1800 mins - 540 mins = 1260)

2. how do i end the query once i have a task end date? so it doesnt go through all the dates in the dates table?

if i can figure these two problems out then i think im on my way...i'll need to add the annual leave to the task time also, which isnt a issue.

set @days_work         = NULL;
set @minutes_remaining = NULL;

SELECT t.task_id
	  , times.day_working_hours
      , times.username
      , times.task_type
      , times.date
      , times.duration_hours
      , times.task_start_timestamp
      , times.task_end_timestamp
      , times.day_name
      , times.remaining_minutes
      , times.days_work
      , times.holiday_start_timestamp
      , times.holiday_end_timestamp
      , (times.duration_hours * 60)
      FROM tasks t
LEFT JOIN (
        SELECT date
             , t.task_id
             , u.username
             , tt.task_type
             , tt.duration_hours
             , ot.start_timestamp as task_start_timestamp
             , @day_working_hours := (timestampdiff(MINUTE, och.start_time, och.finish_time) - (timestampdiff(MINUTE, och.start_time, och.finish_time) DIV 270 ) * 30) / 60 as day_working_hours
             , DAYNAME(date) as day_name
             , oal.start_timestamp as holiday_start_timestamp
		     , oal.end_timestamp as holiday_end_timestamp 
             , @minutes_remaining := CASE WHEN @minutes_remaining IS NULL 
					THEN TRUNCATE((tt.duration_hours * 60) - (@day_working_hours * 60), 0)
					ELSE TRUNCATE(@minutes_remaining - (@day_working_hours * 60), 0)
			   END as remaining_minutes
			 , @days_work := CASE WHEN @days_work IS NULL AND (@day_working_hours * 60) < @minutes_remaining
                                  THEN 1
                                  WHEN @days_work IS NOT NULL AND (@day_working_hours * 60) < @minutes_remaining OR @days_work IS NOT NULL AND @minutes_remaining > 0
                                  THEN @days_work + 1
						     END as days_work
			 , CASE WHEN (@day_working_hours * 60) > @minutes_remaining AND @minutes_remaining > 0
					THEN CONCAT(DATE_ADD(date(ot.start_timestamp), INTERVAL @days_work DAY), ' ', DATE_ADD(och.start_time, INTERVAL @minutes_remaining MINUTE))
                    ELSE NULL
                    END as task_end_timestamp
		 FROM dates d
         LEFT JOIN  operative_contracted_hours och ON weekday(date) = och.week_day
		 JOIN operatives_tasks ot USING (user_id)
		 JOIN tasks t USING (task_id)
		 JOIN task_types tt USING (task_type_id)
		 JOIN users u USING (user_id)
         LEFT JOIN operatives_annual_leave oal ON oal.start_timestamp < CONCAT(date, ' ', och.finish_time)
                                               AND oal.end_timestamp > CONCAT(date, ' ', och.start_time)
                                               AND och.user_id = oal.user_id  
                                               
                                               ORDER BY date
       ) as times ON times.task_id = t.task_id

hope you can help further, thank you so far

June 26, 2020

i like that idea, i looked at the old code you helped me with which had the same principle 😁

$db->exec("CREATE TEMPORARY TABLE date_range (

              date DATE PRIMARY KEY

         )");

$stmt = $db->prepare("INSERT INTO date_range VALUES (?)");

foreach ($dp as $date) {

    $d = $date->format('Y-m-d');

    $stmt->execute([$d]);

}

regarding the query i added a dates table and inserted a number of dates ands ran the query, thank you, i increased the task duration and increased the contracted working hours on a few days while testing and saw the query would only work a maximum of two days, so i had a little play around, and came up with this

set @days_work         = NULL;
set @minutes_remaining = NULL;

SELECT t.task_id
	  , times.day_working_hours
      , times.username
      , times.task_type
      , times.date
      , times.duration_hours
      , times.task_start_timestamp
      , times.task_end_timestamp
      , times.day_name
      , times.remaining_minutes
      , times.days_work
      , times.holiday_start_timestamp
      , times.holiday_end_timestamp
      FROM tasks t
LEFT JOIN (
        SELECT date
             , t.task_id
             , u.username
             , tt.task_type
             , tt.duration_hours
             , ot.start_timestamp as task_start_timestamp
             , @day_working_hours := (timestampdiff(MINUTE, och.start_time, och.finish_time) - (timestampdiff(MINUTE, och.start_time, och.finish_time) DIV 270 ) * 30) / 60 as day_working_hours
             , DAYOFWEEK(date) as day_name
             , oal.start_timestamp as holiday_start_timestamp
		     , oal.end_timestamp as holiday_end_timestamp 
             , @days_work := IF (@day_working_hours >  @minutes_remaining, IF (@days_work IS NULL, 1, @days_work + 1), NULL) as days_work
			 , @minutes_remaining := IF (@minutes_remaining IS NULL, ((tt.duration_hours * 60) - (@day_working_hours * 60)), @minutes_remaining - (@day_working_hours * 60)) as remaining_minutes
             , CONCAT(IF ((@day_working_hours * 60)> @minutes_remaining , DATE_ADD(date, INTERVAL @days_work DAY), ''), ' ', IF ((@day_working_hours * 60) > @minutes_remaining, DATE_ADD(och.start_time, INTERVAL @remaining_minutes MINUTE), '')) as task_end_timestamp 
		 FROM dates d
         LEFT JOIN  operative_contracted_hours och ON weekday(date) = och.week_day
		 JOIN operatives_tasks ot USING (user_id)
		 JOIN tasks t USING (task_id)
		 JOIN task_types tt USING (task_type_id)
		 JOIN users u USING (user_id)
         LEFT JOIN operatives_annual_leave oal ON oal.start_timestamp < CONCAT(date, ' ', och.finish_time)
                                               AND oal.end_timestamp > CONCAT(date, ' ', och.start_time)
                                               AND och.user_id = oal.user_id  
       ) as times ON times.task_id = t.task_id
       ORDER BY task_id, date;

the results are here:

https://i.ibb.co/Zzn3C9z/Untitled4.png

what im trying to do is caluclate the number of days work it is for the task and the minutes remaining and add them to task start date, but the problem lies with days_work and hours_remaining not corresponding with the correct date and is all muddled up.

i feel if i can get the @days_work and @remaining_minutes in the right dates i can get a task end timestamp working for all different task durations

thank you for your help once again barand

June 25, 2020

hi Barand,

i've digested what it is you've done, but realised that the query would work of only that scenario, so ive had a little play for the last hour and this is what i've came up with

SELECT ot.task_id
     , tt.duration_hours
	 , ot.start_timestamp
     , @start_weekday := weekday(ot.start_timestamp)
	, @days_work := IF (@start_weekday = 0 AND monday.shift_hours > tt.duration_hours, 0, 
       IF (@start_weekday = 1 AND monday.shift_hours + tuesday.shift_hours > tt.duration_hours, 1, 
       IF (@start_weekday = 2 AND monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours > tt.duration_hours, 2,
	   IF (@start_weekday = 3 AND monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours > tt.duration_hours, 3, 
       IF (@start_weekday = 4 AND monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours + friday.shift_hours > tt.duration_hours, 4, 
       IF (@start_weekday = 5 AND monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours + friday.shift_hours + saturday.shift_hours > tt.duration_hours, 5, 
       IF (@start_weekday = 6  AND monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours + friday.shift_hours + saturday.shift_hours + sunday.shift_hours > tt.duration_hours, 6, 'who knows'))))))) as `days_work`
	, @hours_remaining := IF (@start_weekday = 0 AND monday.shift_hours > tt.duration_hours, tt.duration_hours, 
       IF (@start_weekday = 1 AND monday.shift_hours + tuesday.shift_hours > tt.duration_hours, tt.duration_hours - monday.shift_hours, 
       IF (@start_weekday = 2 AND monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours > tt.duration_hours, tt.duration_hours - (monday.shift_hours + tuesday.shift_hours), 
	   IF (@start_weekday = 3 AND monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours > tt.duration_hours, tt.duration_hours - (monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours), 
       IF (@start_weekday = 4 AND monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours + friday.shift_hours > tt.duration_hours, tt.duration_hours - (monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours), 
       IF (@start_weekday = 5 AND monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours + friday.shift_hours + saturday.shift_hours > tt.duration_hours, tt.duration_hours - (monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours + friday.shift_hours), 
       IF (@start_weekday = 6  AND monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours + friday.shift_hours + saturday.shift_hours + sunday.shift_hours > tt.duration_hours, tt.duration_hours - (monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours + friday.shift_hours + saturday.shift_hours), 
       tt.duration_hours - (monday.shift_hours + tuesday.shift_hours + wednesday.shift_hours + thursday.shift_hours + friday.shift_hours + saturday.shift_hours + sunday.shift_hours)))))))) as `hours_remaining`
	, ot.start_timestamp + INTERVAL (@days_work) DAY + INTERVAL (60 * @hours_remaining) MINUTE as `end_timestamp`
     FROM tasks t
INNER JOIN operatives_tasks ot ON ot.task_id = t.task_id
INNER JOIN task_types tt ON tt.task_type_id = t.task_type_id
INNER JOIN users u on u.user_id = ot.user_id
LEFT JOIN (SELECT user_id
				  , @contacted_hours_monday := TIMEDIFF(finish_time, start_time) AS `contracted_hours`
				  , @hours := CONCAT('2020-01-01 ', @contacted_hours)
				  , @break_duration := FLOOR(CAST(TIME_TO_SEC(@hours) / (60 * 60) AS DECIMAL(10, 1)) / 4.5) * 30
				  , CAST(TIME_TO_SEC(SUBSTRING( DATE_SUB(@hours, INTERVAL @break_duration MINUTE), 12, 19)) / (60 * 60) AS DECIMAL(10, 1)) AS `shift_hours`
		    FROM operative_contracted_hours
            WHERE week_day = 0
		   ) monday ON monday.user_id = u.user_id
LEFT JOIN (SELECT user_id
				  , @contacted_hours_monday := TIMEDIFF(finish_time, start_time) AS `contracted_hours`
				  , @hours := CONCAT('2020-01-01 ', @contacted_hours)
				  , @break_duration := FLOOR(CAST(TIME_TO_SEC(@hours) / (60 * 60) AS DECIMAL(10, 1)) / 4.5) * 30
				  , CAST(TIME_TO_SEC(SUBSTRING( DATE_SUB(@hours, INTERVAL @break_duration MINUTE), 12, 19)) / (60 * 60) AS DECIMAL(10, 1)) AS `shift_hours`
		    FROM operative_contracted_hours
            WHERE week_day = 1
		   ) tuesday ON tuesday.user_id = u.user_id
LEFT JOIN (SELECT user_id
				  , @contacted_hours_monday := TIMEDIFF(finish_time, start_time) AS `contracted_hours`
				  , @hours := CONCAT('2020-01-01 ', @contacted_hours)
				  , @break_duration := FLOOR(CAST(TIME_TO_SEC(@hours) / (60 * 60) AS DECIMAL(10, 1)) / 4.5) * 30
				  , CAST(TIME_TO_SEC(SUBSTRING( DATE_SUB(@hours, INTERVAL @break_duration MINUTE), 12, 19)) / (60 * 60) AS DECIMAL(10, 1)) AS `shift_hours`
		    FROM operative_contracted_hours
            WHERE week_day = 2
		   ) wednesday ON wednesday.user_id = u.user_id
LEFT JOIN (SELECT user_id
				  , @contacted_hours_monday := TIMEDIFF(finish_time, start_time) AS `contracted_hours`
				  , @hours := CONCAT('2020-01-01 ', @contacted_hours)
				  , @break_duration := FLOOR(CAST(TIME_TO_SEC(@hours) / (60 * 60) AS DECIMAL(10, 1)) / 4.5) * 30
				  , CAST(TIME_TO_SEC(SUBSTRING( DATE_SUB(@hours, INTERVAL @break_duration MINUTE), 12, 19)) / (60 * 60) AS DECIMAL(10, 1)) AS `shift_hours`
		    FROM operative_contracted_hours
            WHERE week_day = 3
) thursday ON thursday.user_id = u.user_id
LEFT JOIN (SELECT user_id
				  , @contacted_hours_monday := TIMEDIFF(finish_time, start_time) AS `contracted_hours`
				  , @hours := CONCAT('2020-01-01 ', @contacted_hours)
				  , @break_duration := FLOOR(CAST(TIME_TO_SEC(@hours) / (60 * 60) AS DECIMAL(10, 1)) / 4.5) * 30
				  , CAST(TIME_TO_SEC(SUBSTRING( DATE_SUB(@hours, INTERVAL @break_duration MINUTE), 12, 19)) / (60 * 60) AS DECIMAL(10, 1)) AS `shift_hours`
		    FROM operative_contracted_hours
            WHERE week_day = 4
) friday ON friday.user_id = u.user_id
LEFT JOIN (SELECT user_id
				  , @contacted_hours_monday := TIMEDIFF(finish_time, start_time) AS `contracted_hours`
				  , @hours := CONCAT('2020-01-01 ', @contacted_hours)
				  , @break_duration := FLOOR(CAST(TIME_TO_SEC(@hours) / (60 * 60) AS DECIMAL(10, 1)) / 4.5) * 30
				  , CAST(TIME_TO_SEC(SUBSTRING( DATE_SUB(@hours, INTERVAL @break_duration MINUTE), 12, 19)) / (60 * 60) AS DECIMAL(10, 1)) AS `shift_hours`
		    FROM operative_contracted_hours
            WHERE week_day = 5
) saturday ON saturday.user_id = u.user_id
LEFT JOIN (SELECT user_id
				  , @contacted_hours_monday := TIMEDIFF(finish_time, start_time) AS `contracted_hours`
				  , @hours := CONCAT('2020-01-01 ', @contacted_hours)
				  , @break_duration := FLOOR(CAST(TIME_TO_SEC(@hours) / (60 * 60) AS DECIMAL(10, 1)) / 4.5) * 30
				  , CAST(TIME_TO_SEC(SUBSTRING( DATE_SUB(@hours, INTERVAL @break_duration MINUTE), 12, 19)) / (60 * 60) AS DECIMAL(10, 1)) AS `shift_hours`
		    FROM operative_contracted_hours
            WHERE week_day = 6
) sunday ON sunday.user_id = u.user_id
INNER JOIN operatives_annual_leave oal ON oal.user_id = u.user_id
WHERE u.user_id = 1

https://i.ibb.co/GJYG2Vq/Untitled3.png

so i've worked out how many days work and hours remaining and added it all to the start timestamp, but the big problem is, what if the task takes more than 7 days, i could go forever with these if statements.

if its not possible with mysql then i could work it all out via php

what are your thoughts please?

thank you

June 24, 2020

ooook so your a mysql geneious 😄 jeeez, to be honest im gonna have to look at the manual with what your written here, but i just ran the query and the result is beautiful

i'll get onto implimenting annual holidays and bank holidays tomorrow, i'll let you know what it looks like, when its done.

thank you very much barand 😁😁😁

June 24, 2020

1 hour ago, Barand said:

Do all users always work the same hours every day? EG Wednesday 08:00 - 12:00 = short day and no break)

What if a boiler installation starts at 3pm on a Friday?

no, a user can have different work patterns, for instance a user could work monday, wednesday, friday from 8-1 each day.

1 hour ago, Barand said:

Suppose a task takes 10 hours. They start at 08:00 on day 1, take a 30 min break (12:30 - 13:00) then work until 16:00. They have now done 7.5 hours with 2.5 hour to go.

Day2, starts at 08:00 but at 09:30 they have done another 4.5 hours on that task so break until 10:00 and finishes the task at 11:00.

On to the next task - at 15:30 is due another break after 4.5 hours work so has break and goes home at 16:00 having had two breaks that day (7 hours work)

day 1 is correct in your scenario, but day 2 is a new day therefor if the user/operative is working from 8-4 again he/she will only be entitled to a break for 30 mins at 12.30, but break times can change depending on the users contacted hours. ie (8-12 no break)

but 30 mins every 4.5 hrs is rule of thumb (daily)

if working from, 08:00- 19:00, 9 hrs work the user would be entitled to 2 breaks, break at 12.00 - 12:30 then break at 16:30 - 17:00 and home for 19:00

it probably make more sense to have a break on the 4th hour if entitled to breaks.

would it be best for me to create a lof of if statements in the query? how is the best way for me to get the end results (task esistmated completion timestamp)?

thank you again barand

June 23, 2020

       @contacted_hours := TIMEDIFF(och.finish_time, och.start_time) AS `contracted_hours`,
       @hours := CONCAT('2020-01-01 ', @contacted_hours),
       @breaks := FLOOR(CAST(TIME_TO_SEC(@hours) / (60 * 60) AS DECIMAL(10, 1)) / 4.5) AS `breaks`,
	   @break_duration := @breaks * 30 AS `break_duration`,
	   CAST(TIME_TO_SEC(SUBSTRING( DATE_SUB(@hours, INTERVAL @break_duration MINUTE), 12, 19)) / (60 * 60) AS DECIMAL(10, 1)) AS `shift_hours`,
        IF (@breaks = 1, CONCAT("{[", DATE_ADD(och.start_time, INTERVAL 270 MINUTE), ":", DATE_ADD(och.start_time, INTERVAL 300 MINUTE),"]}"), 
        IF (@breakss = 2, CONCAT("{[", DATE_ADD(och.start_time, INTERVAL 270 MINUTE), ":", DATE_ADD(och.start_time, INTERVAL 300 MINUTE), "] , [", DATE_ADD(och.start_time, INTERVAL 540 MINUTE), ":", DATE_ADD(och.start_time, INTERVAL 570 MINUTE), "]}"), NULL)) AS `break_times`,

i've also added break times which will display as {[12:30:00:13:00:00]}

June 23, 2020

nice to hear from you again barand , the caluclations are based on 4 variables

firsly though ive noticed that i missed @contacted_hours := TIMEDIFF(och.finish_time, och.start_time) AS `contracted_hours`, from my sql

SELECT u.username,
       tt.task_type, 
       tt.duration_hours `task_duration_hours`,
       ot.start_timestamp AS `task_start_timestamp`, 
       @task_week_day_start := WEEKDAY(ot.start_timestamp) AS `task_week_day_start`,
       och.shift_hours,
       och.week_day,
       och.start_time AS `operatives_start_time`,
       och.finish_time AS `operatives_finish_time`,
       annual_leave_start_timestamp,
       annual_leave_end_timestamp
FROM tasks t
LEFT JOIN operatives_tasks ot ON ot.task_id = t.task_id
LEFT JOIN task_types tt ON tt.task_type_id = t.task_type_id
LEFT JOIN users u on u.user_id = ot.user_id
LEFT JOIN (
	SELECT och.user_id,
           och.week_day,
           och.start_time,
           och.finish_time,
		   @contacted_hours := TIMEDIFF(och.finish_time, och.start_time) AS `contracted_hours`,
		   @hours := CONCAT('2020-01-01 ', @contacted_hours),
		   @break_duration := FLOOR(CAST(TIME_TO_SEC(@hours) / (60 * 60) AS DECIMAL(10, 1)) / 4.5) * 30 AS `break_duration`,
		   CAST(TIME_TO_SEC(SUBSTRING( DATE_SUB(@hours, INTERVAL @break_duration MINUTE), 12, 19)) / (60 * 60) AS DECIMAL(10, 1)) AS `shift_hours`,
		   oal.start_timestamp AS `annual_leave_start_timestamp`,
           oal.end_timestamp AS `annual_leave_end_timestamp`
	FROM operative_contracted_hours och 
    INNER JOIN operatives_annual_leave oal ON oal.user_id = och.user_id
) AS och ON och.user_id = u.user_id 
WHERE u.user_id = 1

the 4 variables are:

@contacted_hours := TIMEDIFF(och.finish_time, och.start_time) AS `contracted_hours`,
@hours := CONCAT('2020-01-01 ', @contacted_hours),
@break_duration := FLOOR(CAST(TIME_TO_SEC(@hours) / (60 * 60) AS DECIMAL(10, 1)) / 4.5) * 30 AS `break_duration`,
CAST(TIME_TO_SEC(SUBSTRING( DATE_SUB(@hours, INTERVAL @break_duration MINUTE), 12, 19)) / (60 * 60) AS DECIMAL(10, 1)) AS `shift_hours`,

@contracted_hours for that weekday

@hours is just a created timestamp so i can workout the breaks

@break_duration is putting the timestamp into seconds and converting into a decimal (ie. 08:00 - 16:00 would be 8.0) then dividing by 4.5 hrs, as a employee is entitled to 30 mins break for every 4.5 hours work.

shift_hours then just taking away the 30 min break in this case returning 7.5 (hours)

in this particual query the task is 9 hours long, but i added a 2 hour annual leave for the next day, in operatives_annual_leave table.

so the operative works 7.5 each day in this case, and the task duration is 9 hours (1.5 hrs remaining), meaning the task would have to extend over 2 days, including the 2 hour annual leave next day, the operative should be finished 2020-06-24 11:30:00

i think im on the right track, but struggling to put it all together

thanks you for your reply barand

June 23, 2020

after along time away from trying to progamming, im trying to get back into the swing of things.

I'm trying to complete a project i once started that @Barand helped me out with many moons ago.

im trying to create a scheduling programme where a task is give to a operative and added to the operatives_tasks table, which stores the start timestamp of the task. Overall what im trying to achieve is to generate a finish timestamp for the task, based on the operatives, contracted days, hours, start time end time and any annual leave he/she man have in between aswell as the task duration.

here is my sql and results at present, but im kinda scratching my head on generating a task finish date.

SELECT u.username,
       tt.task_type, 
       tt.duration_hours `task_duration_hours`,
       ot.start_timestamp AS `task_start_timestamp`, 
       @task_week_day_start := WEEKDAY(ot.start_timestamp) AS `task_week_day_start`,
       och.shift_hours,
       och.week_day,
       och.start_time AS `operatives_start_time`,
       och.finish_time AS `operatives_finish_time`,
       annual_leave_start_timestamp,
       annual_leave_end_timestamp
FROM tasks t
LEFT JOIN operatives_tasks ot ON ot.task_id = t.task_id
LEFT JOIN task_types tt ON tt.task_type_id = t.task_type_id
LEFT JOIN users u on u.user_id = ot.user_id
LEFT JOIN (
	SELECT och.user_id,
           och.week_day,
           och.start_time,
           och.finish_time,
		   @hours := CONCAT('2020-01-01 ', @contacted_hours),
		   @break_duration := FLOOR(CAST(TIME_TO_SEC(@hours) / (60 * 60) AS DECIMAL(10, 1)) / 4.5) * 30 AS `break_duration`,
		   CAST(TIME_TO_SEC(SUBSTRING( DATE_SUB(@hours, INTERVAL @break_duration MINUTE), 12, 19)) / (60 * 60) AS DECIMAL(10, 1)) AS `shift_hours`,
		   oal.start_timestamp AS `annual_leave_start_timestamp`,
           oal.end_timestamp AS `annual_leave_end_timestamp`
	FROM operative_contracted_hours och 
    INNER JOIN operatives_annual_leave oal ON oal.user_id = och.user_id
) AS och ON och.user_id = u.user_id 
WHERE u.user_id = 1

any help would be appreciated, here are the tables also if required

https://pastebin.com/mKXLZUVJ

better quality picture

https://i.ibb.co/k01nYq2/Untitled.png

thank you

Sign In

Destramic

Posts

Joined

Last visited

Content Type

Profiles

Forums

Posts posted by Destramic

adding minutes

adding minutes

adding minutes

adding minutes

adding minutes

adding minutes

adding minutes

adding minutes

adding minutes

Cookie DB

Cookie DB

Cookie DB

Cookie DB

Cookie DB

Cookie DB

Job advise

long time no see

long time no see

long time no see

long time no see

long time no see

long time no see

long time no see

long time no see

long time no see

Browse

Activity

Important Information