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 2008 Forums
 Transact-SQL (2008)
 Insert into new table with very specific list
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pvong
Yak Posting Veteran

52 Posts

Posted - 12/02/2013 :  23:17:40  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
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

52 Posts

Posted - 12/03/2013 :  15:02:12  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 12/04/2013 :  00:14:50  Show Profile  Reply with Quote

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



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

pvong
Yak Posting Veteran

52 Posts

Posted - 12/04/2013 :  11:11:59  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 12/05/2013 :  01:55:57  Show Profile  Reply with Quote
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

52 Posts

Posted - 12/05/2013 :  10:57:19  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 12/05/2013 :  13:21:18  Show Profile  Reply with Quote
welcome

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

pvong
Yak Posting Veteran

52 Posts

Posted - 12/06/2013 :  15:55:04  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 12/07/2013 :  08:41:24  Show Profile  Reply with Quote
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
  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.12 seconds. Powered By: Snitz Forums 2000