In sql split row1 by row, row2 by row3 ...... and keep the output third column
I have this table. Now I need to do division on the amt column and update the int CalcAmt data
month amt CalcAmt
JAN 10000 NULL
FEB 20000 NULL
MAR 30000 NULL
APR 40000 NULL
eg: (FEB / JAN) then save the output to CalcAmt Feb, (MAR / FEB) then save the output to CalcAmt of MAR,
Expected Result:
month amt CalcAmt
JAN 10000 0
FEB 20000 2
MAR 30000 1.5
APR 40000 1.33
source to share
This can be easily done with LEAD/LAG
. Since you are using Sql Server 2008
, you cannot use the function LEAD/LAG
. Instead, use can generate a row number for each row and join one table with row number + 1 to get the data of the previous row.
Note. You need to add the remaining months
order in case
if you have one.
As a side note, you shouldn't use reserved keywords as the ex object name : MONTH
;WITH cte
AS (SELECT Rn=CASE [month]
WHEN 'Jan' THEN 1
WHEN 'feb' THEN 2
WHEN 'mar' THEN 3
ELSE 4
END,
*
FROM YOURTABLE)
SELECT A.month,
A.amt,
CalcAmt = A.amt / NULLIF(b.amt ,0)
FROM cte A
LEFT OUTER JOIN cte B
ON A.rn = b.rn + 1
If you want to update the table use this.
;WITH cte
AS (SELECT Rn=CASE [month]
WHEN 'Jan' THEN 1
WHEN 'feb' THEN 2
WHEN 'mar' THEN 3
ELSE 4
END,
*
FROM YOURTABLE)
UPDATE C
SET C.calcamt = D.calcamt
FROM cte C
INNER JOIN (SELECT A.month,
A.amt,
calcamt=A.amt / b.amt
FROM cte A
LEFT OUTER JOIN cte B
ON A.rn = b.rn + 1) D
ON C.[month] = D.[month]
source to share