php - get all record between two dates interval in mysql


get all record between two dates interval in mysql

I have a user and resource_bookings tables in redmine. I want to get record of users who works 8 hours, less than 8 hours, 0 hours and more than 8 hours work between two given dates on basis of date period.

User table:

id  firstname   lastname    status  type
1   Ankur       Mishra      1       user
2   Anil        Chauhan     1       user
3   Jitendra    Singh       1       user

resource_bookings table:

id  project_id  user_id   start_date                end_date              hours_per_day
1   363         3         2019-09-13 00:00:00       2019-10-31 00:00:00   4 
2   350         3         2019-09-01 00:00:00       2019-11-30 00:00:00   6 
3   316         260       2020-01-01 00:00:00       2020-02-31 00:00:00   8.5 

My expected output is:

When user select start date '2019-08-01' and end date '2020-02-01', expected out is like this:

id  user_id   user_name       start_date       end_date     hours_per_day
1   1         Ankur Mishra    2019-08-01       2020-02-01   0 
2   2         Anil Chauhan    2019-08-01       2020-02-01   0 
3   3         Jitendra Singh  2019-08-01       2019-08-31   0
4   3         Jitendra Singh  2019-09-01       2019-09-12   6
5   3         Jitendra Singh  2019-09-13       2019-10-31   10
6   3         Jitendra Singh  2019-11-01       2019-11-30   6
7   3         Jitendra Singh  2019-12-01       2020-02-01   0

Please note in above expected output, we need all date intervals from starting date to end date even if the user is unscheduled for any slot/period. And If two slot period overlap we need sum of those slots in output.




What you are looking for is called outer join. You want to show all users with their dates in the given period, but still show the users when they don't have a date within.

  coalesce(rb.start_date, param.start_date) as start_date,
  coalesce(rb.end_date, param.end_date) as end_date,
  coalesce(rb.hours_per_day, 0) as hours_per_day
from (select date '2019-08-01' as start_date, date '2020-02-01' as end_date) param
cross join user u
left join resource_bookings rb on rb.user_id = 
                              and rb.start_date <= param.end_date
                              and rb.end_date >= param.start_date
order by,;

As you see, I am also using a cross join in my query in order to introduce the parameters once. This is not necessary, but otherwise you'd have to put the parameter dates multiple times in the query.

People are also looking for solutions to the problem: array combine - How to fill a empty value in KEY=> VALUE for array_combine in php


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.