Background: I have two tables, let's call them "purchaseTransactions" and "Products", the first one, holds all the transaction made by product, and the second one has all my products available, let's see an example oh how the look:
PurchaseTransactions
- PurchaseDate
- ProductId
- Quantity
- Some other fields...
Products
- ProductName
- ProductNumber
- Some other fields...
Basically what I want to obtain is the quantity sold by month and product from a specific timelapse, For instance:
Let's say we have the Products
- Name: Product A, No: 01
- Name: Product B, No: 02
- Name: Product C, No: 03
For Purchase Orders
- PurchaseDate: Jan/01/2018, ProductId: Product A, Quantity: 100
- PurchaseDate: Jan/18/2018, ProductId: Product A, Quantity: 150
- PurchaseDate: Feb/02/2018, ProductId: Product A, Quantity: 150
- PurchaseDate: Feb/24/2018, ProductId: Product A, Quantity: 200
PurchaseDate: April/04/2018, ProductId: Product A, Quantity: 200
PurchaseDate: January/01/2018, ProductId: Product B, Quantity: 222
- PurchaseDate: March/02/2018, ProductId: Product B, Quantity 250
- PurchaseDate: May/04/2018, ProductId: Product B, Quantity: 333
- PurchaseDate: May/20/2018, ProductId: Product B, Quantity: 666
An example of what I want is to bring ALL the products sold grouped by Year,Month, Product and Sum of its quantity. Something like this.
Year Month, ProductId, QuantitySold
-----------------------------------
2018 Jan Product A 250
2018 Jan Product B 222
2018 Jan Product C 0
2018 Feb Product A 250
2018 Feb Product B 0
2018 Feb Product C 0
2018 March Product A 0
2018 March Product B 0
2018 March Product C 250
.... etc
Notice that the query should return even the products that does not have any purchases in the specified month, this is what I have tried so far:
SELECT YEAR(PurchaseDate), MONTH(PurchaseDate), ProductId, SUM(Quantity)
FROM PurchaseOrders
WHERE Product IN (Product A, Product B...) AND MONTH(PurchaseDate) => DATEADD(MONTH, -3, GETDATE()) --Could be by some specific products and for a specific timelapse, ex the past three months.
GROUP BY YEAR(PurchaseDate), MONTH(PurchaseDate), ProductId
But this only returns the Products existing in the Purchase table, I have tried with a LEFT JOIN with the Products table to get all the products, but I think I am not implementing it correctly.
Any Idea of how to do this?