CASE WHEN formula

Analytics
Enthusiast

CASE WHEN formula

Hello, can anyone help?

 

I am putting the following part to the query:

 

      ,CASE WHEN (sw.BRAND_SWITCH_DATE >= '01.01.2018') then 'CY' 
      WHEN (sw.BRAND_SWITCH_DATE >= '01.01.2017' and < '01.01.2018') then 'LY' END as Time_Year      ,CASE WHEN (sw.BRAND_SWITCH_DATE >= '01.07.2018' and < '01.10.2018') then 'Q3 2018' 
      WHEN (sw.BRAND_SWITCH_DATE >= '01.04.2018' and  < '01.07.2018') then 'Q2 2018' 
      WHEN (sw.BRAND_SWITCH_DATE >= '01.01.2018' and < '01.04.2018') then 'Q1 2018' 
      WHEN (sw.BRAND_SWITCH_DATE >= '01.01.2017' and < '01.04.2017') then 'Q1 2017' 
      WHEN (sw.BRAND_SWITCH_DATE >= '01.04.2017' and < '01.07.2017') then 'Q2 2017' 
      WHEN (sw.BRAND_SWITCH_DATE >= '01.07.2017' and <'01.10.2017') then 'Q3 2017' 
      WHEN (sw.BRAND_SWITCH_DATE >= '01.10.2017' and < '01.01.2018') then 'Q4 2017' END as Quarter

 The error shows: "Syntax error: expected something between the 'and' keyword and '>'.


Accepted Solutions
Highlighted
Teradata Employee

Re: CASE WHEN formula

Hi magda_880,

 

Just add an AND condition in the WHEN part :

case
  when trans.TRANS_DATE_KEY >= add_months (sw.BRAND_SWITCH_DATE, -4)
   and trans.TRANS_DATE_KEY <  add_months (sw.BRAND_SWITCH_DATE, -1)
   and extract(year from trans.TRANS_DATE_KEY) = 2018
  then ...
  else ...
end
1 ACCEPTED SOLUTION
6 REPLIES
Senior Apprentice

Re: CASE WHEN formula

Hi,

 

On the second line of code that you show, change:

CASE WHEN (sw.BRAND_SWITCH_DATE >= '01.07.2018' and < '01.10.2018')

to

CASE WHEN (sw.BRAND_SWITCH_DATE >= '01.07.2018' and sw.BRAND_SWITCH_DATE < '01.10.2018')

HTH

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Senior Apprentice

Re: CASE WHEN formula

Sorry, I should have said...

 

All of your WHEN clauses need to change from (for example):

(sw.BRAND_SWITCH_DATE >= '01.01.2017' and < '01.01.2018')

to

(sw.BRAND_SWITCH_DATE >= '01.01.2017' and sw.BRAND_SWITCH_DATE < '01.01.2018')

HTH

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: CASE WHEN formula

Also, BRAND_SWITCH_DATE appears to be a DATE column, so better to use ANSI date literals such as date'2018-04-01' instead of character strings like '01.04.2018' which rely on implicit data type conversion.

 

Junior Contributor

Re: CASE WHEN formula

CASE stops at the first TRUE condition, thus there's no need for ANDed conditions:

 

      ,CASE WHEN sw.BRAND_SWITCH_DATE >= '01.01.2018' then 'CY' 
            WHEN sw.BRAND_SWITCH_DATE >= '01.01.2017' then 'LY' -- all rows from 2018+ are already covered by the first WHEN
END as Time_Year ,CASE WHEN >= '01.10.2018' then null -- probably not needed because you filter in WHERE?
WHEN sw.BRAND_SWITCH_DATE >= '01.07.2018' then 'Q3 2018' WHEN sw.BRAND_SWITCH_DATE >= '01.04.2018' then 'Q2 2018' WHEN sw.BRAND_SWITCH_DATE >= '01.01.2018' then 'Q1 2018' WHEN sw.BRAND_SWITCH_DATE >= '01.01.2017' then 'Q1 2017' WHEN sw.BRAND_SWITCH_DATE >= '01.04.2017' then 'Q2 2017' WHEN sw.BRAND_SWITCH_DATE >= '01.07.2017' then 'Q3 2017' WHEN sw.BRAND_SWITCH_DATE >= '01.10.2017' then 'Q4 2017'
END as Quarter

But as this is simply formatting a date the 2nd CASE can be replaced by a simple TO_CHAR:

To_Char(sw.BRAND_SWITCH_DATE, '"Q"q yyyy')

 

Of course, follow Fred's advice to use Standard SQL date literal: DATE '2018-01-01'

 

Enthusiast

Re: CASE WHEN formula

Hello,

Having specified number of months between certain months, can I also specify year to be 2018? Would it be double case?

 

Below I only have 1 CASE

SUM(CASE WHEN trans.TRANS_DATE_KEY >= add_months (sw.BRAND_SWITCH_DATE,-4) and trans.TRANS_DATE_KEY < add_months (sw.BRAND_SWITCH_DATE,-1) then 'NRN_CY_PRE' END)

 

Highlighted
Teradata Employee

Re: CASE WHEN formula

Hi magda_880,

 

Just add an AND condition in the WHEN part :

case
  when trans.TRANS_DATE_KEY >= add_months (sw.BRAND_SWITCH_DATE, -4)
   and trans.TRANS_DATE_KEY <  add_months (sw.BRAND_SWITCH_DATE, -1)
   and extract(year from trans.TRANS_DATE_KEY) = 2018
  then ...
  else ...
end