1

Here is my query. I need to return all active drivers for that month for each day even if for that day they did not transport someone. So someone may transport a patient today but may be off tomorrow and won't transport tomorrow. So for today driver 1 should in query for X number of transports but for tomorrow driver 1 should show in query for 0 number of transports. How is that possible with mysql?

  • schedule table provides information about the appointment
  • vehicle_driver table provides information about the providers(drivers) assigned to different vehicles for that day
  • drivers is like an employee table with driver information.

here's the query 

Select date_format(Date,'%d') as date, 
        concat(FirstName, ' ', LastName) as name, count(ScheduleID)
        schedule,vehicle_driver, drivers
    where schedule.crewunit = vehicle_driver.id
        and Driver_ID IN (vehicle_driver.provider1, vehicle_driver.provider2)
        and schedule.Date between DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY) and CURRENT_DATE)
        and schedule.activityStatus = 0
    group_by schedule.Date, name ASC
    order_by trans_schedule.Date ASC

Here's some more information....

Here's what the table results are (I only included it for 11/1/2018):

date    name        Trips
1       Ashley      4
1       Bruce       6
1       Carlos      16
1       Charles     4
1       Charles     4
1       Curtis      4
1       Frances     7
1       Garrick     4
1       James       6
1       Janet       5
1       Katherine   4
1       Kim         13
1       Mark        2
1       Ronnie      15
1       stuart      5

I'd like for it to give me results like this:

1       Ashley      4
1       Bruce       6
1       Carlos      16
1       Charles     4
1       Charles     4
**1     Chris       0**
1       Curtis      4
1       Darrell     0
1       Frances     7
1       Garrick     4
**1     Heath       0**
1       James       6
1       Janet       5
**1     Joseph      0
1       Katelyn     0**
1       Katherine   4
1       Kim         13
**1     Linzie      0**
1       Mark        2
**1     Roger       0**
1       Ronnie      15
**1     Ryan        0**
1       stuart      5
**1     Tiffany     0**
Sirmyself
  • 1,464
  • 1
  • 14
  • 29
Ali
  • 11
  • 3
  • 3
    Can you post table def and data sample? – DanB Nov 13 '18 at 15:27
  • See e.g. [MySQL how to fill missing dates in range?](https://stackoverflow.com/q/3538858) – Solarflare Nov 13 '18 at 16:38
  • I just added some more info. PLease let me know if that helps. It didn't format it really well. – Ali Nov 13 '18 at 19:06
  • You want all the dates in a month, even if all the drivers are showing zero on that day ? Can the date range in the query span across months (and even years) ?? Due to lots of tables involved, will it be possible for you to setup a https://www.db-fiddle.com/ . Please go through this link once: https://meta.stackoverflow.com/q/333952/2469308 – Madhur Bhaiya Nov 17 '18 at 08:00
  • https://www.db-fiddle.com/f/s1i3SguMgfLdhSsuSwwAoy/5 – Ali Nov 18 '18 at 16:20
  • For some reason value is not being returned though on db-fiddle – Ali Nov 18 '18 at 16:20
  • Yes, date range could span over multiple months. I was trying to keep it simple with just 1 month. – Ali Nov 18 '18 at 16:21
  • Also the purpose of this data is populate google line reports. It requires all unique values as keys and then you have to provide all the values for all keys. – Ali Nov 18 '18 at 16:22

0 Answers0