Nested CASE query issue

Question

Post reply

Nested CASE query issue

ss_nl

Say Hey Kid

Points: 666
More actions
July 15, 2010 at 6:54 am

#387360

By using the query below I'm getting 3 kinds of productStatus results:
"Upgrade_Year", "NULL" or "Upgrade_Basic".
Why always NULL in stead of "Upgrade_3Months" ??
This should be the logic:
----
If the latest ActualDate is less then 3 months ago and product in (61,63) then 'Upgrade_3Months'
If the latest ActualDate is less then 1 year ago and product in (62,64) then 'Upgrade_Year'
Else 'Upgrade_Basic'
----
But can't seem to find the right CASE setup...
SELECT
fo.objectId
,MAX(dd.ActualDate)
,fo.Product
,CASE WHEN DATEADD (year, 1, MAX(dd.ActualDate)) >= GETDATE() THEN
(CASE WHEN fo.Product IN (62,64) THEN 'Upgrade_Year'
END)
WHEN DATEADD (month, 3, MAX(dd.ActualDate)) >= GETDATE() THEN
(CASE WHEN fo.Product IN (61,63) THEN 'Upgrade_3Months'
END)
ELSE 'Upgrade_Basic'
END AS 'productStatus'
FROM fo
INNER JOIN DimDate dd ON fo.Date = dd.DateKey
WHERE fo.Product IN (61,62,63,64)
GROUP BY fo.objectId, fo.Product
ORDER BY fo.objectId

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

CASE shortcircuits for the first expression that matches.

Try inverting the two expressions:

SELECT

fo.objectId

,MAX(dd.ActualDate)

,fo.Product

,CASE

WHEN DATEADD (month, 3, MAX(dd.ActualDate)) >= GETDATE() THEN

CASE WHEN fo.Product IN (61,63) THEN 'Upgrade_3Months' END

WHEN DATEADD (year, 1, MAX(dd.ActualDate)) >= GETDATE() THEN

CASE WHEN fo.Product IN (62,64) THEN 'Upgrade_Year' END

ELSE 'Upgrade_Basic'

END AS 'productStatus'

FROM fo

INNER JOIN DimDate dd ON fo.Date = dd.DateKey

WHERE fo.Product IN (61,62,63,64)

GROUP BY fo.objectId, fo.Product

ORDER BY fo.objectId

Edited: fixed indentation

-- Gianluca Sartori

ss_nl Say Hey Kid Points: 666 More actions · Answer 2

Thanks, but then I'm getting

NULL, Upgrade_3Months or Upgrade_Basic

in the results....so NULL in stead of Upgrade_Year this time

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

My bad. I ran into the same mistake...

SELECT

fo.objectId

,MAX(dd.ActualDate)

,fo.Product

,CASE

WHEN DATEADD (month, 3, MAX(dd.ActualDate)) >= GETDATE() AND DATEADD (year, 1, MAX(dd.ActualDate)) < GETDATE() THEN

CASE WHEN fo.Product IN (61,63) THEN 'Upgrade_3Months' END

WHEN DATEADD (year, 1, MAX(dd.ActualDate)) >= GETDATE() THEN

CASE WHEN fo.Product IN (62,64) THEN 'Upgrade_Year' END

ELSE 'Upgrade_Basic'

END AS 'productStatus'

FROM fo

INNER JOIN DimDate dd ON fo.Date = dd.DateKey

WHERE fo.Product IN (61,62,63,64)

GROUP BY fo.objectId, fo.Product

ORDER BY fo.objectId

-- Gianluca Sartori

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

,CASE

WHEN DATEADD (year, 1, MAX(dd.ActualDate)) >= GETDATE() AND fo.Product IN (62,64) THEN 'Upgrade_Year'

WHEN DATEADD (month, 3, MAX(dd.ActualDate)) >= GETDATE() AND fo.Product IN (61,63) THEN 'Upgrade_3Months'

ELSE 'Upgrade_Basic'

END AS 'productStatus'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ss_nl Say Hey Kid Points: 666 More actions · Answer 5

Gianluca Sartori (7/15/2010)
My bad. I ran into the same mistake...
...

Thanks, and sorry for the late reply.

This query however is also not giving the desired results....

Now I'm getting either 'Upgrade_3Months', 'Upgrade_Year' or NULL values....so no Upgrade_Basic.

ss_nl Say Hey Kid Points: 666 More actions · Answer 6

