Building report with date gaps

Question

I need to make a report with sales by seller in a certain range of dates. The problem I have is that the report should also pick up sellers who have not made any sales on that day.

Vendors (table)

id | name
----------
 1 | John
 2 | Tom

Sales (table)

id | vendor_id |    date        | status | price
------------------------------------------------
 1 |        1  |    2019-01-01  |    0   | 100
 2 |        1  |    2019-01-01  |    0   | 100 
 3 |        2  |    2019-01-01  |    0   | 100
 4 |        2  |    2019-01-02  |    0   | 100

Desired Report: Total sales between 2019-01-01 and 2019-01-04

date        |vendor| total
--------------------------
2019-01-01  | John | 200
2019-01-01  | Tom  | 100
2019-01-02  | John | 0
2019-01-02  | Tom  | 100
2019-01-03  | John | 0
2019-01-03  | Tom  | 0
2019-01-04  | John | 0
2019-01-04  | Tom  | 0

To generate the sequence of days I'm using the date generator here

And when I cross (left join) the dates generated with the sales I get the following:

SELECT date_generator.daily_date, vendors.name, sales.price
        FROM ( 
            SELECT DATE_ADD(@startDate, INTERVAL (@i:=@i + 1) - 1 DAY) AS daily_date   
            FROM information_schema.columns, (SELECT @i:=0) gen_sub   
            WHERE DATE_ADD(@startDate, INTERVAL @i DAY) BETWEEN @startDate AND @endDate 
        ) date_generator 
        LEFT JOIN sales ON DATE(sales.date) = DATE(date_generator.daily_date)
        LEFT JOIN vendors ON vendors.id = sales.vendor_id
ORDER BY date_generator.daily_date

date        |vendor| price
--------------------------
2019-01-01  | John | 100
2019-01-01  | John | 100
2019-01-01  | Tom  | 100
2019-01-02  | Tom  | 100
2019-01-03  | null | null
2019-01-04  | null | null

Can you give me some trick to get the grouping of vendor and date?

Your approach seems wrong. Firstly, you never aggregate and sum() so how do you expect '200'? Secondly while the date generator with the sub-query is smart, it won't satisfy your needs because you'll only get as many rows per date (day) as many vendors have sales for that date and using left join you'll get NULL's for dates when no vendor had sales. I suggest you you simplify the query (besides adding grouping and aggregation) something like this https://www.db-fiddle.com/f/2TQJKn2SqZwEEqxcoqqc9M/1 and transfer the date interval logic to the application layer. — marekful, Feb 13 '19 at 11:36

Nick · Accepted Answer · 2019-02-13T12:07:51.380

As some days have no sales, you will have a NULL sales.vendor.id value, so nothing to JOIN to vendors. To make sure you get all the vendors on all the dates, you need to do a CROSS JOIN from date_generator to vendors, and then LEFT JOIN to sales. Then you need to take the SUM of sales.price and GROUP BY the date and the vendor to get your desired output:

SELECT date_generator.daily_date, vendors.name, SUM(sales.price)
        FROM ( 
            SELECT DATE_ADD(@startDate, INTERVAL (@i:=@i + 1) - 1 DAY) AS daily_date   
            FROM information_schema.columns, (SELECT @i:=0) gen_sub   
            WHERE DATE_ADD(@startDate, INTERVAL @i DAY) BETWEEN @startDate AND @endDate 
        ) date_generator 
        CROSS JOIN vendors
        LEFT JOIN sales ON DATE(sales.date) = DATE(date_generator.daily_date) AND vendors.id = sales.vendor_id
GROUP BY date_generator.daily_date, vendors.name

Really nice answer, I was freaking out. Thank you Nick! – Miguel Feb 13 '19 at 13:47 — Miguel, Feb 13 '19 at 13:47

Building report with date gaps

1 Answers1