In sql substract next value from previous value and check

Vishnu Bhangale

8y880

i have data in Log column

Log

8:50

9:50

10:00

13:00

15:00

when i substract next value from previous value and the difference is greater than 5 min then skip the next value and first value store kip as it is in column

ex suppose we substract 10:00 fom 9:50 and diff is greater than 5 min skip the 10:00 and store 9:50

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manish Kumar8y ago
Put in where condition column is not null
0
Vishnu Bhangale8y ago
i want again substract of last column and no null value in last col
NULL
08:30:00.0000000
NULL
08:45:00.0000000
NULL
NULL
NULL
NULL
NULL
NULL
NULL
09:06:00.0000000
NULL
12:23:00.0000000
NULL
13:12:00.0000000
NULL
13:44:00.0000000
NULL
NULL
Here No need of null value
like and 8:45-8:30 next 12:23-09:06 i am trying to using Lag but its not working also i
08:30:00.0000000
08:45:00.0000000
09:06:00.0000000
12:23:00.0000000
13:12:00.0000000
13:44:00.0000000
0
Manish Kumar8y ago
Vishnu not understand what u want
+2
Vishnu Bhangale8y ago
thanks but next i want the diff
NULL
08:30:00.0000000-09:06:00.0000000
12:23:00.0000000-13:12:00.0000000
and so on

+2
Manish Kumar8y ago
Try below query. You need to add your logic to match the requirement.
declare @tbl table
(logtime time)
insert into @tbl
select '8:50'
union all
select '8:50'
union all
select '9:50'
union all
select '10:00'
union all
select '10:00'
union all
select '13:00'
union all
select '15:00'
select logtime,lgtime,datediff(MINUTE,logtime,lgtime) as diff,
case when datediff(MINUTE,logtime,lgtime)>5 then logtime end
from (
select logtime,lead(logtime) over (order by logtime) as lgtime from @tbl
) t

Thanks,
Manish
+2
Sagar Pandurang Kap8y ago
Hi,
Refer below links :-

https://stackoverflow.com/questions/17560829/how-can-i-subtract-a-previous-row-in-sql
https://www.interfacett.com/blogs/how-to-use-values-from-previous-or-next-rows-in-a-query-in-sql-server/

They might help you....
+1