HI Everyone i am trying to write query to get last 4 week data individual, but if any data not exist how to get 0 or null on that row, pls help me out.
this is sampal data
https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=c87b46766f39a8f3e03687270bcbae21
SELECT week_start_date,week_end_date,driver_id,driver_name,fleet_driver.mobile,
CASE WHEN week_end_date >= NOW() - INTERVAL 4 WEEK
AND week_end_date < NOW() - INTERVAL 3 WEEK THEN '4th week'
WHEN week_end_date >= NOW() - INTERVAL 3 WEEK
AND week_end_date < NOW() - INTERVAL 2 WEEK THEN '3rd week'
WHEN week_end_date >= NOW() - INTERVAL 2 WEEK
AND week_end_date < NOW() - INTERVAL 1 WEEK THEN '2nd week'
WHEN week_end_date >= NOW() - INTERVAL 1 WEEK
THEN 'last week'
END WeekPeriod, IFNULL (total_trips,0) total_trips,driver_rating
FROM fleet_driver_dash_weekly
left join fleet_driver
on fleet_driver_dash_weekly.driver_id=fleet_driver.id
WHERE team_id= %s
and week_end_date >= NOW() - INTERVAL 4 WEEK order by driver_id
Expected output
| week_start_date | week_end_date | driver_id | WeekPeriod | total_trips | driver_rating |
|---|---|---|---|---|---|
| 2022-05-09 | 2022-05-15 | 10 | Last week | 50 | Mediocre |
| 2022-05-02 | 2022-05-08 | 10 | 2nd week | 50 | Mediocre |
| 2022-04-25 | 2022-05-01 | 10 | 3rd week | 0 | null |
| 2022-04-18 | 2022-04-24 | 10 | 4th week | 0 | null |
| 2022-05-09 | 2022-05-15 | 20 | Last week | 50 | Mediocre |
| 2022-05-02 | 2022-05-08 | 20 | 2nd week | 0 | null |
| 2022-04-25 | 2022-05-01 | 20 | 3rd week | 0 | null |
| 2022-04-18 | 2022-04-24 | 20 | 4th week | 0 | null |
| 2022-05-09 | 2022-05-15 | 30 | Last week | 50 | Mediocre |
| 2022-05-02 | 2022-05-08 | 30 | 2nd week | 0 | null |
| 2022-04-25 | 2022-05-01 | 30 | 3rd week | 0 | null |
| 2022-04-18 | 2022-04-24 | 30 | 4th week | 0 | null |
