Sql-server – Sum purchased & sold quantities for all items on daily/weekly

sql serversql-server-2008

I have 4 tables Items, DimDate, Purchase and Sales for items definitions, calendar table, purchased and sold Items respectively. Tables structure here

I need to outer join sum(Purchased_Qty) and sum(Sales_Qty) with a date range, and also with the Items table so that the results would display as follows:

Date, Items.Product_Name, Sum(purchased_Qty), Sum(sold_Qty) 
group by [Date], Items.Product_Name.

I need to display purchased & sold quantities for all items and for all days of a date range.

THIS is an example of a possible starting point query, i tried several combinations of joins & sub queries on query in this example, but none of them is correct in logic as i tested them.

Best Answer

i need to display purchased & sold quantities for all items and for all days of a date range.

According to this I applied Cartesian product between dimDates and Items using OUTER APPLY, that should return a combination of all dates and all items.

Then I've used two subqueries in the SELECT but you could LEFT JOIN Purchases and Sales tables.

SELECT d.[Date], 
       i.Product_Code, 
       i.Product_Name,
       COALESCE((SELECT SUM(Purchased_Qty)
                 FROM   dbo.Purchase
                 WHERE  Product_Code = i.Product_Code
                 AND    Purchase_Date = d.Date), 0) Purchased,
       COALESCE((SELECT SUM(Sale_Qty)
                 FROM   dbo.Sales
                 WHERE  Product_Code = i.Product_Code
                 AND    Sale_Date = d.Date), 0) Saled
FROM   [dbo].[DimDate] d
OUTER APPLY (
            SELECT Product_Code, Product_Name
            FROM   dbo.Items 
            ) i            
WHERE  d.[Date] >= '20180115'
AND    d.[Date] <= '20180116';
GO

Date                | Product_Code | Product_Name | Purchased | Saled
:------------------ | :----------- | :----------- | :-------- | :----
15/01/2018 00:00:00 | P1           | ProductA     | 26        | 20   
15/01/2018 00:00:00 | P2           | ProductB     | 5         | 20   
15/01/2018 00:00:00 | P3           | ProductC     | 20        | 40   
15/01/2018 00:00:00 | P4           | ProductD     | 0         | 0    
15/01/2018 00:00:00 | P5           | ProductE     | 0         | 0    
16/01/2018 00:00:00 | P1           | ProductA     | 15        | 21   
16/01/2018 00:00:00 | P2           | ProductB     | 37        | 22   
16/01/2018 00:00:00 | P3           | ProductC     | 23        | 3    
16/01/2018 00:00:00 | P4           | ProductD     | 0         | 0    
16/01/2018 00:00:00 | P5           | ProductE     | 0         | 0    

DECLARE @Start_Date date = '20180115';
DECLARE @End_Date date = '20180116';

SELECT d.[Date], 
       i.Product_Code, 
       i.Product_Name,
       COALESCE(p.Purchased_Qty, 0) Purchased_Qty,
       COALESCE(s.Sale_Qty, 0) Sale_Qty
FROM   [dbo].[DimDate] d
OUTER APPLY (
            SELECT Product_Code, Product_Name
            FROM   dbo.Items 
            ) i
LEFT JOIN (SELECT Product_Code, Purchase_Date, SUM(Purchased_Qty) Purchased_Qty
           FROM   dbo.Purchase
           WHERE  Purchase_Date >= @Start_Date
           AND    Purchase_Date <= @End_Date
           GROUP BY Product_Code, Purchase_Date) p
ON p.Product_Code = i.Product_Code
AND p.Purchase_Date = d.[Date]
LEFT JOIN (SELECT Product_Code, Sale_Date, SUM(Sale_Qty) Sale_Qty
           FROM   dbo.Sales
           WHERE  Sale_Date >= @Start_Date
           AND    Sale_Date <= @End_Date
           GROUP BY Product_Code, Sale_Date) s
ON s.Product_Code = i.Product_Code
AND s.Sale_Date = d.[Date]
WHERE  d.[Date] >= @Start_Date
AND    d.[Date] <= @End_Date
ORDER BY d.[Date], i.Product_Code;
GO

Date                | Product_Code | Product_Name | Purchased_Qty | Sale_Qty
:------------------ | :----------- | :----------- | :------------ | :-------
15/01/2018 00:00:00 | P1           | ProductA     | 26            | 20      
15/01/2018 00:00:00 | P2           | ProductB     | 5             | 20      
15/01/2018 00:00:00 | P3           | ProductC     | 20            | 40      
15/01/2018 00:00:00 | P4           | ProductD     | 0             | 0       
15/01/2018 00:00:00 | P5           | ProductE     | 0             | 0       
16/01/2018 00:00:00 | P1           | ProductA     | 15            | 21      
16/01/2018 00:00:00 | P2           | ProductB     | 37            | 22      
16/01/2018 00:00:00 | P3           | ProductC     | 23            | 3       
16/01/2018 00:00:00 | P4           | ProductD     | 0             | 0       
16/01/2018 00:00:00 | P5           | ProductE     | 0             | 0

dbfiddle here

Related Question