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.
| 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 |
 |
|
|
|
|
|
|
|
Min problem