php - get all record between two dates interval in mysql
897
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.
Answer
Solution:
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.
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.