CODEWITHSUNDEEP
sqlsql-server
Alina Dilshad
Alina Dilshad

Reputation: 193

get previous row calculated column to next row for calculation

I am trying to get previous row value to next row for doing calculation but its not working. Below is my query. Let me explain you what I am trying to achieve. column Y is calculating by adding (n1+n2+n3+n4)/4 of current row and column x value will same as column n1 for first row but from second row and onward column x will calculate by adding column y of previous row and column x of previous row and divided by 2 and so on. Table values can be any

CREATE TABLE #TestingData 
(
    nId INT IDENTITY(1, 1),
    n1 DECIMAL(18, 3),
    n2 DECIMAL(18, 3),
    n3 DECIMAL(18, 3),
    n4 DECIMAL(18, 3)
)

INSERT INTO #TestingData 
VALUES (2, 5, 1, 5), (1, 1, 1, 1), (9, 4, 8, 99), (6, 5, 4, 3)

SELECT 
    nId, n1, n2, n3, n4,    
    CASE 
        WHEN nId = 1 THEN x 
        ELSE (MAX(x) OVER (ORDER BY nId) + (SUM(y) OVER (ORDER BY nId) - y)) / 2 
    END AS x,
    y
FROM 
    (SELECT 
         nId, n1, n2, n3, n4,
         CASE 
             WHEN nId = 1 THEN n1 ELSE 0 
         END x,
         (n1 + n2 + n3 + n4) / 4 y
     FROM 
         #TestingData) AS outtab

Below is the sample output:

enter image description here

Upvotes: 0

Views: 334

Answers (1)

D-Shih
D-Shih

Reputation: 46219

You can try to use cte recursive to make the calculation, by the way you might need to let type more than DECIMAL(18,3) otherwise the floating-point numbers will be inaccurate.

;WITH CTE AS (
  SELECT nId,n1,n2,n3,n4,n1 x,(n1+n2+n3+n4)/4 y
  FROM TestingData
  WHERE nId = 1
  UNION ALL
  SELECT t.nId,t.n1,t.n2,t.n3,t.n4,CAST((c.x+c.y)/2 AS DECIMAL(18,3)),(t.n1+t.n2+t.n3+t.n4)/4 
  FROM CTE c
  INNER JOIN TestingData t
  ON c.nId +1 = t.nid
)
SELECT * FROM CTE

sqlfiddle

if your nId number is discontinuous, you can try to use LEAD window function get the next nId each rows then do cte recursive

;WITH CTE AS (
  SELECT *,LEAD(nId) OVER(ORDER BY nId) n_nId,(n1+n2+n3+n4)/4 y
  FROM TestingData
), CTE2 AS (
  SELECT n_nId,n1,n2,n3,n4,n1 x,y
  FROM CTE
  WHERE nId = 1
  UNION ALL
  SELECT t.n_nId,t.n1,t.n2,t.n3,t.n4,CAST((c.x+c.y)/2 AS DECIMAL(18,6)),t.y
  FROM CTE2 c
  INNER JOIN CTE t
  ON c.n_nId = t.nid
)
SELECT * 
FROM CTE2

sqlfiddle

Upvotes: 1

Related Questions