How can I join two tables in teradata in which the output columns are calculated based on a condition?

Question

I have two table, Table 1 :

Col1_1  Col1_2
A       X
B       Y
C       Z

Table 2 :

Col2_1  Col2_2  Col2_3     Col2_4
A          X    6/20/2018   10
B          Y    8/13/2018   20
C          Z    6/27/2018   10
A          X    8/13/2018   40
B          Y    9/3/2018    80
C          Z    12/1/2018   5

Here's the output I'm looking for (Today's date is 6/18/2018 ):

Col1_1  Col1_2  Sum of Col2_4 between today and (today + 5 days)    Sum of Col2_4  between (today + 5 days) and (today + 10 days)
A            X                          10                                   0
B            Y                           0                                   0
C            Z                           0                                  10

Here's the code I have so far in teradata:

SELECT 
Col1_1,
Col1_2, 
Case 
    WHEN Col2_3 > CURRENT_DATE and Col2_3 < CURRENT_DATE+5
    THEN SUM (Col2_4)
    ELSE '0'
    End as Calculated_Col_1,

Case 
    WHEN Col2_3 > CURRENT_DATE+5 and Col2_3 < CURRENT_DATE+10
    THEN SUM (Col2_4)
    ELSE '0'
    End as Calculated_Col_2, 

FROM Table1 as A

INNER JOIN Table2 as B
ON A.Col1_1 = B.Col2_1 
AND A.Col1_2 = B.Col2_2

WHERE -- ? ?

I am stuck at how I need to define the column names in the SELECT statement and how the WHEREcondition needs to be defined.

Any suggestion is appreciated! Thanks!

Answer 1

You need to move the SUM outside of the CASE and conditions on the calculated columns are applied in HAVING:

SELECT 
Col1_1,
Col1_2, 
SUM(Case 
      WHEN Col2_3 > CURRENT_DATE and Col2_3 < CURRENT_DATE+5
      THEN Col2_4
      ELSE 0
    End) as Calculated_Col_1,

SUM(Case 
      WHEN Col2_3 > CURRENT_DATE+5 and Col2_3 < CURRENT_DATE+10
      THEN Col2_4
      ELSE 0
    End as Calculated_Col_2, 

FROM Table1 as A

INNER JOIN Table2 as B
ON A.Col1_1 = B.Col2_1 
AND A.Col1_2 = B.Col2_2

HAVING Calculated_Col_1 > 1111 -- ot whatever