SQL Difference Between Rows

with cte as
(SELECT
   ROW_NUMBER() OVER (PARTITION BY table.name ORDER BY id) row,
   name,
   score
 FROM table)
SELECT 
   a.name ,
   a.score - ISNULL(b.score,0)
FROM
   cte a
   LEFT  JOIN cte b
   on a.name = b.name
    and a.row = b.row+1

declare @Test table (emp_audit_id int, eid int, [name] varchar(32), salary money);

insert into @Test (emp_audit_id, eid, [name], salary)
values
(1, 1, 'Daniel', 1000),
(2, 1, 'Dani', 1000),
(3, 1, 'Danny', 3000);

with cte as (
    select emp_audit_id, eid, [name], salary
      , row_number() over (partition by eid order by emp_audit_id) rn
    from @Test
)
select C.emp_audit_id, 'name' columnName, P.[Name] oldValue, C.[name] newValue
from cte C
left join cte P on P.eid = C.eid and P.rn + 1 = C.rn
where coalesce(C.[name],'') != coalesce(P.[Name],'')
union all
select C.emp_audit_id, 'salary' columnName, convert(varchar(21),P.salary), convert(varchar(21),C.salary)
from cte C
left join cte P on P.eid = C.eid and P.rn + 1 = C.rn
where coalesce(C.salary,0) != coalesce(P.salary,0)
order by C.emp_audit_id, columnName;

emp_audit_id	columnName	oldValue	newValue
1	name	NULL	Daniel
1	salary	NULL	1000.00
2	name	Daniel	Dani
3	name	Dani	Danny
3	salary	1000.00	3000.00

SQL: Difference between consecutive rows

You can use lag() to get the date of the previous order by the same customer:

select o.*,
    datediff(
        order_date,
        lag(order_date) over(partition by member_id order by order_date, order_id)
    ) days_diff
from orders o

When there are two rows for the same date, the smallest order_id is considered first. Also note that I fixed your datediff() syntax: in Hive, the function just takes two dates, and no unit.

I just don't get the logic you want to compute num_orders.

SQL Server - Sum of difference between rows in a table

Using a self join along with the modulus:

SELECT SUM(t1.SomeData - t2.SomeData) AS total_diff
FROM yourTable t1
INNER JOIN yourTable t2
    ON t1.SomeID = t2.SomeID + 1
WHERE t1.SomeID % 2 = 0;

Demo

This answer assumes that the SomeID sequence in fact starts with 1 and increments by 1 with each subsequent row. If not, then we might be able to first apply ROW_NUMBER over SomeID and generate a 1 to N sequence.

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY SomeID) rn
    FROM yourTable
)

SELECT SUM(t1.SomeData - t2.SomeData) AS total_diff
FROM cte t1
INNER JOIN cte t2
    ON t1.SomeID = t2.SomeID + 1
WHERE t1.rn % 2 = 0;

Difference between rows of the same column if particular conditions are met

Assuming the date column is always in increasing order we can use left join with self and bring the previous rep value and then calculate the difference outside. As,

select security_id,dt,rep,(rep-prev_rep) diff
  from
(
select t1.security_id,t1.dt,t1.rep,
       coalesce(t2.rep,0) prev_rep
  from mytable t1
  left join mytable t2
    on t1.security_id = t2.security_id
   and t2.dt = t1.dt - 1
)
order by rep;

Edit: addressing the query attempt by OP

If you can use window function as you have shown , you can modify the query as below,

select security_id
     , dt
     , rep
     , (rep-coalesce(max(rep) over (partition by security_id order by dt rows between unbounded preceding and 1 preceding),0)) diff
from mytable;
order by rep

Display the difference between rows in the same table

Here is an option that will dynamically unpivot your data without actually using Dynamic SQL.

Example

;with cte as (
Select emp_audit_id
      ,eid
      ,[key]
      ,newValue=[value]
      ,oldvalue = lag(value) over (partition by eid,[key] order by emp_audit_id)
 From Employee_audit A
 Cross Apply ( Select [key],[value] From OpenJson( (Select A.* For JSON Path,Without_Array_Wrapper  )  ) ) B
 Where [key] not in ('emp_audit_id','eid')
)
Select emp_audit_id
      ,columName = [key]
      ,oldvalue
      ,newvalue 
 From  cte
 Where newvalue<>oldvalue or oldvalue is null

Returns

emp_audit_id    columName   oldvalue    newvalue
1               department  NULL        ROP
1               name        NULL        Daniel
2               name        Daniel      Dani
3               name        Dani        Danny
1               salary      NULL        1000
3               salary      1000        3000

---

Related Topics