CASE with 'and' condition not working
-
sorry for bring such a simple thing to this forum. for some reason the case statement with and condition is not working. I used adventureworks to simulate the problem since this is not the actual data.
1. I have an inner select statement that with product name with group by statements
2. I used a datediff to get the ELAPSED days between [SellStartDate] and [DATE_60_ELAPSE] check (this part is important to my question)
3. used a simple CTE to get the min date from my orginial SELECT statement in #1 and generated a COUNT of records
4.I want to put a CASE statement look at the ELAPSED days and COUNT i.e. if ELAPSED days is > 60 AND COUNT >=4 then 1 else 2. Problem is, it is showing 2 in results(see attached) even though the conditions was met and therefore it should show 1WITH CTE AS
(
SELECT PP.ProductID
,PP.Name
,PP.[SellStartDate]
,DATEADD(MONTH, 2, PP.[SellStartDate]) [DATE_60_ELAPSE]
,DATEDIFF(DAY, PP.[SellStartDate], DATEADD(MONTH, 2, PP.[SellStartDate])) as daysElapse
,COUNT(*) TOTAL_COUNT
FROM [Production].[Product] PP
LEFT JOIN [Production].[ProductInventory] PC WITH (NOLOCK) ON PP.ProductID = PC.ProductID
WHERE PP.ProductID = 316
GROUP BY PP.ProductID,
PP.Name ,
PP.[SellStartDate])
SELECT CTE.ProductID,
CTE.Name,
MIN(CTE.SellStartDate) SELLDATE,
MIN(CTE.DATE_60_ELAPSE) '60YDAYSCHECK',
MIN(CTE.daysElapse) DaysElapse,
COUNT(CTE.TOTAL_COUNT) TOTAL_CNT,
CASE WHEN CTE.daysElapse > 60 and cte.TOTAL_COUNT= 1 THEN '1'
ELSE '2'
END AS CODE
FROM CTE
GROUP BY CTE.ProductID,
CTE.Name,
CASE WHEN CTE.daysElapse > 60 and cte.TOTAL_COUNT=1 THEN '1'
ELSE '2'
END -
There may be an inconsistency between your text description any your SQL query: The text description has " if ELAPSED days is > 60 AND COUNT >=4 then 1 else 2". The posted SQL has "... WHEN CTE.daysElapse > 60 and cte.TOTAL_COUNT= 1 THEN '1'...". Note the difference between the count values checked.
Please check the raw data in your copy of AdventureWorks. In the (admittedly old) version I have installed (on a SQL 2012 server), there are three records that appear in the CTE. Since TOTAL_COUNT does not satisfy the "... and cte.TOTAL_COUNT=1 " condition, the result of the case in the full query should be 2. You can determine if this is the case by running the SELECT definition of your CTE and checking the value in the TOTAL_COUNT field. -
Not convinced AdventureWorks is a good representation of what your real situation is; In your CTE you are grouping by ProductId and then grouping again in your query, so you are getting a count of the already calculated total, which will always equal 1. Taking out the aggregations and grouping from your query gives better results and would also resolve the issue within the case statement of the CTE columns not being aggregated.
...
-
@ben.bizzell yes, i changed the query after writing text description. so instead of count >=4 it is count>=1. But regardless of the data set ,my question is why the CASE statement is not working with the CTE result.
@HAPPYGREEK
In your CTE you are grouping by ProductId and then grouping again in your query, so you are getting a count of the already calculated total, which will always equal 1.
That is correct because it is count of 3 rows because data is same for all rows, which I want to count as 1 in CTE. e.g if the inner query had 10 unique rows, I wanted the CTE to report 10 count. which is what I am getting.
The question is why CASE not accounting for the numbers in ELAPSE and COUNT? I have updated script and removed CASE if you would like to rewrite and include it.1. in my real world data, I have 10 unique rows in the inner query
2. and the count from the CTE gives me 10 which is what i want.
3. In the AW data I get a count of 3 because there are 3 non-unique records and therefore I get a count of 1 from CTE which is right.
4. so my CASE statement was to just look for those 2 conditions, date elapse total and count total.
i have updated the script to be a better representation of the real world dataWITH CTE AS
(
SELECT PP.ProductID
,PP.Name
,PP.[SellStartDate]
,DATEADD(MONTH, 2, PP.[SellStartDate]) SellsStartDate60daysCheck
,DATEDIFF(DAY, PP.[SellStartDate], DATEADD(MONTH, 2, PP.[SellStartDate])) as daysElapse
,COUNT(*) TOTAL_COUNT
FROM [Production].[Product] PP
LEFT JOIN [Production].[ProductInventory] PC WITH (NOLOCK) ON PP.ProductID = PC.ProductID
WHERE PP.ProductID = 316
GROUP BY PP.ProductID,
PP.Name ,
PP.[SellStartDate]
)
SELECT CTE.Name NAME,
CTE.ProductID PRODID,
MIN(CTE.[SellStartDate]) SellStartDate,
MIN(CTE.SellsStartDate60daysCheck) SellsStartDate60daysCheck,
MIN(CTE.daysElapse) DaysElapse,
COUNT(CTE.TOTAL_COUNT) COUNT_AMT
FROM CTE
GROUP BY
CTE.Name,
CTE.ProductID -
The most recent script posted no longer contains a CASE statement, so I cannot use it to address your question.
I have returned to your original posting to try to address the situation you are describing.
In that script, as originally posted in the CTE your script "... COUNT(*) TOTAL_COUNT ..." You then evaluate the TOTAL_COUNT value as part of the CASE statement in the full query:
"... CASE WHEN CTE.daysElapse > 60 and cte.TOTAL_COUNT = 1 THEN '1'..."
.
Since the original data has three records, the TOTAL_COUNT value in your original CTE will be 3. Three is neither "=1" or ">=4", so the return will never satisfy the conditional in the CASE.If you go back to the originally posted script, and modify the case statement so that the case portion is:
CASE WHEN CTE.daysElapse > 60 and cte.TOTAL_COUNT >= 2 THEN '1'
ELSE '2'
END AS CODE
the TOTAL_COUNT will be 3, which will satisfy the conditional in the CASE statement, and return the expected value.
-
WITH CTE AS
(SELECT PP.ProductID
,PP.Name
,PP.[SellStartDate]
,DATEADD(MONTH, 2, PP.[SellStartDate]) SellsStartDate60daysCheck
,DATEDIFF(DAY, PP.[SellStartDate], DATEADD(MONTH, 2, PP.[SellStartDate])) as daysElapse
,COUNT(*) TOTAL_COUNT
FROM [Production].[Product] PP
LEFT JOIN [Production].[ProductInventory] PC WITH (NOLOCK) ON PP.ProductID = PC.ProductID
--WHERE PP.ProductID = 316
GROUP BY PP.ProductID,
PP.Name ,
PP.[SellStartDate]
)
SELECT CTE.Name NAME,
CTE.ProductID PRODID,
MIN(CTE.[SellStartDate]) SellStartDate,
MIN(CTE.SellsStartDate60daysCheck) SellsStartDate60daysCheck,
MIN(CTE.daysElapse) DaysElapse,
COUNT(CTE.TOTAL_COUNT) COUNT_AMT,
CASE WHEN CTE.daysElapse > 60 and cte.TOTAL_COUNT >= 4 THEN '1'
ELSE '2'
END AS CODE
FROM CTE
GROUP BY CTE.Name, CTE.ProductID ,CTE.daysElapse, CTE.TOTAL_COUNT
...
-
@GREEK....your solution does not work due to what @benbizell stated, if i change it to =3 then the THEN statement works. so you are both right.
@benbizell
you said: Since the original data has three records, the TOTAL_COUNT value in your original CTE will be 3. Three is neither "=1" or ">=4", so the return will never satisfy the conditional in the CASE.
YOU ARE right!!!, even though the record in the inner query is showing this: the aggregated count "3" in CTE is still represent the 3 row/ records itself. So when I changed it to CASE WHEN CTE.daysElapse > 60 and cte.TOTAL_COUNT = 3 THEN '1'
it showed the right THEN statement.solution to my real word will be to aggregate in inner query count to 5 , as i am doing with CTE, and therefore not even need CTE at all, I can simply write the case statement against inner.
So i have e.g.
TOTAL_COUNT
--------------------
1
1
1
1
1
THANK YOU BOTH!!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply