SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Min problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 03/05/2014 :  06:05:02  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/05/2014 :  07:47:37  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000