2

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.

  • 2
    You might get faster answers if you provide some sample data as inserts and expected results. – Erik Darling Jan 25 '18 at 20:41
  • When you say "I cannot join properly to get correct results," you don't specify what your problem is. Can you provide more detail as to exactly what's causing a problem? – RDFozz Jan 25 '18 at 20:47
  • One possibility: If you're joining each of the three transaction tables to the Products and/or calendar date tables, your totals may be too high. This would be due to the fact that for a given product and date record, SQL would provide a cross-product of the matching rows from the three transaction tables, leading to those totals being multiplied by the number of matching rows (when more than one matches). See my answer to this question, and see if it helps any. – RDFozz Jan 25 '18 at 20:52
  • @RDFozz the possibility is typical ! the totals are too high when more than a match occur. I read your answer to the other question, your explanation how DB engine works is thorough, thank you. however am still unable to think a solution through. may be some sleep will help. sp_BlitzErik i'll work on some sample data when i wake up. – MHDuke Jan 25 '18 at 23:10
  • yes show how record are inserted in these 3 tables for one transaction.how many rows are there. – KumarHarsh Jan 26 '18 at 5:41
1

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

  • 1
    @MHDuke have you checked the answer? – McNets Feb 5 '18 at 18:08
1

This is a bit of a guess, but I think you are after something a little bit like this;

Select i.name, sqty, p.date, pqty 
From
    (Select sum(purchased) as pqty, item, date
     From 
         Purchases
     Group by item, date
    ) p
  Left Outer Join 
    (Select sum(sold) as sqty, item, date
     From 
         Sales
     Group by item, date
    ) s On s.item = p.item and s.date = p.date
  Join 
    items i On i.id = p.item
 ;
  • what do you mean by the expression "items" in both sub queries and in last line "i.id = items"? – MHDuke Feb 4 '18 at 12:12
  • I edited the post - my excuse is I did it off my phone. The concept I wanted to imply is a join to an items table – LJ01 Feb 5 '18 at 22:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.