Room for Cam and Andriy M

5:32 PM

Hi Andriy,

If you remember you had helped me with this a while back:

https://dba.stackexchange.com/questions/150570/retrieving-data-of-vehicle-drivers-that-have-not-been-assigned-to-a-job

Do you reckon you could help us with the following please: stackoverflow.com/questions/53748383/…

Andriy M

8:48 PM

Hey @Cam. Sorry, I haven't exactly got time for a fully fledged answer at the moment, might post one later but no promises. I do have this to tell you that you seem to have the correct condition to join the list of dates with the planner_events table in your query, but you've made a complete mess attempting to apply it correctly :)

I'll try and show you where the issue is and how to resolve it. I'm going to do it here, because compiling all the points and explanations into a sensible answer would take me much longer

But first, since I'd like this to be a learning experience for you, please let me point out that the sub derived table is returning exactly the same set as listofdays. It means it's doing redundant job with the left join and grouping.

So you might as well replace this:

  ( SELECT
       listofdays.planner_start_date
    FROM
       ( SELECT
            '2018-12-10' + INTERVAL seq.seq DAY AS planner_start_date
         FROM
            seq_0_to_999999 AS seq
         WHERE
            seq.seq <= TIMESTAMPDIFF(DAY, '2018-12-10', '2018-12-16')
        ) as listofdays
    LEFT JOIN planner_events AS e ON listofdays.planner_start_date = DATE(e.event_start_date <= listofdays.planner_start_date AND e.event_end_date >= listofdays.planner_start_date)
    GROUP BY listofdays.planner_start_date

with just this:

   ( SELECT
        '2018-12-10' + INTERVAL seq.seq DAY AS planner_start_date
     FROM
        seq_0_to_999999 AS seq
     WHERE
        seq.seq <= TIMESTAMPDIFF(DAY, '2018-12-10', '2018-12-16')
    ) as sub

And now about the joining condition. It's there in the sub derived table and it's this one: e.event_start_date <= listofdays.planner_start_date AND e.event_end_date >= listofdays.planner_start_date. Note that it shouldn't be inside the DATE function and you don't need to additionally compare listofdays.planner_start_date to anything, i.e. it should be just ... ON e.event_start_date <= listofdays.planner_start_date AND e.event_end_date >= listofdays.planner_start_date.

However, it's also in the wrong part of the query too. It should actually be applied to the planner_events instance you are joining to sub. So replacing the references accordingly it should be like this:

FROM
  (
    ...
  ) AS sub
  LEFT JOIN planner_events AS e ON e.event_start_date <= sub.planner_start_date AND e.event_end_date >= sub.planner_start_date

And that's all that's needed to resolve the immediate issue. I would also add that I'd resolve the driver reference via another left join, rather that using a correlated subquery inside a CASE expression, something like this:

SELECT
  ...
  COALESCE(d.driver_name, 'N/A') AS user_id
FROM
  (
    ...
  ) AS sub
  LEFT JOIN planner_events AS e ON e.event_start_date <= sub.planner_start_date AND e.event_end_date >= sub.planner_start_date
  LEFT JOIN drivers AS d ON p.user_id = d.driver_id AND p.user_type = 'driver'
ORDER BY ...

And that will be all I've got to say

Cam

9:20 PM

Hi Andriy,

You are absolutely right - what was I thinking of! What a fantastic explanation, thank you so much - you are a true genius!

I have modified the query and the results are exactly how they should be. Here is the final query.

SELECT sub.planner_start_date, COALESCE(d.driver_name, 'N/A') AS user_id, e.planner_id, e.event_name, e.event_start_date, e.event_start_time, e.event_end_date, e.event_end_time
FROM
( SELECT
'2018-12-30' + INTERVAL seq.seq DAY AS planner_start_date
FROM
seq_0_to_999999 AS seq

Andriy M

Not a genius, no, but thank you for the kind words all the same :)

Cam

I have just updated the post and give you credit for the right solution :)

https://stackoverflow.com/questions/53748383/display-planner-events-each-week-from-start-date-until-end-date

Cam

9:50 PM

Don't mind me asking, do you specialise in databases?

Andriy M

@Cam Oh good, your self-answer spares me the effort :)

@Cam I'm a developer, writing queries is a big part of my job

I've so far worked only with SQL Server, though. No working experience with any other platforms

Cam

Is that more around asp.net?

Andriy M

10:06 PM

Delphi. No .Net at all for me

Cam

Excellent - Delphi is starting to get very popular since last year, even though it has been around for a while. How long have you been developing in Delphi?

Andriy M

Well, I didn't actually have an actual job developing in Delphi until 10 years ago. Before that it was on and off, just some home projects, not sure but another 10 years perhaps.

You are right, it used to be popular in the olden days, then something happened and lo and behold, suddenly it's no longer one of the best development environments any more. Embarcadero seem to be doing a decent job making it popular again, but it's tough now for Delphi to get to the level of popularity it had in the beginning

Hey, I'll be calling it a day, it's late here. Was happy to help. Take care

Cam

10:24 PM

No worries at all, it was nice speaking to you. Thank you once again and speak soon.

Transcript for

Room for Cam and Andriy M