CODEWITHSUNDEEP
sqljoinsum
user2888246
user2888246

Reputation: 185

SQL Sum revenue and from dates in second table

I have two tables which I'm trying to combine to return revenue figures between two dates defined in a second table.

Table 1

Product     Date         Revenue
A           01/01/2013   300
A           02/01/2013   400
A           03/01/2013   500
B           04/01/2013   100
B           08/01/2013   400
C           04/01/2013   800
C           10/01/2013   900

Table 2

 Product    Date1         Date2
    A          02/01/2013    04/01/2013
    B          05/01/2013    07/01/2013
    C          01/01/2013    11/01/2013

I need to join these tables so that I get the sum of revenue between dates and before the first date. So I'm hoping the resultant table to look like this

Table 3

Product    Revenue1    Date1       Revenue2     Date2
A          300         02/01/2013  900          04/01/2013
B          100         05/01/2013  400          07/01/2013
C          0           01/01/2013  1700         11/01/2013

I'm using teradata. Any help would be appreciated

Upvotes: 1

Views: 373

Answers (1)

adrianm
adrianm

Reputation: 14726

The difficult part is that all products might not have reveneue both before and between the dates. This returns NULL in those cases but you can use COALESCE/ISNULL if you want something else.

WITH RevenueBeforeDate1 AS (
    SELECT Table1.Product
          ,Table2.Date1
          ,Table2.Date2
          ,SUM(Table1.Revenue) AS SumRevenue
    FROM Table1
         INNER JOIN Table2
             ON Table1.Product = Table2.Product
    WHERE Table1.Date < Table2.Date1
    GROUP BY Table1.Product
            ,Table2.Date1
            ,Table2.Date2
)
,RevenueBetweenDates AS (
    SELECT Table1.Product
          ,Table2.Date1
          ,Table2.Date2
          ,SUM(Table1.Revenue) AS SumRevenue
    FROM Table1
         INNER JOIN Table2
             ON Table1.Product = Table2.Product
    WHERE Table1.Date BETWEEN Table2.Date1 AND Table2.Date2
    GROUP BY Table1.Product
            ,Table2.Date1
            ,Table2.Date2
)
SELECT COALESCE(RevenueBeforeDate1.Product, RevenueBetweenDates.Product) AS Product
      ,RevenueBeforeDate1.SumRevenue AS Revenue1
      ,COALESCE(RevenueBeforeDate1.Date1, RevenueBetweenDates.Date1) AS Date1
      ,RevenueBetweenDates.SumRevenue AS Revenue2
      ,COALESCE(RevenueBeforeDate1.Date2, RevenueBetweenDates.Date1) AS Date2
FROM RevenueBeforeDate1
     FULL OUTER JOIN RevenueBetweenDates
         ON RevenueBeforeDate1.Product = RevenueBetweenDates.Product

Edit
Feel a bit bad about the over-complicated code. Answered to fast.

Here is a much cleaner solution

SELECT Table2.Product
      ,SUM(RevenueBeforeDate1) AS Revenue1
      ,Table2.Date1
      ,SUM(RevenueBetweenDates) AS Revenue2
      ,Table2.Date2
FROM Table2
     LEFT JOIN Table1
         ON Table2.Product = Table1.Product
     CROSS APPLY (
         SELECT CASE WHEN Table1.Date < Table2.Date1
                     THEN Table1.Revenue
                     ELSE NULL
                END AS RevenueBeforeDate1
               ,CASE WHEN Table1.Date BETWEEN Table2.Date1 AND Table2.Date2
                     THEN Table1.Revenue
                     ELSE NULL
                END AS RevenueBetweenDates
     )
GROUP BY Table2.Product
        ,Table2.Date1
        ,Table2.Date2

Upvotes: 1

Related Questions