Chris Morris-439714 (7/15/2010)
,CASE
WHEN DATEADD (year, 1, MAX(dd.ActualDate)) >= GETDATE() AND fo.Product IN (62,64) THEN 'Upgrade_Year'
WHEN DATEADD (month, 3, MAX(dd.ActualDate)) >= GETDATE() AND fo.Product IN (61,63) THEN 'Upgrade_3Months'
ELSE 'Upgrade_Basic'
END AS 'productStatus'

Close, but now I'm getting double records for some objects, where the productStatus is either Upgrade_Year or Upgrade_Basic....

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

ss_nl (7/23/2010)
Chris Morris-439714 (7/15/2010)
,CASE
WHEN DATEADD (year, 1, MAX(dd.ActualDate)) >= GETDATE() AND fo.Product IN (62,64) THEN 'Upgrade_Year'
WHEN DATEADD (month, 3, MAX(dd.ActualDate)) >= GETDATE() AND fo.Product IN (61,63) THEN 'Upgrade_3Months'
ELSE 'Upgrade_Basic'
END AS 'productStatus'
Close, but now I'm getting double records for some objects, where the productStatus is either Upgrade_Year or Upgrade_Basic....

That's your FROM list, or GROUP BY.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ss_nl Say Hey Kid Points: 666 More actions · Answer 8

To make life a bit easier I made a workaround using two columns:

First column is telling whether or not it's a A)3months/year upgrade or just the b) basic one,

second column tells me if it's a 3 months or year upgrade in case A.

Thanks however for your suggestions!!

Nevyn SSChampion Points: 14210 More actions · Answer 9

ss_nl (7/15/2010)
By using the query below I'm getting 3 kinds of productStatus results:
"Upgrade_Year", "NULL" or "Upgrade_Basic".
Why always NULL in stead of "Upgrade_3Months" ??
This should be the logic:
----
If the latest ActualDate is less then 3 months ago and product in (61,63) then 'Upgrade_3Months'
If the latest ActualDate is less then 1 year ago and product in (62,64) then 'Upgrade_Year'
Else 'Upgrade_Basic'
----
But can't seem to find the right CASE setup...
SELECT
fo.objectId
,MAX(dd.ActualDate)
,fo.Product
,CASE WHEN DATEADD (year, 1, MAX(dd.ActualDate)) >= GETDATE() THEN
(CASE WHEN fo.Product IN (62,64) THEN 'Upgrade_Year'
END)
WHEN DATEADD (month, 3, MAX(dd.ActualDate)) >= GETDATE() THEN
(CASE WHEN fo.Product IN (61,63) THEN 'Upgrade_3Months'
END)
ELSE 'Upgrade_Basic'
END AS 'productStatus'
FROM fo
INNER JOIN DimDate dd ON fo.Date = dd.DateKey
WHERE fo.Product IN (61,62,63,64)
GROUP BY fo.objectId, fo.Product
ORDER BY fo.objectId

The problem with this query is that when a record matches an OUTER case condition it passes to the inner case. So for the above every record where DATEADD (year, 1, MAX(dd.ActualDate)) >= GETDATE() goes into your inner case. and since there is no else on the inner case, it can only return "upgrade_year" or null.

That should give you an idea what's going on. If you nest your case statements here, you need to handle all possible situations to get them to behave properly. But as Chris' code showed, you dont need to nest at all, you can just use 'AND'.

If you want to nest statements just for the sake of doing it, the best way is to make product the outer case (with no else on the outer, its not reachable), and then date on the inner cases, and give each inner case an else 'Upgrade_Basic'. But Chris's way is better, and there is no way it would cause duplicate records.

ss_nl Say Hey Kid Points: 666 More actions · Answer 10

The problem with this query is that when a record matches an OUTER case condition it passes to the inner case. So for the above every record where DATEADD (year, 1, MAX(dd.ActualDate)) >= GETDATE() goes into your inner case. and since there is no else on the inner case, it can only return "upgrade_year" or null.
That should give you an idea what's going on. If you nest your case statements here, you need to handle all possible situations to get them to behave properly. But as Chris' code showed, you dont need to nest at all, you can just use 'AND'.
If you want to nest statements just for the sake of doing it, the best way is to make product the outer case (with no else on the outer, its not reachable), and then date on the inner cases, and give each inner case an else 'Upgrade_Basic'. But Chris's way is better, and there is no way it would cause duplicate records.

Ok, thanks for this explanation! Helps me understand how it works and how to do it the next time..