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

David_G
Starting Member

10 Posts

Posted - 2012-07-30 : 02:56:04

If I had a table like below how would I loop through and update the Qty column based on the ratio of the previous column with the same SKU

Create table #lines
(SKU varchar(50),
UOM varchar(35),
Qty int
)

Insert into #lines(SKU,UOM,Qty) values('ABC','EACH','1')
Insert into #lines(SKU,UOM,Qty) values('ABC','INNER','12')
Insert into #lines(SKU,UOM,Qty) values('ABC','OUTER','144')
Insert into #lines(SKU,UOM,Qty) values('ABC','CTN','1728')

Insert into #lines(SKU,UOM,Qty) values('DEF','EACH','1')
Insert into #lines(SKU,UOM,Qty) values('DEF','INNER','5')
Insert into #lines(SKU,UOM,Qty) values('DEF','OUTER','25')
Insert into #lines(SKU,UOM,Qty) values('DEF','CTN','125')

select * from #lines

ABC EACH 1
ABC INNER 12
ABC OUTER 144
ABC CTN 1728
DEF EACH 1
DEF INNER 5
DEF OUTER 25
DEF CTN 125
-- What I need to do is replace the Qty column value with the ratio of the following ...
-- CTN/OUTER into Qty column for SKU CARTON
-- OUTER/INNER into Qty Column for SKU OUTER
-- INNER/EACH into Qty Column for SKU INNER

-- After loooping or update the table would look like this
ABC EACH 1
ABC INNER 12
ABC OUTER 12
ABC CTN 12
DEF EACH 1
DEF INNER 5
DEF OUTER 5
DEF CTN 5

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 03:09:58

[code]
;With SKU_Order
AS
(
SELECT t.*,t1.Val
FROM table t
INNER JOIN (SELECT 'EACH' AS Cat,1 AS Val UNION ALL
SELECT 'INNER',2 UNION ALL
SELECT 'OUTER',3 UNION ALL
SELECT 'CTN',4
)t1
ON t1.Cat = t.UOM
)

SELECT s1.SKU,s1.UOM,s1.Qty/ISNULL(s2.Qty,1) AS Qty
FROM Sku_Order s1
LEFT JOIN Sku_Order s2
ON s2.SKU = s1.SKU
AND s2.Val = s1.Val-1

if you need to update main table replace above select with update

UPDATE s1
SET s1.Qty = s1.Qty/s2.Qty
FROM Sku_Order s1
INNER JOIN Sku_Order s2
ON s2.SKU = s1.SKU
AND s2.Val = s1.Val-1
[/code]

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

David_G
Starting Member

10 Posts

Posted - 2012-07-30 : 22:32:03

Thank you very much.
This worked well.

Regards,

David

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-30 : 23:49:39

welcome

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

David_G
Starting Member

10 Posts

Posted - 2012-09-08 : 21:10:11

Hello,
Thanks very much for your assistance. Everthing worked well but I now need to adjust this code to handle the fact that we may not always have one of the values for the division.

I have the select part working for testing but cant work out how to translate this into an update statement which is what I require.
Any assistance will be greatly appreciated.


SELECT t.*,t1.Val, 0 as back
into ##w
FROM #lines t
INNER JOIN (SELECT 'EACH' AS Cat,1 AS Val UNION ALL
            SELECT 'INNER',2 UNION ALL
            SELECT 'OUTER',3 UNION ALL
            SELECT 'CTN',4
            )t1
ON t1.Cat = t. UOM


select * from ##w
update ##w
  set back = isnull((select max(w1.Val) from ##w w1 inner join ##w w2 on (w1.SKU = w2.SKU)
             where w1.Val < ##w.Val and ##w.SKU =
w2.SKU),0)
-- I need to code below to update the QTY column
SELECT w1.SKU,
		w1.UOM,
		w1.QTY,
		cast(w1.QTY as decimal)/ISNULL(cast(w2.QTY as
decimal),1) AS RATIO
FROM ##w w1 
	LEFT JOIN ##w w2 
	ON (w2.SKU = w1.SKU and w1.back = w2.Val)
drop table ##w

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-09 : 11:56:06

in cases where one of quantity does not exist what should be the division result?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources