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