joe
joe

Reputation: 1473

CASE expression in SQL Server

I have three tables w some records below. I was expecting to return on two distinct Keys with a 'BERRY' in Group field. However, for example the KEYs = 179526189 shows BERRY and COCA instead of just BERRY.

Table A

AGE AGE_IND     KEYS
34  (null)      179526189
6   N           179526390

Table B
ID          STATUS      FK_KEY
16478           (null)  179526189
16478           (null)  179526390
16479           (null)  179526189
16479           (null)  179526390

Table C

FK_ID   PRO_CODE    DEL_DT
16478   Drug        6/10/2014 12:00:00 AM
16479   Drug        (null)  



SELECT KEYS,
CASE WHEN (AGE < 18 AND AGE_IND = 'Y') AND (B.STATUS != 'S' or B.STATUS is null) THEN 'APPLE'
WHEN C.PRO_CODE = 'Drug' THEN 'BERRY'
WHEN AGE >= 18 THEN 'COCA'
ELSE 'APPLE' END as Groups,
COUNT(DISTINCT KEYS) AS CT
FROM #tableA A 
LEFT OUTER JOIN #tableB B
ON (A.KEYS = B.FK_KEY AND (B.STATUS <>'S' or B.STATUS IS NULL))
LEFT OUTER JOIN #tableC C
ON (B.ID = C.FK_ID AND C.DEL_DT IS NULL)
GROUP BY KEYS,
CASE WHEN (AGE < 18 AND AGE_IND = 'Y') AND (B.STATUS != 'S' or B.STATUS is null) THEN 'APPLE'
WHEN C.PRO_CODE = 'Drug' THEN 'BERRY'
WHEN AGE >= 18 THEN 'COCA'
ELSE 'APPLE' END

running the above query, yields

KEYS        Groups  CT
179526189   BERRY   1
179526189   COCA    1
179526390   APPLE   1
179526390   BERRY   1

Would you please tell me what is wrong with my CASE expression? so that my expected result yields:

KEYS        Groups  CT
179526189   BERRY   1
179526390   BERRY   1

Upvotes: 1

Views: 111

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You are grouping by GROUPS so you get each one. Choose one! It is not clear how you are choosing 'BERRY', but if you want one row per KEYS, then the idea is to only include that in the GROUP BY:

SELECT KEYS,
       MAX(CASE WHEN (AGE < 18 AND AGE_IND = 'Y') AND (B.STATUS != 'S' or B.STATUS is null) THEN 'APPLE'
                WHEN C.PRO_CODE = 'FMU' THEN 'BERRY'
                WHEN AGE >= 18 THEN 'COCA'
                ELSE 'APPLE'
           END) as Groups,
          COUNT(DISTINCT KEYS) AS CT
FROM #tableA A LEFT OUTER JOIN
     #tableB B
     ON A.KEYS = B.FK_KEY AND (B.STATUS <>'S' or B.STATUS IS NULL) LEFT OUTER JOIN 
     #tableC C
     ON B.ID = C.FK_ID AND C.DEL_DT IS NULL
GROUP BY KEYS;

Upvotes: 1

Related Questions