I have a hard time naming this in the title, thus also difficult to search for a solution..
I have a transaction table which contains (among other things):
etc
I then also have a table that contains "VIP" customers.
I would like to get a table that is like this:
So basically starting with the VIP customer table, then taking the distinct categories and distinct months, then repeat all company names so that is looks like the table above and aggregating all values for each combination of company- category-month.
What is the easiest way of doing this?
I have a transaction table which contains (among other things):
Company name | Category | Order date | Sold pieces |
1 | A | 2021-06-02 | 100 |
1 | A | 2021-06-18 | 20 |
2 | B | 2021-05-05 | 30 |
1 | B | 2021-06-05 | 10 |
3 | C | 2021-05-06 | 50 |
4 | B | 2021-05-06 | 40 |
I then also have a table that contains "VIP" customers.
Customer name |
1 |
2 |
I would like to get a table that is like this:
Company name | Category | Month | Pieces |
1 | A | May | 0 |
1 | B | May | 0 |
1 | C | May | 0 |
1 | A | June | 120 |
1 | B | June | 10 |
1 | C | June | 0 |
2 | A | May | 0 |
2 | B | May | 30 |
2 | C | May | 0 |
2 | A | June | 0 |
2 | B | June | 0 |
2 | C | June | 0 |
So basically starting with the VIP customer table, then taking the distinct categories and distinct months, then repeat all company names so that is looks like the table above and aggregating all values for each combination of company- category-month.
What is the easiest way of doing this?