I want to calculate diff bet 1st&2nd and 3rd&4th row in sql

Question

hi i want to calculate the time diff bet 1st&2nd and 3rd&4th row of same column in sql and calculate sum of the difference in my table only two field ID and time

Vishnu Bhangale · Answer

Thanks for you help but you calculate two conservative column diff following i got solution and it solve my problem thanks again Rajneesh SELECT *, diff= CASE DIrection WHEN 'O' THEN DATEDIFF(minute, Lag(Punchtime, 1) OVER(ORDER BY [UserID], Punchtime), Punchtime) ELSE NULL END FROM dbo.logdata

Rajneesh Chaubey · Answer

WITH rows AS ( SELECT *, ROW_NUMBER() OVER ( ORDER BY SampleColumn) AS rn FROM SampleTable ) SELECT DATEDIFF( second , mc.SampleColumn, mp.SampleColumn) FROM rows mc JOIN rows mp ON mc.rn = mp.rn - 1

Rajneesh Chaubey · Answer

Hi @Vishnu, I gave you the query that was what you asked for. Anyway you got answer, that is more important. Thank you.

Vishnu Bhangale · Answer

it not working i have the table structure 1 08:50:00.0000000 7 I 2 09:59:00.0000000 7 O 3 10:00:00.0000000 7 I 4 12:20:00.0000000 7 O 5 13:40:00.0000000 7 I 6 15:50:00.0000000 7 O 7 18:15:00.0000000 7 I i want o/p 09:59-8:50 calculate this diff 12:20-10:00 calculate diff i.e. 140 min

Rajneesh Chaubey · Answer

I think you need something like this:- SELECT SampleColumn ,SampleColumn- LAG(SampleColumn, 1) OVER ( ORDER BY SampleColumn) FROM SampleTable Let me know if it worked. Thanks.

I want to calculate diff bet 1st&2nd and 3rd&4th row in sql

Answers (5)