selecting with case - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

selecting with case

Author

Topic

madtown_nihs
Starting Member

7 Posts

Posted - 2010-03-16 : 12:07:13

I have the following query. It currently returns 4 values, min/max sale, min/max price. Right now, it just straight pick min and maxs through the values. I, however, need it to consider sale = 0 if price = sale.

so if a sale = price, i need the min max function to consider it 0 and min max accordingly.

Thanks So Much
Ludwig

SELECT
MIN(c.Sale) MinPrice, MAX(c.Sale) MaxPrice
MIN(c.Sale_Special) MinSale, MAX(c.Sale_Special) MaxSale
FROM Product a " &_
LEFT JOIN Accessory_Map b " &_
ON a.Product_ID = b.Product_ID " &_
AND b.Accessory_Group_ID IN (3, 4) " &_
LEFT JOIN Price c " &_
ON b.Accessory_ID = c.Product_ID " &_
LEFT join product d on d.Product_ID = b.Accessory_ID "
WHERE a.Product_ID = " & rsProd("Product_ID") & " " &_
AND d.Orderable = 0 AND d.Deleted = 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 12:10:32

[code]SELECT
MIN(c.Sale) MinPrice, MAX(c.Sale) MaxPrice
COALESCE(NULLIF(MIN(c.Sale_Special),MIN(c.Sale)),0) MinSale, COALESCE(NULLIF(MAX(c.Sale_Special),MAX(c.Sale)),0) MaxSale
FROM Product a " &_
LEFT JOIN Accessory_Map b " &_
ON a.Product_ID = b.Product_ID " &_
AND b.Accessory_Group_ID IN (3, 4) " &_
LEFT JOIN Price c " &_
ON b.Accessory_ID = c.Product_ID " &_
LEFT join product d on d.Product_ID = b.Accessory_ID "
WHERE a.Product_ID = " & rsProd("Product_ID") & " " &_
AND d.Orderable = 0 AND d.Deleted = 0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madtown_nihs
Starting Member

7 Posts

Posted - 2010-03-16 : 12:26:19

Doesn't this just select MIN(c.Sale_Special) first if it's not null? same for max.

Lets say there is this data set

Sale Sale Special
10 8
40 40
90 0

sale 10 - 90
SS 0 - 8

because at 40 s = ss, I consider it 0

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 12:29:51

[code]SELECT
MIN(c.Sale) MinPrice, MAX(c.Sale) MaxPrice
MIN(CASE WHEN c.Sale_Special = c.Sale THEN 0 ELSE c.Sale_Special END) MinSale, MAX(CASE WHEN c.Sale_Special = c.Sale THEN 0 ELSE c.Sale_Special END) MaxSale
FROM Product a " &_
LEFT JOIN Accessory_Map b " &_
ON a.Product_ID = b.Product_ID " &_
AND b.Accessory_Group_ID IN (3, 4) " &_
LEFT JOIN Price c " &_
ON b.Accessory_ID = c.Product_ID " &_
LEFT join product d on d.Product_ID = b.Accessory_ID "
WHERE a.Product_ID = " & rsProd("Product_ID") & " " &_
AND d.Orderable = 0 AND d.Deleted = 0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources