February 29, 2008 at 12:24 am
Hi folks,
How can I use a CASE statement in a where condition?
My SQL code is like below:
SET ANSI_WARNINGS OFF
SET NOCOUNT ON
SELECT
d.StockCode,
SalesPrice1 = MAX(CASE WHEN PriceNum = 1 THEN d.SalesPrice ELSE NULL END),
Salesprice2 = MAX(CASE WHEN PriceNum = 2 THEN d.SalesPrice ELSE NULL END),
SalesPrice3 = MAX(CASE WHEN PriceNum = 3 THEN d.SalesPrice ELSE NULL END),
SalesPrice4 = MAX(CASE WHEN PriceNum = 4 THEN d.SalesPrice ELSE NULL END),
SalesPrice5 = MAX(CASE WHEN PriceNum = 5 THEN d.SalesPrice ELSE NULL END),
from
(
SELECTStockCode,
SalesPrice,
PriceNum = (SELECT COUNT(*) FROM dbo.tblSales s2
WHERE s2.StockCode = s1.StockCode
AND s2.SalesDate<=s1.SalesDate
and s2.SalesPrice=(case when s2.SalesPrice=s1.SalesPrice then NULL else s2.SalesPrice end)) FROM dbo.tblSales s1 )d
GROUP BY d.StockCode
ORDER BY d.StockCode
As you can see, I just want to eleminate duplicate values on a single row.The only criteria is their sales dates.My purpose is also list the distinct sales prices
Thank you very much
February 29, 2008 at 3:10 pm
Yes you can use case in a where clause. I think if you explain the desired results and post the table structures and dummy data, we can come up with a simpler solution.
Oh and unless you put ANSI NULLS OFF, Null is never equal to anything so you need to use Is Null or Is Not Null for Null comparisons.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply