CODEWITHSUNDEEP
sqlsql-server
Dodgeball
Dodgeball

Reputation: 125

subtract data from single column

I have a database table with 2 columns naming piece and diff and type.

Here's what the table looks like

id | piece | diff | type
1  | 20    | NULL | cake
2  | 15    | NULL | cake
3  | 10    | NULL | cake

I want like 20 - 15 = 5 then 15 -10 = 5 , then so on so fort with type as where.

Result will be like this

id | piece | diff | type
1  | 20    | 0    | cake
2  | 15    | 5    | cake
3  | 10    | 5    | cake

Here's the code I have so far but i dont think I'm on the right track

SELECT 
    tableblabla.id,
    (tableblabla.cast(pieces as decimal(7, 2)) - t.cast(pieces as decimal(7, 2))) as diff 
FROM 
    tableblabla
INNER JOIN
    tableblablaas t ON tableblabla.id = t.id + 1

Thanks for the help

Upvotes: 1

Views: 46

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93764

Use LAG/LEAD window function.

Considering that you want to find Difference per type else remove Partition by from window functions

select id, piece,
       Isnull(lag(piece)over(partition by type order by id) - piece,0) as Diff,
       type
From yourtable

If you are using Sql Server prior to 2012 use this.

;WITH cte
     AS (SELECT Row_number()OVER(partition by type ORDER BY id) RN,*
         FROM   Yourtable)
SELECT a.id,
       a.piece,
       Isnull(b.piece - a.piece, 0) AS diff,
       a.type
FROM   cte a
       LEFT JOIN cte b
              ON a.rn = b.rn + 1

Upvotes: 3

Related Questions