Author |
Topic |
pvong
Yak Posting Veteran
58 Posts |
Posted - 2013-12-02 : 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 TABLE1Fruit | Qty | CostApple | 400 | 3.75Orange | 500 | 7.90Apple | 200 | 1.45Orange | 100 | 5.65What 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 | CostApple | 200 | 1.45Apple | 300 | 3.75As 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 | CostOrange | 100 | 5.65Orange | 150 | 7.90Again, 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 R2Be kind to the newbies because you were once there. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-03 : 00:22:06
|
its possible something likeSELECT t.*FROM table tCROSS APPLY (SELECT SUM(Qty) AS Tot FROM table WHERE Fruit = t.Fruit AND Cost < t.Cost )t1WHERE @Qty BETWEEN Tot AND t.QtyAND Fruit = @Fruit WHERE @Qty is Qty you pass------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
pvong
Yak Posting Veteran
58 Posts |
Posted - 2013-12-03 : 15:02:12
|
When I tried you example:SELECT t.*FROM table1 tCROSS APPLY (SELECT SUM(Qty) AS Tot FROM table1 WHERE Fruit = t.Fruit AND Cost < t.Cost )t1WHERE 500 BETWEEN Tot AND t.QtyAND 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 R2Be kind to the newbies because you were once there. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-04 : 00:14:50
|
[code]declare @t table(Fruit varchar(20),Qty int,Cost decimal(5,2))insert @tvalues('Apple',400,3.75),('Orange',500 , 7.90),('Apple', 200 , 1.45),('Orange', 100 , 5.65)declare @Qty int=250--500declare @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 QtyFROM @t tOUTER APPLY (SELECT SUM(Qty) AS Tot FROM @t WHERE Fruit = t.Fruit AND Cost < t.Cost )t1WHERE (COALESCE(Tot,0) <= @Qty OR COALESCE(Tot,0) + t.Qty <=@Qty) and Fruit = @FruitFruit Qty-------------Orange 150Orange 100[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
pvong
Yak Posting Veteran
58 Posts |
Posted - 2013-12-04 : 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.costFROM Table1 as tOUTER APPLY (SELECT SUM(Qty) AS Tot FROM Table1 as t WHERE Fruit = t.Fruit AND Cost < t.Cost )t1WHERE (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 R2Be kind to the newbies because you were once there. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-05 : 01:55:57
|
Reason is you're using same alias for both table which is causing query engine to take wrong values for comparisonSELECT t.Fruit,CASE WHEN COALESCE(Tot,0) + t.Qty > 250 THEN 250 - COALESCE(Tot,0) else t.Qty end as Qty, t.costFROM Table1 as tOUTER APPLY (SELECT SUM(Qty) AS TotFROM Table1 as t WHERE Fruit = t.FruitAND Cost < t.Cost)t1WHERE (COALESCE(Tot,0) <= 250 OR COALESCE(Tot,0) + t.Qty <=250) andFruit = 'orange' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
pvong
Yak Posting Veteran
58 Posts |
Posted - 2013-12-05 : 10:57:19
|
This worked!!!! Thanks so much!!!!!!------------------------------Using VS2010 / Learning in VB.Net / Win2008 R2 / SQL 2008 R2Be kind to the newbies because you were once there. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-05 : 13:21:18
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
pvong
Yak Posting Veteran
58 Posts |
Posted - 2013-12-06 : 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 SharesFrom MutualFundsReflowBankRAW as tOUTER APPLY (SELECT SUM(Shares) as TotFrom MutualFundsReflowBankRAWWhere Ticker = t.TickerAND CostPerShare < t.CostPershare)t1WHERE (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 10Incorrect 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 R2Be kind to the newbies because you were once there. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-07 : 08:41:24
|
you missed a ) after the second coalesceiewhere should be like below...WHERE (COALESCE(Tot,0) <= 5000 or COALESCE(Tot,0) + t.Shares <=5000) and Ticker = 'ADT' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|