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 2008 Forums
 Transact-SQL (2008)
 Insert into new table with very specific list

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 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

52326 Posts

Posted - 2013-12-03 : 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
Go to Top of Page

pvong
Yak Posting Veteran

58 Posts

Posted - 2013-12-03 : 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.
Go to Top of Page

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 @t
values('Apple',400,3.75),
('Orange',500 , 7.90),
('Apple', 200 , 1.45),
('Orange', 100 , 5.65)
declare @Qty int=250--500
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

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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.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.
Go to Top of Page

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 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
Go to Top of Page

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 R2
Be kind to the newbies because you were once there.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-05 : 13:21:18
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

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 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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-07 : 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
Go to Top of Page
   

- Advertisement -