CODEWITHSUNDEEP
sql
Yagnesh Cangi
Yagnesh Cangi

Reputation: 393

Complicated SQL query to find number difference in same table

Could someone please help me write an SQL server query which would give me expected outcome from current data.I need to find meter Gap between each position.

I need to find meter Gap between each position

Upvotes: 0

Views: 89

Answers (3)

Vecchiasignora
Vecchiasignora

Reputation: 1315

just use LEAD and combine it with subquery (reading table data once), like this

select tt.PersonId, tt.PreviousPosition, tt.NextPosition,
       tt.NextMeter - tt.previousMeter
  from (select PersonId, t.position PreviousPosition,
                LEAD(Position) OVER(order by Position) NextPosition,
                t.Meter PreviousMeter,
                LEAD(Meter) OVER(order by Position) NextMeter
           from myTable t) tt
 where tt.NextPosition is not null

but i have sorted by Position, if you have some key (which keep inserting ordering ) or you have some special logic with inerting and ordering use only that columns in sorting

Upvotes: 1

SE1986
SE1986

Reputation: 2760

Try this (I had to add a unique PK column to your data)

CREATE TABLE #Data
(
    ID INT,
    PersonId INT,
    Position INT,
    Meter INT
)

INSERT INTO #Data VALUES (1,1,10,10),(2,1,22,20),(3,1,34,35),(4,1,46,46)

SELECT  PersonId,
        Position AS PreviousPosition,
        LEAD(Position) OVER (ORDER BY ID) NextPosition,
        Meter as ThisMeter,
        LEAD(Meter) OVER (ORDER BY ID) NextMeter
INTO    #Results
FROM    #Data

SELECT PersonId,
       PreviousPosition,
       NextPosition,
       NextMeter - ThisMeter AS MetreGap
FROM   #Results
WHERE  NextPosition IS NOT  NULL

DROP TABLE #Results
DROP TABLE #Data

Upvotes: 0

kkica
kkica

Reputation: 4104

Try something like this.

SELECT id, position as previous,(
       SELECT TOP 1 position
       FROM table t1
       WHERE ti.position > t2.position
       ORDER BY
             position
       ) as next, 
       (
       SELECT TOP 1 meter
       FROM table t1
       WHERE ti.position > t2.position
       ORDER BY
             position
       ) - meter AS gap
FROM table t2
ORDER BY position

Upvotes: 0

Related Questions