Author 
Topic 

pvong
Yak Posting Veteran
58 Posts 
Posted  12/02/2013 : 23:17:40

Thanks for considering to help!
I have a feeling this is a hard one and maybe even impossible but here it is...
I have TABLE1
Fruit  Qty  Cost Apple  400  3.75 Orange  500  7.90 Apple  200  1.45 Orange  100  5.65
What I want is an INSERT where in my query parameter I will pick a fruit and a QTY and it wil give me the exact QTY broken down by lot and by LOWEST COST first. The resulting table (TABLE2) should look like this if I were to pick Apple and 500 as the QTY.
Fruit  Qty  Cost Apple  200  1.45 Apple  300  3.75
As you can see, I get my exact 500 apples and it's in lowest cost first. AND it knows to only bring 300 out of the 400 because I only need 300 to get to my 500.
Another example is if I were to pick Oranges and 250 for QTY, this is what I should get for TABLE 2.
Fruit  Qty  Cost Orange  100  5.65 Orange  150  7.90
Again, it knows I only need 150 out of the 500 to complete my 250.
Is this even possible in some kind of a query?
 Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2 Be kind to the newbies because you were once there. 

visakh16
Very Important crosS Applying yaK Herder
India
52325 Posts 
Posted  12/03/2013 : 00:22:06

its possible something like
SELECT t.*
FROM table t
CROSS APPLY (SELECT SUM(Qty) AS Tot
FROM table
WHERE Fruit = t.Fruit
AND Cost < t.Cost
)t1
WHERE @Qty BETWEEN Tot AND t.Qty
AND Fruit = @Fruit
WHERE @Qty is Qty you pass
 SQL Server MVP http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs



pvong
Yak Posting Veteran
58 Posts 
Posted  12/03/2013 : 15:02:12

When I tried you example:
SELECT t.* FROM table1 t CROSS APPLY (SELECT SUM(Qty) AS Tot FROM table1 WHERE Fruit = t.Fruit AND Cost < t.Cost )t1 WHERE 500 BETWEEN Tot AND t.Qty AND Fruit = 'Apple'
PLEASE NOTE I changed table to table1 and actually used the values of 500 and apples and nothing came up? I didn't use parameters because I was testing this in SMS. I just get my headers but no data.
I really did create a test table in my DB called Table1 with the exact data as this post.
Can someone please help?
 Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2 Be kind to the newbies because you were once there. 


visakh16
Very Important crosS Applying yaK Herder
India
52325 Posts 
Posted  12/04/2013 : 00:14:50

declare @t table
(
Fruit varchar(20),
Qty int,
Cost decimal(5,2)
)
insert @t
values('Apple',400,3.75),
('Orange',500 , 7.90),
('Apple', 200 , 1.45),
('Orange', 100 , 5.65)
declare @Qty int=250500
declare @fruit varchar(10)='Orange''Apple'
SELECT t.Fruit,
CASE WHEN COALESCE(Tot,0) + t.Qty > @Qty THEN @Qty  COALESCE(Tot,0) else t.Qty end as Qty
FROM @t t
OUTER APPLY (SELECT SUM(Qty) AS Tot
FROM @t
WHERE Fruit = t.Fruit
AND Cost < t.Cost
)t1
WHERE (COALESCE(Tot,0) <= @Qty OR COALESCE(Tot,0) + t.Qty <=@Qty) and
Fruit = @Fruit
Fruit Qty

Orange 150
Orange 100
 SQL Server MVP http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs



pvong
Yak Posting Veteran
58 Posts 
Posted  12/04/2013 : 11:11:59

I know we are very very close and I know what's missing is something simple.
Based on what you had, I used this Select statement to try and get Orange at 250 QTY for my Table1.
SELECT t.Fruit, CASE WHEN COALESCE(Tot,0) + t.Qty > 250 THEN 250  COALESCE(Tot,0) else t.Qty end as Qty, t.cost FROM Table1 as t OUTER APPLY (SELECT SUM(Qty) AS Tot FROM Table1 as t WHERE Fruit = t.Fruit AND Cost < t.Cost )t1 WHERE (COALESCE(Tot,0) <= 250 OR COALESCE(Tot,0) + t.Qty <=250) and Fruit = 'orange'
 As you can see from the results below, I get the 100 but then I get 250 instead of 150. Why? What am I missing?
 Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2 Be kind to the newbies because you were once there. 


visakh16
Very Important crosS Applying yaK Herder
India
52325 Posts 
Posted  12/05/2013 : 01:55:57

Reason is you're using same alias for both table which is causing query engine to take wrong values for comparison
SELECT t.Fruit,
CASE WHEN COALESCE(Tot,0) + t.Qty > 250 THEN 250  COALESCE(Tot,0) else t.Qty end as Qty, t.cost
FROM Table1 as t
OUTER APPLY (SELECT SUM(Qty) AS Tot
FROM Table1 as t
WHERE Fruit = t.Fruit
AND Cost < t.Cost
)t1
WHERE (COALESCE(Tot,0) <= 250 OR COALESCE(Tot,0) + t.Qty <=250) and
Fruit = 'orange'
 SQL Server MVP http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs



pvong
Yak Posting Veteran
58 Posts 
Posted  12/05/2013 : 10:57:19

This worked!!!! Thanks so much!!!!!!
 Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2 Be kind to the newbies because you were once there. 


visakh16
Very Important crosS Applying yaK Herder
India
52325 Posts 

pvong
Yak Posting Veteran
58 Posts 
Posted  12/06/2013 : 15:55:04

Can you help me one last time? I tried to apply what you showed me to real world data and it should work but it's not.
Below shows my select showing the data I'm working with and then my New Select statement based on what you helped me with earlier.
Here is the statement: SELECT t.Ticker, CASE WHEN COALESCE(Tot,0) + t.Shares > 5000 THEN 5000  COALESCE(Tot,0) else t.Shares end as Shares From MutualFundsReflowBankRAW as t OUTER APPLY (SELECT SUM(Shares) as Tot From MutualFundsReflowBankRAW Where Ticker = t.Ticker AND CostPerShare < t.CostPershare )t1 WHERE (COALESCE(Tot,0) <= 5000 or COALESCE(Tot,0) + t.Shares <=5000 and Ticker = 'ADT'
************************
The error message I keep getting is:
Msg 102, Level 15, State 1, Line 10 Incorrect syntax near 'ADT'.
******************
ADT is there. Why will it not accept this when it worked with my orange example?
 Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2 Be kind to the newbies because you were once there. 


visakh16
Very Important crosS Applying yaK Herder
India
52325 Posts 
Posted  12/07/2013 : 08:41:24

you missed a ) after the second coalesce
ie where should be like below
...
WHERE (COALESCE(Tot,0) <= 5000 or COALESCE(Tot,0) + t.Shares <=5000) and
Ticker = 'ADT'
 SQL Server MVP http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs




Topic 
