Splitting an interval based table into daily data

Question

I am trying to create a daily data view from an interval based table looking like this:

startDate  |  endDate  | Product | Qty | Price |
2018-11-01   2018-11-30     A       12     10

This is how I want the result in the view to be:

  Date     | Product  | Qty  | Price |
2018-11-01      A       12      10
2018-11-02      A       12      10
....
2018-11-30      A       12      10
2018-11-31      A        0       0  

As the above shows, I want to split the intervals into daily observations and if product A is not purchased for a given date then the quantity and price should be 0.

I have created a Calendar table to help me come up with a query but no luck so far.

I have been through these posts but have not been able it to my problem:

Break into multiple rows based on date range of a single row

https://dba.stackexchange.com/questions/168915/split-date-range-into-separate-records

generate days from date range

Answer 1

You have a calendar table, awesome. Just use it on the left side of a LEFT JOIN:

SELECT Calendar.Date, YourData.*
FROM Calendar
LEFT JOIN YourData ON Calendar.Date BETWEEN startDate AND endDate
WHERE Calendar.Date BETWEEN '2018-11-01' AND '2018-11-30'

If you want all date-product pairs then add a CROSS JOIN:

SELECT Calendar.Date, Products.Product, YourData.*
FROM Calendar
CROSS JOIN (
    SELECT Product
    FROM YourData
    GROUP BY Product
) AS Products
LEFT JOIN YourData ON
    Calendar.Date BETWEEN startDate AND endDate AND
    Products.Product = YourData.Product
WHERE Calendar.Date BETWEEN '2018-11-01' AND '2018-11-30'