-
T-SQL Case Statement in Where Clause?
I have the following query:
SELECT DISTINCT
p.ProductID,
p.ProductName,
p.ProductDesc,
p.DefaultColor,
p.SaleID,
v.VendorName,
(SELECT MIN(Price) FROM tblInventory WHERE ProductID = p.ProductID AND Active = 1) AS MinPrice,
(SELECT MAX(Price) FROM tblInventory WHERE ProductID = p.ProductID AND Active = 1) AS MaxPrice,
(SELECT MIN(SalePrice) FROM tblInventory WHERE ProductID = p.ProductID AND Active = 1) AS MinSalePrice,
(SELECT MAX(SalePrice) FROM tblInventory WHERE ProductID = p.ProductID AND Active = 1) AS MaxSalePrice,
s.StartDate,
s.EndDate,
pcj.SortOrder,
i.SalePrice
FROM
tblProducts p,
tblInventory i,
tblVendors v,
tblProductCategoryJunc pcj,
tblSales s
WHERE
p.ProductID = i.ProductID
AND p.VendorID = v.VendorID
AND p.ProductID = pcj.ProductID
AND pcj.CategoryID NOT IN (24,25,26)
AND i.Price IS NOT NULL
AND i.Price <> 0
AND p.Active = 1
AND i.Active = 1
AND pcj.CategoryID = 1
AND p.SaleID *= s.SaleID
I want to add the following logic to the query somehow: (I was trying to use a CASE statement in the WHERE clause, but, I'm not sure if that is actually the best way to do it. what I wrote didn't work quite right for me)
If p.SaleID > 0 AND s.StartDate IS NOT NULL AND s.EndDate IS NOT NULL AND GETDATE() >= s.StartDate AND GETDATE <= s.EndDate then I want to add into the WHERE Clause the following: AND i.SalePrice >= @StartPrice AND i.SalePrice <= @END Price
Otherwise (ELSE) I want to add the following to the WHERE clause: AND i.Price >= @StartPrice AND i.Price <= @EndPrice
Your help would be GREATLY appreciated. Thanks in advance
SC
Similar Threads
-
By dmiller23462 in forum VB Classic
Replies: 4
Last Post: 05-06-2006, 01:51 AM
-
By DH in forum VB Classic
Replies: 5
Last Post: 05-10-2002, 11:59 AM
-
By Dean Earley in forum VB Classic
Replies: 3
Last Post: 05-03-2002, 08:50 AM
-
By Ian B in forum Database
Replies: 0
Last Post: 05-13-2001, 10:36 PM
-
By Devaraj in forum Enterprise
Replies: 0
Last Post: 05-11-2000, 12:48 PM
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Development Centers
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center
|