Using multiple case conditions

Question

So I'm gathering 3 month intervals using Date_Year and Date_month columns but having some issues

For example, the first group is Oct-Dec 2016 which is fine. The second group is the one I'm having trouble with , Nov-Dec 2016 along with Jan 2017..

Here is the sample code:

SELECT
  [2016_Oct_Dec]    = SUM(CASE WHEN date_year = 2016 AND date_month IN 
                     (10,11,12) THEN Sales_amt END )
, [2016/17_Nov_Jan] = SUM(CASE WHEN (date_year = 2016 AND date_month IN 
                     (11,12)) AND ((date_year = 2017 AND date_month = 1)) 
                     THEN Sales_amt END )
From Sales

I tried to create two conditions (Nov-Dec 2016) and (Jan 2017) but no luck.

Any help appreciated.

Brien Foss Brien Foss · Accepted Answer · 2018-03-22T00:49:57

date_year cannot be 2016 AND 2017. Try using an OR instead of AND:

SUM(CASE WHEN (date_year = 2016 AND date_month IN 
                     (11,12)) OR (date_year = 2017 AND date_month = 1) 
                     THEN Sales_amt END )

Here is a [DEMO]

Test setup:

WITH Sales AS(
    SELECT *
    FROM (VALUES ('2016','08',100),
                 ('2016','10',100), --Capture in 1st CASE
                 ('2016','11',100), --Capture in 1st CASE  --Capture in 2nd CASE
                 ('2016','12',100), --Capture in 1st CASE  --Capture in 2nd CASE
                 ('2017','1',100),  --Capture in 2nd CASE
                 ('2017','1',100),  --Capture in 2nd CASE
                 ('2017','1',100),  --Capture in 2nd CASE
                 ('2018','2',100)) T(date_year, date_month, sales_amt))

--Expect 300, 500
--Instead use OR
SELECT
  [2016_Oct_Dec]    = SUM(CASE WHEN date_year = 2016 AND date_month IN 
                     (10,11,12) THEN Sales_amt END )
, [2016/17_Nov_Jan] = SUM(CASE WHEN (date_year = 2016 AND date_month IN 
                     (11,12)) OR ((date_year = 2017 AND date_month = 1)) 
                     THEN Sales_amt END )
From Sales

Using multiple case conditions

2 Answers