Min problem - 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 2012 Forums

Transact-SQL (2012)

Min problem

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2014-03-05 : 06:05:02

Hi All,

i got this query:

SELECT TOP (100) PERCENT dbo.T_Product_Option.ProductID, (CASE WHEN SUM(T_Product_Option.stock) > 0 THEN 1 ELSE 0 END) AS stock,

MIN(dbo.V_Product_option_stock.Price) AS Price,

dbo.V_Product_option_stock.oldprice
FROM dbo.T_Product_Option INNER JOIN
dbo.V_Product_option_stock ON dbo.T_Product_Option.id = dbo.V_Product_option_stock.id
GROUP BY dbo.T_Product_Option.ProductID, dbo.V_Product_option_stock.Price, dbo.V_Product_option_stock.oldprice

the problem is that it chooses the min value of product option, even if that product is out of stock! :-(

how can i add something like:

MIN(Select dbo.V_Product_option_stock.Price from dbo.V_Product_option_stock where stock=1) AS Price,

problem is some product have 1 option other more then one.
to check if all product options have stock 0 i do this
(CASE WHEN SUM(T_Product_Option.stock) > 0 THEN 1 ELSE 0 END) AS stock

This case product is soldout
but Problem case is when i got this

productid,optionid,price,stock
100,22,10,95,0
100,23,15,00,1
100,24,17,95,1
100,25,18,95,1

at the moment it returns 10,95 which not on stock, i want to return 15,00

Any help please

Thanks a lot

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-05 : 07:47:37

the suggested output shows the following fields: productid,optionid,price,stock
which are not same and in same sequence as illustrated in the given query ???

Though I don't fully understand. Also, I suspect that you might not need to group by Price field .. "dbo.V_Product_option_stock.Price". How if you provide some sample data and the desired output you want. We can help by providing a query then you can use/transform it as per your needs.

by the way .. how about about the following ...

SELECT TOP (100) PERCENT
dbo.T_Product_Option.ProductID
,dbo.V_Product_option_stock.oldprice
,SUM(1) AS stock
,MIN(dbo.V_Product_option_stock.Price) AS Price
FROM dbo.T_Product_Option
INNER JOIN dbo.V_Product_option_stock ON dbo.T_Product_Option.id = dbo.V_Product_option_stock.id
GROUP BY dbo.T_Product_Option.ProductID
,dbo.V_Product_option_stock.oldprice
Having SUM(T_Product_Option.stock) > 0

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources