Find a time interval between two records in sql server

It's easiest just to show the table:

CREATE TABLE [dbo].[ThisTable](
      [ID] [int] NOT NULL,
      [Tiempo] [int] NULL,
      [Flag] [nvarchar](30) NULL,
 )

insert into thistable(id, tiempo, flag) values(1, 11, 'lsdf')
insert into thistable(id, tiempo, flag) values(2, 13, 'start')
insert into thistable(id, tiempo, flag) values(3, 15, 'lsdf')
insert into thistable(id, tiempo, flag) values(4, 19, 'lsdf')
insert into thistable(id, tiempo, flag) values(5, 21, 'stop')
insert into thistable(id, tiempo, flag) values(6, 22, 'lsdf')
insert into thistable(id, tiempo, flag) values(8, 24, 'start')
insert into thistable(id, tiempo, flag) values(10, 25, 'lsdf')
insert into thistable(id, tiempo, flag) values(11, 27, 'lsdf')
insert into thistable(id, tiempo, flag) values(12, 29, 'stop')

What I need to do is find the difference (subtract) the 'tiempo' values that are labeled start and stop in the 'flag' column.  So the first pair I need to do is 21-13 and the final 29 -24. The id is an autonumber and the tiempo field is a time stamp, so it can be assumed to order the table.  What I mean is if you are doing it manually you would order the table by 'tiempo' or id and subtract each start stop pair. These are the only assumptions you can make about this table.

Here is my clumsy attempt:
select maintable.id, maintable.start, maintable.stopp from
(select Id, Tiempo, flag,
(select (tiempo) from thistable where id = D.id and flag = 'start') as start,
(select (tiempo) from thistable where id = D.id and flag = 'stop') as stopp
from thistable D
where flag = 'start' or flag = 'stop') Maintable
OutOnALimbAlwaysAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
Reza RadConsultant, TrainerCommented:
change @thistable variable in query below with your table name

select stop.Tiempo-start.Tiempo as timedifference, start.Tiempo as starttime, stop.Tiempo as stoptime from 
(
select id,Tiempo,row_number() over(order by id) as RowNo from @thistable
where flag='start'
)start
inner join 
(
select id,Tiempo,row_number() over(order by id) as RowNo from @thistable
where flag='stop'
)stop
on start.RowNo=stop.RowNo

Open in new window

PortletPaulEE Topic AdvisorCommented:
This result:
| start_tiempo | stop_tiempo | diff |
|--------------|-------------|------|
|           13 |          21 |    8 |
|           24 |          29 |    5 |

Open in new window


from this query:
select
      ca.start_tiempo
    , t1.tiempo as stop_tiempo
    , t1.tiempo - ca.start_tiempo as diff
from ThisTable t1
cross apply (
             select top (1) tiempo
             from ThisTable t2
             where t2.tiempo < t1.tiempo and flag = 'start'
             order by tiempo DESC
            ) ca (start_tiempo)
where flag = 'stop'
;

Open in new window


see: http://sqlfiddle.com/#!6/48f730/3

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Reza RadConsultant, TrainerCommented:
the code above only works if start and stop came after each other consecutively without any repetition of start or stop.
OutOnALimbAlwaysAuthor Commented:
Speaking of dates, they actually are unix times (seconds since jan 1 1970). I just threw those numbers in. All I have to do is subtract the right start-stop pairs of numbers.  The right pair is in consecutive order by id or my 'tiempo' field: Start-stop start-stop. After that I will sum up the results of those subtractions.
Reza RadConsultant, TrainerCommented:
sorry my comments was separated. my second comment relates to my first comment
PortletPaulEE Topic AdvisorCommented:
Reza, your statement isn't true

the outer query is for all 'stops', the cross apply then finds the first start that is prior to that stop therefore ensuring there are accurate pairs

I think the row_number approach fails if there is a start but no corresponding stop (yet)
PortletPaulEE Topic AdvisorCommented:
>>sorry my comments was separated. my second comment relates to my first comment

oh!, now I understand.
OutOnALimbAlwaysAuthor Commented:
reza, looking good. Let me try it first thing tomorrow with the real data.  I will also try Pauls. Thank you guys. I don't know how you figure this stuff out, but glad you did!
OutOnALimbAlwaysAuthor Commented:
Paul's solution seems so far to be absolutely perfect for my needs. Actually, I did not mention it, but there may be a start without a stop, so I just transposed 'start' and 'stop' and the query did not consider the start without a stop! Perfect.

I did try Reza's solution and it worked, and I really appreciate his comment about a possible weakness.

I am really impressed with the T-sql ability on this site.
PortletPaulEE Topic AdvisorCommented:
Actually I chose the start and stop deliberately, I'm surprised it had to be reversed,sorry thought I had it the right way around. Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.