Loop through table? - SQL Server Forums

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)

Loop through table?

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2012-09-11 : 00:26:19

I need to buy from customers until I reach the limit of 250.000. So from the first 2 customers I can buy all the amount. Customers 1 and 2 will have 0 left but customer 3 will have 23 left has could only buy 27 units from him (if i will buy everything the total will exceed 250.000. The other customers will be left with all their amounts has 250 has been exceeded with customer 3 and therefore cant buy any of their stock/amount.

I will I go about updating AmountLeftWith column (hardcoded in the example)? Thanks



--===== If the test table already exists, drop it 
IF OBJECT_ID('TempDB..#tmpCustomerAmount','U') IS NOT NULL 
DROP TABLE #tmpCustomerAmount



CREATE TABLE #tmpCustomerAmount (
[id] [int] IDENTITY(1,1) NOT NULL,

AmountSold [decimal](13, 2) NULL,
AmountLeftWith [decimal](13, 2) NULL


)

SET IDENTITY_INSERT #tmpCustomerAmount ON
iNSERT INTO #tmpCustomerAmount (ID, AmountSold,AmountLeftWith ) 
SELECT '1','123.000',0 UNION ALL
SELECT '2','100.000',0 UNION ALL
SELECT '3','50.000',23.000 UNION ALL
SELECT '4','700.00' ,700.00 UNION ALL 
SELECT '5','300.00' , 300.00


--===== Set the identity insert back to normal 
SET IDENTITY_INSERT #tmpCustomerAmount OFF

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 00:45:36

[code]
DECLARE @Amt int

SET @Amt = 250

SELECT t.ID,
t.AmountSold,
Case When Amt < @Amt then 0
when PrevAmt < @Amt AND Amt >= @Amt then Amt - @Amt
else AmountSold end AS AmountLeftWith
FROM Table t
CROSS APPLY (SELECT SUM(AmountSold) AS Amt,
SUM(CASE WHEN ID < t.ID THEN AmountSold ELSE 0 END) AS PrevAmt
FROM table
WHERE ID <= t.ID
)r
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2012-09-11 : 01:19:08

Thanks. When I run the query I get this error:
Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-09-11 : 01:41:24

Do you get the same error if you move the CROSS APPLY to derived tables?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2012-09-11 : 01:49:56

Not sure what you mean

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2012-09-11 : 03:38:43

If you mean something like this then yes, same error.

select   Amount 
	,(SELECT  
            SUM(CASE WHEN ID < o.ID THEN Amount ELSE 0 END) AS PrevAmt
             FROM #tmpCustomerAmount a
          WHERE ID <= O.ID)t
	 
from #tmpCustomerAmount O

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-09-11 : 09:07:26

You're trying to mix inner and outer references in aggregated expressions. A potential workaround is to create a JOIN such as :
select AmountSold
,(SELECT
SUM(CASE WHEN a.ID < o2.ID THEN a.AmountSold ELSE 0 END) AS PrevAmt
FROM #tmpCustomerAmount a
JOIN #tmpCustomerAmount AS O2 ON a.ID = O.ID
WHERE a.ID <= O.ID)t

from #tmpCustomerAmount O

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 11:19:06

quote:
Originally posted by collie

Thanks. When I run the query I get this error:
Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.


DECLARE @Amt int

SET @Amt = 250

SELECT t.ID,
t.AmountSold,
Case When Amt < @Amt then 0 
when COALESCE(PrevAmt,0) < @Amt AND Amt >= @Amt  then Amt - @Amt
else AmountSold end AS AmountLeftWith 
FROM Table t
CROSS APPLY (SELECT SUM(AmountSold) AS Amt
             FROM table
             WHERE ID <= t.ID
            )r
OUTER APPLY (SELECT SUM(AmountSold) AS PrevAmt
             FROM table
             WHERE ID < t.ID
            )r1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2012-09-11 : 15:05:54

Thanks to all that replied. Visakh16 works great :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 15:26:47

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2012-09-19 : 03:30:41

How do I accomplish the same but now lets say i want to buy only 100 units from each customer until 250.000 is bought? If customer 1 has 400.00 units then I can buy 300 units from him .
customer 2 has 600 units so I can buy 500. Do I need a cursor?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-19 : 12:07:46

quote:
Originally posted by collie

How do I accomplish the same but now lets say i want to buy only 100 units from each customer until 250.000 is bought? If customer 1 has 400.00 units then I can buy 300 units from him .
customer 2 has 600 units so I can buy 500. Do I need a cursor?

Thanks

same logic above with only difference being instead of AmountSold you take unitsbought from customer

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources