Get first and last entry from a set and find time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Get first and last entry from a set and find time

Author

Topic

kelemvor
Starting Member

6 Posts

Posted - 2011-07-13 : 09:20:42

Howdy,

I know I can do this manually but I'm wondering if there's a way to code this as all one query.

I have a bunch of data in sets where each set has one column the same. Each row also has s time stamp on it.

I want to find the first instance of a set and the last instance of a set and then calculate the time difference between them to see how long that set was active.

SO my data might look like this:


ID   Time      text
1    5:30:00   start
1    5:31:00   middle
1    5:33:00   end
2    5:31:00   start
2    5:35:00   middle
2    5:39:00   end

And the info I want to get back is:
1 - 2:00
2 - 8:00

Is there an easy way to do that?

If not, what's the easiest way to just return the earliest entry for each set, and to then return the latest entry, and I can calculate the difference in Excel or something.

Thanks.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-13 : 09:35:13

Will text always contain 'Start' for the earliest entry and 'End' for the latest?




Declare @t table (
	id int,
	t time,
	l varchar(10)
)

Insert Into @t Select 1, '5:30:00', 'Start'
Insert Into @t Select 1, '5:31:00', 'Middle'
Insert Into @t Select 1, '5:33:00', 'End'
Insert Into @t Select 2, '5:31:00', 'Start'
Insert Into @t Select 2, '5:35:00', 'Middle'
Insert Into @t Select 2, '5:39:00', 'End'


Select * From @t

Select 
	A.id,
	A.t,
	B.t,
	DATEDIFF(MI, a.t, b.t),
	convert(time,dateadd(mi,DATEDIFF(MI, a.t, b.t),0))
From @t A
Inner join @t B
On A.id = B.id
and 'End' = B.l
Where A.l = 'Start'

Corey

I Has Returned!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-07-13 : 09:44:16

Arent' Start Middle and End irrelevant as they time itself tells you the order
Select * From @t
select id,datediff(mi,min(t),max(t)) as Diff
from @t
group by id

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources