In sql substract next value from previous value and check

Question

i have data in Log column Log 8:50 8:50 9:50 10:00 10:00 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

Manish Kumar · Answer

Vishnu not understand what u want

Vishnu Bhangale · Answer

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

Manish Kumar · Answer

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

Sagar Pandurang Kap · Answer

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....

Manish Kumar · Answer

Put in where condition column is not null

Vishnu Bhangale · Answer

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

In sql substract next value from previous value and check

Answers (6)