Query a table having minimum time span

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

SQL Server 2012 Forums

Transact-SQL (2012)

Query a table having minimum time span

Author

Topic

roberte_79
Starting Member

2 Posts

Posted - 2015-05-04 : 10:31:34

Hi guys:

I am having problems doing a particular query for a given table in a SQL 2012 database. First, the structute of the "TimeTable" table is as follows:

Id      STime                   FTime
779	30/04/2015 15:13	30/04/2015 16:23
787	30/04/2015 17:01	30/04/2015 19:58
795	30/04/2015 20:05	01/05/2015 00:39
803	30/04/2015 21:26	30/04/2015 23:09
811     01/05/2015 00:45        01/05/2015 02:00

What I want is to query this table grouping different rows so difference in time from "STime" field of "Id" and "FTime" field of "Id_Min" is minimum. So the result of the query should be as follow

Id      Id_Min	STime                   FTime_Min               Span
787	779	30/04/2015 17:01	30/04/2015 16:23	38
795	787	30/04/2015 20:05	30/04/2015 19:58	7
803	795	30/04/2015 21:26	30/04/2015 20:05	88
811     803	01/05/2015 00:45        01/05/2015 00:39	6

So far I have only come to a query that returns the "span" time

SELECT   t1.Id,MIN(DATEDIFF('s',t2.FTime,t1.STime)) As Span
FROM     TimeTable AS t2,TimeTable AS t1
WHERE    DATEDIFF('s',t2.FTime,t1.STime)>=0
GROUP BY t1.Id
ORDER BY t1.Id

which returns the following

Id      Span
787	38
795	7
803	88
811     6

which is incomplete. Anyone can help me out with this, please?

Note: Although Id_Min in query is almost always the previous Id in table, this is not always the case.

Thanks in advance

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-04 : 12:17:31

I think there's a problem in your sample output, third line, since the difference in minutes between 30/04/2015 21:26 and 30/04/2015 20:05 is 81, not 88

Gerald Britton, MCSA
Toronto PASS Chapter

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-04 : 12:19:15

If I understand your problem, this might do it for you:


SELECT t1.id
	,t3.id
	,t1.stime
	,t3.ftime
	,t2.Span
FROM TimeTable t1
-- Get minimum time span for a given two rows
CROSS APPLY (
	SELECT t1.Id
		,MIN(DATEDIFF(Minute, t2.FTime, t1.STime)) AS Span
	FROM TimeTable AS t2
	INNER JOIN TimeTable AS t1 ON t1.Stime >= t2.Ftime
	GROUP BY t1.Id
	) t2
-- get data from row matching time span
CROSS APPLY (
	SELECT t3.id
		,t3.Ftime
		,t3.Stime
	FROM TimeTable t3
	WHERE datediff(minute, t3.Ftime, t1.Stime) = t2.Span
	) t3

Gerald Britton, MCSA
Toronto PASS Chapter

roberte_79
Starting Member

2 Posts

Posted - 2015-05-05 : 08:56:34

quote:
Originally posted by gbritton

I think there's a problem in your sample output, third line, since the difference in minutes between 30/04/2015 21:26 and 30/04/2015 20:05 is 81, not 88

Yes, it's a mistake. I wrote the values by hand so the result was not automatically computed.

quote:
Originally posted by gbritton
If I understand your problem, this might do it for you:

SELECT t1.id,t3.id,t1.stime,t3.ftime,t2.Span
FROM TimeTable t1
-- Get minimum time span for a given two rows
CROSS APPLY (
SELECT t1.Id,MIN(DATEDIFF(Minute, t2.FTime, t1.STime)) AS Span
FROM TimeTable AS t2
INNER JOIN TimeTable AS t1 ON t1.Stime >= t2.Ftime
GROUP BY t1.Id
) t2
-- get data from row matching time span
CROSS APPLY (
SELECT t3.id,t3.Ftime,t3.Stime
FROM TimeTable t3
WHERE datediff(minute, t3.Ftime, t1.Stime) = t2.Span
) t3

It works! Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources