none
how to return sales information in sql ? RRS feed

  • Question

  • Hi, can help anybody,please

    here the first table

    PromoId ProductId Date
    1 11 08.07.2019
    1 12 09.07.2019

    PromoId  - promo Id

    ProductId  - Product Id

    Date  - Date of the promo

    here the second table

    ProductId Date Quantity
    1 08.07.2019 34
    1 09.07.2019 44
    1 09.07.2019 56
    1 09.07.2019 79

    ProductId  - Product Id

    Date  - Date of Sale

    Quantity- Sales of goods per day

    How to return sales information for all promo from the Promotions table,i,e, the table of the type 

    PromoId ProductId Date SalesQuantity
    1 12 09.07.2019 179  (44+56+79)
    For some promo, sales may not be in the day of the promotion. For products there can be several sales records for one day.

    How to do it

    Monday, July 8, 2019 12:06 PM

Answers

  • CREATE TABLE #Promos (PromoId INT, ProductId INT, Date DATE)
    INSERT INTO #Promos VALUES (1,11,'2019-07-08'),(1,12,'2019-07-09')
    
    CREATE TABLE #Sales (ProductId INT, Date DATE, Quantity INT)
    INSERT INTO #Sales VALUES (12,'2019-07-08',34),(12,'2019-07-09',44),
    (12,'2019-07-09',56),(12,'2019-07-09',79)
    
    SELECT pr.PromoId, pr.ProductId, pr.Date, SUM(s.Quantity) AS SalesQuantity
    FROM #Promos pr
    INNER JOIN #Sales s ON s.ProductId=pr.ProductId AND s.Date=pr.Date
    GROUP BY pr.PromoId, pr.ProductId, pr.Date
    
    DROP TABLE #Promos
    DROP TABLE #Sales



    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    • Marked as answer by merzavazeh Monday, July 8, 2019 9:29 PM
    Monday, July 8, 2019 1:19 PM
  • I made some changes in your sample data since the ProductId 12 in the table Promotions is not in the table Products:

    DECLARE @Promotions TABLE (
    	[PromoId] int,
    	[ProductId] int,
    	[Date] date
    );
    DECLARE @Products TABLE (
    	[ProductId] int,
    	[Date] date,
    	[Quantity] int
    );
    
    INSERT INTO @Promotions 
    VALUES (1, 11, '2019-07-08'), (1, 12, '2019-07-09');
    INSERT INTO @Products 
    VALUES (12, '2019-07-08', 34), (12, '2019-07-09', 44), (12, '2019-07-09', 56), (12, '2019-07-09', 79);
    
    SELECT prm.[PromoId], prm.[ProductId], pdc.[SalesQuantity]
    FROM @Promotions AS prm
    INNER JOIN (
    	SELECT [ProductId], [Date], SUM([Quantity]) AS [SalesQuantity]
    	FROM @Products 
    	GROUP BY [ProductId], [Date]
    ) AS pdc ON pdc.[ProductId] = prm.[ProductId] AND pdc.[Date] = prm.[Date];


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by merzavazeh Monday, July 8, 2019 9:29 PM
    Monday, July 8, 2019 1:55 PM

All replies

  • CREATE TABLE #Promos (PromoId INT, ProductId INT, Date DATE)
    INSERT INTO #Promos VALUES (1,11,'2019-07-08'),(1,12,'2019-07-09')
    
    CREATE TABLE #Sales (ProductId INT, Date DATE, Quantity INT)
    INSERT INTO #Sales VALUES (12,'2019-07-08',34),(12,'2019-07-09',44),
    (12,'2019-07-09',56),(12,'2019-07-09',79)
    
    SELECT pr.PromoId, pr.ProductId, pr.Date, SUM(s.Quantity) AS SalesQuantity
    FROM #Promos pr
    INNER JOIN #Sales s ON s.ProductId=pr.ProductId AND s.Date=pr.Date
    GROUP BY pr.PromoId, pr.ProductId, pr.Date
    
    DROP TABLE #Promos
    DROP TABLE #Sales



    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    • Marked as answer by merzavazeh Monday, July 8, 2019 9:29 PM
    Monday, July 8, 2019 1:19 PM
  • I made some changes in your sample data since the ProductId 12 in the table Promotions is not in the table Products:

    DECLARE @Promotions TABLE (
    	[PromoId] int,
    	[ProductId] int,
    	[Date] date
    );
    DECLARE @Products TABLE (
    	[ProductId] int,
    	[Date] date,
    	[Quantity] int
    );
    
    INSERT INTO @Promotions 
    VALUES (1, 11, '2019-07-08'), (1, 12, '2019-07-09');
    INSERT INTO @Products 
    VALUES (12, '2019-07-08', 34), (12, '2019-07-09', 44), (12, '2019-07-09', 56), (12, '2019-07-09', 79);
    
    SELECT prm.[PromoId], prm.[ProductId], pdc.[SalesQuantity]
    FROM @Promotions AS prm
    INNER JOIN (
    	SELECT [ProductId], [Date], SUM([Quantity]) AS [SalesQuantity]
    	FROM @Products 
    	GROUP BY [ProductId], [Date]
    ) AS pdc ON pdc.[ProductId] = prm.[ProductId] AND pdc.[Date] = prm.[Date];


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by merzavazeh Monday, July 8, 2019 9:29 PM
    Monday, July 8, 2019 1:55 PM