March 8, 2012 at 12:37 pm
I have copied sample code and the results i want to see. I have also copied the case statement.
I'm trying to get when numberofdates field is greater than i i need to use below condition. Can somebody please help?
CREATE TABLE #Final (Product int, RDate smalldatetime, Quantity int, OnHand int,NumberofDates int)
INSERT INTO #Final Values (1011,'2012/02/15',35,56,4)
INSERT INTO #Final Values (1011,'2012/01/11',31,56,4)
INSERT INTO #Final Values (1011,'2012/01/01',23,56,4)
INSERT INTO #Final Values (1011,'2011/12/26',104,56,4)
INSERT INTO #Final Values (2134,'2012/02/10',27,26,1)
SELECT
Product,RDate,Quantity,OnHand,NumberofDates
FROM #Final
--SELECT
--Product,RDate,Quantity,OnHand,NumberofDates
--FROM #Final
--WHERE CASE
-- WHEN NumberofDates >1 THEN (OnHand - Quantity) >=0
-- ELSE ' '
-- END
DROP TABLE #Final
Results I want to see:
ProductRDateQuantityOnHandNumberofDates
10112012-02-15 00:00:0035564
10112012-01-11 00:00:0031564
10112012-01-01 00:00:0023564
21342012-02-10 00:00:0027261
March 8, 2012 at 1:25 pm
It's not clear from your CASE statement what the intended result is.
The CASE statment isn't a Boolean expression, it's just an expression. The entire CASE ... END is like a column name--a single value. It can be used in a WHERE clause like so:
WHERE CASE WHEN booleanExpression THEN expression ELSE expression END = expression
Depending what you mean by ELSE '' END in the query, you may want one of these:
SELECT Product,RDate,Quantity,OnHand,NumberofDates
FROM #Final
WHERE NumberofDates > 1
AND (OnHand - Quantity) >=0
SELECT Product,RDate,Quantity,OnHand,NumberofDates
FROM #Final
WHERE ISNULL(NumberofDates, 0) <= 1
OR (OnHand - Quantity) >=0
March 8, 2012 at 1:31 pm
SELECT
Product,RDate,Quantity,OnHand,NumberofDates
FROM #Final
WHERE CASE
WHEN NumberofDates >1 THEN (OnHand - Quantity) >=0
ELSE ' '
END
Is exactly the same as scripting:
SELECT
Product,RDate,Quantity,OnHand,NumberofDates
FROM #Final
WHERE X
See, it doesn't make sense. You have to give an condition after the end. Maybe try to explain in words what you want to do with the where?
Jared
CE - Microsoft
March 8, 2012 at 1:56 pm
*/
Please execute the query and let me know whether what I understood was correct :).
You wanted to display record if the numberOFDays is 1 ( irrespective whether onHand<Quantity).
Also you wanted to display record when numberOfdays>1 and Onhand>Quantity.
And not to display when OnHand<Quantity and NumberofDats != 1.
------*/
CREATE TABLE #Final (Product int, RDate smalldatetime, Quantity int, OnHand int,NumberofDates int)
INSERT INTO #Final Values (1011,'2012/02/15',35,56,4)
INSERT INTO #Final Values (1011,'2012/01/11',31,56,4)
INSERT INTO #Final Values (1011,'2012/01/01',23,56,4)
INSERT INTO #Final Values (1011,'2011/12/26',104,56,4)
INSERT INTO #Final Values (2134,'2012/02/10',27,26,1)
SELECT
Product,RDate,Quantity,OnHand,NumberofDates
FROM #Final
SELECT
Product,RDate,Quantity,OnHand,NumberofDates
FROM
(
select
*,
case
when ( ((onhand-quantity)>=0 or numberofDates=1) or ( numberofDates>1 and (onhand-quantity)>=0 ))
then 'Valid'
else 'Invalid'
end as ConditionFlag
from #Final
)T where ConditionFlag = 'Valid'
DROP TABLE #Final
--Results
--Product RDate Quantity OnHand NumberofDates
--10112012-02-15 00:00:0035564
--10112012-01-11 00:00:0031564
--10112012-01-01 00:00:0023564
--21342012-02-10 00:00:0027261
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply