0

For all the days between 2003-04-01 and 2003-04-07 find the number of trips from town Rostov. Result set: date, number of trips.

Here is the database schema: http://sql-ex.ru/help/select13.php#db_4

query and result set:

SELECT MyDates.d as Dt, 
       count(distinct trip.trip_no) as Qty
FROM   trip 

JOIN pass_in_trip 
         ON trip.trip_no = pass_in_trip.trip_no 
FULL OUTER JOIN
(select cast(cast(x.yr as varchar)+'-'+cast(y.mo as varchar)+'-'+cast(z.dy as varchar) as datetime) d from (select 2003 yr) x CROSS JOIN (SELECT 4 mo) y CROSS JOIN (select 1 dy UNION ALL select 2 UNION ALL select 3 UNION ALL select 4 UNION ALL select 5 UNION ALL select 6 UNION ALL select 7) z where dy<=7) MyDates
ON Mydates.d = pass_in_trip.date
WHERE  trip.town_from = 'Rostov' 
GROUP BY MyDates.d, pass_in_trip.date, trip.trip_no

+-------------------------+-----+
|           Dt            | Qty |
+-------------------------+-----+
| NULL                    |   1 |
| NULL                    |   1 |
| NULL                    |   1 |
| NULL                    |   1 |
| 2003-04-01 00:00:00.000 |   1 |
| 2003-04-05 00:00:00.000 |   1 |
+-------------------------+-----+

Here is the result set I want

+-------------------------+-----+
|           Dt            | Qty |
+-------------------------+-----+
| 2003-04-01 00:00:00.000 |   1 |
| 2003-04-02 00:00:00.000 |   0 |
| 2003-04-03 00:00:00.000 |   0 |
| 2003-04-04 00:00:00.000 |   0 |
| 2003-04-05 00:00:00.000 |   1 |
| 2003-04-06 00:00:00.000 |   0 |
| 2003-04-07 00:00:00.000 |   0 |
+-------------------------+-----+

FYI - I think this is using MySQL or sql server, it's a bit unclear to me.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nona
  • 5,302
  • 7
  • 41
  • 79
  • SQL is not good at filling in values that are not present in the data. One common solution is to have a "dates" table that includes every date that should reasonably be included in your data. See [this question](http://stackoverflow.com/questions/75752) for an example. – D Stanley Mar 22 '16 at 20:17
  • 1
    In Postgres this would be easily achieved with `generate_series(date, date)` :-) – Kamil Gosciminski Mar 22 '16 at 20:18

0 Answers0