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
Go to Top of Page

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
Go to Top of Page

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.

Go to Top of Page
   

- Advertisement -