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

--drop table #lines

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 update the table would look like this We divide CTN by OUTER, OUTER by INNER etc
ABC EACH 1
ABC INNER 12
ABC OUTER 12
ABC CTN 12
DEF EACH 1
DEF INNER 5
DEF OUTER 5
DEF CTN 5
*/
--I got this reply from visakh16 that worked perfectly.
/*
;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
*/
--Now I need to change this so that when OUTER is missing or any other we divide by the next value. Example divide CTN by INNER etc.

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

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

*/
/*
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
CROSS APPLY(SELECT TOP 1 Qty
FROM Sku_Order 
ON SKU = s1.SKU
AND Val < s1.Val
ORDER BY Val DESC
)s2

quote:

---

Originally posted by David_G


I have replaced ON with WHERE. seems to work. Is this correct?

---

UPDATE s1
SET    s1.Qty = s1.Qty / s2.Qty
FROM   Sku_Order s1
       CROSS APPLY(
    SELECT TOP 1 s3.Qty
    FROM   Sku_Order s3
    WHERE  s3.SKU = s1.SKU
           AND s3.Val < s1.Val
    ORDER BY
           s3.Val DESC
)s2