time differense in the lines sql Server 2008 r2

Question

I have a select Statement where 3 columns and n-rows like:

number of the equipment    time some                 count
0128                       2015-05-26 20:31:57.000   1
0128                       2015-05-27 06:53:12.000   1
0128                       2015-05-27 06:58:04.000   1

How can I find datediff between these times if records was 2 it would be possible to remove in one line as max and min time but number of records always different

I need to compare. for example case when datediff(mm,time1,time2) < 60 min then..... how can i compare time1-time2, time2 - time3 if the number of records constantly changes

my code

DECLARE @start_date DATE, @end_date DATE, @equip varchar(4), @shift_ident varchar(2)

SET @start_date = '2015-05-27'

SET @end_date = '2015-05-27'

SET @shift_ident = '1'

SET @equip ='0128'

;with cte as (SELECT ROW_NUMBER() over(ORDER BY START_TIMESTAMP) num, 
    EQUIP_IDENT as eq ,
    START_TIMESTAMP as time,
    count(STATUS_CODE) as Zapravka
from EQUIPMENT_STATUS_TRANS
WHERE STATUS_CODE = 'O23'
    AND SHIFT_DATE between @start_date and @end_date
    AND @shift_ident LIKE '%' + SHIFT_IDENT + '%'
    AND EQUIP_IDENT = @equip
    GROUP BY EQUIP_IDENT,START_TIMESTAMP)
SELECT est.time, DATEDIFF(mm, est.time,est2.time) as duration
from cte est
join cte est2 on est.num = est2.num-1

and result

2015-05-26 20:31:57.000 0
2015-05-27 06:53:12.000 0

duration = 0... why?

what is the expected output?. are you looking for something like this? http://stackoverflow.com/questions/30588284/how-to-calculate-cumulative-time-difference-in-sql/30588490#30588490 — ughai, Jun 02 '15 at 06:35
Welcome to stackoverflow. Please read [ask]. Hint: edit your question to include the desired result. — Zohar Peled, Jun 02 '15 at 07:43
http://stackoverflow.com/questions/21073666/calculate-datediff-between-rows-in-one-column-with-more-than-2-records this might be what you're looking for — Simbi, Jun 02 '15 at 07:47

score 0 · Answer 1 · answered Jun 02 '15 at 10:10

I assume you want to compare two consecutive rows only. You could order rows by datetime column (with row_number() function) and make a self-join by adjacent row numbers.

;with cte as (
select
    *, [rwn] = row_number() over(partition by 1 order by dt asc, no asc )
from ( 
    select '0128',cast('2015-05-26 20:31:57.000' as datetime2(3)),1 union all
    select '0128','2015-05-27 06:53:12.000',1 union all
    select '0128','2015-05-27 06:58:04.000',1 
) x (no,dt,cnt) 
)

select
    *,
    diff = datediff(mm,c2.dt, c1.dt)
from cte c1
join cte c2
    on c1.rwn+1=c2.rwn

time differense in the lines sql Server 2008 r2

1 Answers1