CODEWITHSUNDEEP
mysqlsql
Miguel
Miguel

Reputation: 586

Building report with date gaps

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?

Upvotes: 0

Views: 81

Answers (1)

Nick
Nick

Reputation: 147206

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

Upvotes: 1

Related Questions