How to query the Gross Revenue group by STORE, by item's CATEGORY for each days in year?

Question

I have SALES table of each days, its has an STOREID and the ITEMID with the DATE and GROSS

| DATE          | STOREID   | ITEMID    | GROSS     |
|------------   |---------  |--------   |-------    |
| 2020-07-07    | STORE1    | ITEM1     | 10000     |
| 2020-07-07    | STORE2    | ITEM1     | 25000     |
| 2020-07-06    | STORE2    | ITEM3     | 15000     |
| 2020-07-06    | STORE3    | ITEM2     | 21000     |

The PRODUCT table show the category of the items, we have 5 items with 5 categories:

| ITEMID    | CATEGORY  |
|--------   |---------- |
| ITEM1     | A         |
| ITEM2     | B         |
| ITEM3     | C         |
| ITEM4     | B         |
| ITEM5     | D         |

How can I select the revenue by storeid, itemid of each day with all of the category (if that day only sold category A, B then category C, D will show with gross is 0). Here the example of expected result when selecting the gross of July 7 for STORE1:

| DATE          | STOREID   | CATEGORY  | GROSS     |
|------------   |---------  |---------- |-------    |
| 2020-07-07    | STORE1    | A         | 10000     |
| 2020-07-07    | STORE1    | B         | 0         |
| 2020-07-07    | STORE1    | C         | 0         |
| 2020-07-07    | STORE1    | D         | 0         |

I have tried:

SELECT distinct T.DATE, T.STOREID, P.CATEGORY, ISNULL(T.GROSS,0) AS GROSS

FROM PRODUCT P LEFT JOIN (

    SELECT CONVERT(DATE, DATEID) AS DATE, STOREID, P.CATEGORY, convert(numeric(10,0), sum(S.GROSS)) AS GROSS
    FROM SALES S join PRODUCT P on S.ITEMID = P.ITEMID
    WHERE DATEID = '2020-07-07' and STOREID = 'STORE1'
    GROUP BY P.CATEGORY, DATEID, STOREID

) T

ON P.CATEGORY = T.CATEGORY

The results I get is something like this:

| DATE          | STOREID   | CATEGORY  | GROSS     |
|------------   |---------  |---------- |-------    |
| 2020-07-07    | STORE1    | A         | 10000     |
| 2020-07-07    | STORE1    | B         | 0         |
| NULL          | NULL      | C         | 0         |
| NULL          | NULL      | D         | 0         |

So when I execute the Query for others STORES and others days, how can I automated specify the correct value for the NULL value (like the expected result)

Thank you guys so much for your help!

score 1 · Answer 1 · answered Jul 07 '20 at 08:05

Is this homework? It looks a bit like homework, so instead of writing you a query, I will give you a strategy to think about.

You will first have to generate a cartesian product of date, and category, and store. Then find the sales that apply to each combination of {date, category, store}.

In general, generating "all of the dates you care about" is easy with a calendar table, or tally table, but in your specific case you could also generate all the dates you need for the cartesian product by querying your sales table for the distinct dates. This solution won't always work, because what if you want an output row for a date where no sales happened?

A cartesian product in sql is generated using a cross join

So, the approach you want to take:

Get a set of all the dates you need in the output
Cross join that against the set of all categories you need in the output
Cross join that against the set of all stores you need in the output
Left join that to the sales table

How to query the Gross Revenue group by STORE, by item's CATEGORY for each days in year?

1 Answers1