range of dates

[Destramic]

im trying to get the range of dates from my tasks table,  ranging from current date to the last date...but i also want to get the date even if a result/task doesn't exist

select DISTINCT(CURRENT_DATE()) as today,
	   scheduled_date, 
      (SELECT scheduled_date + INTERVAL 7 - weekday(scheduled_date) DAY FROM tasks order by scheduled_date desc limit 1) AS last_date
from tasks
HAVING today < last_date AND today > scheduled_date

 

result:

--------------------------------------------------------------------------

today                scheduled _date        last_date

2018-08-23

2018-09-24

2018-10-01

------------------------------------------------------------------------

so i only have 1 result in the table for 2018-09-24

the last date (2018-10-01) is the next monday from the furthest date 2018-09-24

 

what im trying to achieve is a range of dates between today and 2018-10-01

ie.

2018/09/24

2018/09/25

... etc

2018-09-30

2018-10-01

 

 

thank you guys

[Barand]

Easiest way to generate a range of dates is with a DatePeriod object in PHP.

$res = $db->query("SELECT MAX(schedule_date) FROM tasks");
$maxdate = $res->fetchColumn();

$d1 = new DateTime($maxdate);
$d2 = clone $d1;
$d2->modify('next monday + 1 days');

$dp = new DatePeriod($d1, new DateInterval('P1D'), $d2);

foreach ($dp as $date) {
    echo $date->format('Y-m-d') . '<br>';
} 

/* outputs

2018-09-24
2018-09-25
2018-09-26
2018-09-27
2018-09-28
2018-09-29
2018-09-30
2018-10-01
*/

What do you intend to do with date range once you have it?

[Destramic]

what im trying to achive to know when there is available slots for a particular employee...in this case a gas engineer.

 

SELECT scheduled_date,
       username,
       t.time_period,
       am.scheduled_time,
       pm.scheduled_time,
       (SELECT task_time FROM task_types WHERE type = 'Gas Service') AS task_time
       FROM users u
INNER JOIN user_role_mappings urm ON urm.user_id = u.user_id
INNER JOIN user_roles ur ON ur.user_role_id = urm.user_id
INNER JOIN tasks t ON t.user_id = u.user_id
LEFT JOIN (SELECT t.task_id,
				  tt.task_time AS `scheduled_time`
            FROM tasks t
            INNER JOIN task_types tt ON tt.task_type_id = t.task_type_id
            WHERE t.time_period = 'AM') AS am ON am.task_id = t.task_id
LEFT JOIN (SELECT t.task_id,
				  tt.task_time AS `scheduled_time`
            FROM tasks t
            INNER JOIN task_types tt ON tt.task_type_id = t.task_type_id
            WHERE t.time_period = 'PM') AS pm ON pm.task_id = t.task_id
WHERE ur.role = 'gas_service_engineer'
GROUP BY t.time_period
HAVING ((am.scheduled_time + task_time) < 360 OR (pm.scheduled_time + task_time) < 360)

 

ive been working on this for 2 nights now and its not returning the exact results.

my theory is to run this query get the engineers availbility, if theres a slot then book a task to those who are free, based on task time...also i wanted to range dates and get everything inbetwen.

overall thinking of it i don't need to the whole range of dates, because there is never no empty date for a two week period (so please scrap my first initial post...sorry to have waste your time)

if i can get this query working then i'll be set.

 

here is my results

which dont match the tasks and task times for the particular date

 

im looking to get there results from my query

date                    period             scheduled time

--------------------------------------------------------------------------

2018-08-21         AM                        180 

2018-08-21        PM                           60

2018-09-24        AM                         180

--------------------------------------------------------------------------

 

i believe its something wrong with my left joins, but what do i know.

i hope you can help and point me in the right direction....thank you barand, sorry again

 

 

 

[Barand]

Instant diagnosis before inspecting in detail

• If you GROUP BY time_period you get one row for each time_period (AM/PM). The rest of the data in the rows output will come from an arbitrary record within the group and is therefore meaningless.
• In your select clause, use a JOIN to task_type table instaed of the subquery

[Barand]

This comes close to your desired results, although our arithmetic differs

SELECT  
       t.scheduled_date
     , t.time_period 
     , SUM(tt.task_time) as scheduled_time
FROM tasks t
    INNER JOIN task_types tt 
        ON t.task_type_id = tt.task_type_id
    INNER JOIN users u 
        ON u.user_id = t.user_id       
    INNER JOIN user_role_mappings urm 
        ON urm.user_id = u.user_id
    INNER JOIN user_roles ur 
        ON ur.user_role_id = urm.user_role_id 
        AND ur.role = 'gas_service_engineer'
    INNER JOIN employee_working_days ewd 
        ON ewd.user_id = u.user_id
GROUP BY 
        t.scheduled_date, t.time_period;

+----------------+-------------+----------------+
| scheduled_date | time_period | scheduled_time |
+----------------+-------------+----------------+
| 2018-08-21     | AM          |            120 |
| 2018-08-21     | PM          |            120 |
| 2018-09-24     | AM          |            180 |
+----------------+-------------+----------------+

This only shows two dates and not those with no time et scheduled.

15 hours ago, Destramic said:

because there is never no empty date for a two week period

That assumption of "never" may come back to bite you one day.

[Destramic]

Quote

This comes close to your desired results, although our arithmetic differs

i just ran the query and seen the figures.  I assumed it was my error and not your query ?

but it works brillantly...can't thank you enough for your efforts barand.

thank you!

 

1 hour ago, Barand said:

because there is never no empty date for a two week period 

and i hope not