# Find gaps in date/time sequence

Hi

I have searched for the last few hours but still cannot find a solution so I hope you guys can help....

I have a table structured as follows - note dates are DMY format - that holds a date from and to in 15 minute intervals

dateFrom                    dateTo                       flags
------------                  ---------                       ------
1/11/2007 0:00:00     1/11/2007 0:15:00         1
1/11/2007 0:15:00     1/11/2007 0:30:00         1
1/11/2007 0:30:00     1/11/2007 0:45:00         0
1/11/2007 0:45:00     1/11/2007 1:00:00         1
1/11/2007 1:00:00     1/11/2007 1:15:00         0
1/11/2007 1:30:00     1/11/2007 1:45:00         1
1/11/2007 1:45:00     1/11/2007 2:00:00         1

Whet I need (if at all possible) is a query that returns the range of dates where FLAGS=1, so I would get a result set of

startRange                    endRange
--------------                   -------------
1/11/2007 0:00:00     1/11/2007 0:30:00
1/11/2007 0:45:00     1/11/2007 1:00:00
1/11/2007 1:30:00     1/11/2007 2:00:00

LVL 6
###### Who is Participating?

Commented:
Sorry,a small change:

select dateFrom,(SELECT
IsNull(Min(T2.dateFrom), (SELECT    Max(T3.dateTo)
FROM   tblDt T3 WHERE  (T3.Flg = 1) AND T3.dateFrom >= T1.DateFrom))
FROM         #tblTmpDt T2 WHERE     (T2.Flg = 0)
AND T2.dateFrom <= (SELECT    Max(T3.dateTo)
FROM         #tblTmpDt T3
WHERE     (T3.Flg = 1) AND T3.dateFrom >= T1.DateFrom) AND
T2.dateFrom >= T1.DateFrom )
from #tblTmpDt T1
where flg = 1 AND
NOT EXISTS(Select * from #tblTmpDt T4 where T4.flg = 1 AND T4.dateTo = T1.DateFrom)
0

Product SpecialistCommented:
select startRange,endRange from YourTablename WHERE     (flags= '1')
0

Commented:
The only way I can think of doing this in an efficient way is in a table valued function.

Create a cursor for your initial query and iterate through it inserting records into the return table when a run finishes.
0

Commented:
Hi,

Just Try :
(I treated 'tblDt' as table name)

CREATE FUNCTION [dbo].[fnc_GetEndTime] (@StTime DATETIME)
RETURNS Datetime AS
BEGIN

DECLARE @EndTime Datetime

IF EXISTS(SELECT * FROM tblDt WHERE FLG =1 AND dateTo =@StTime) RETURN  @StTime

SELECT   @EndTime= Max(dateTo)
FROM         tblDt
WHERE     (Flg = 1) AND dateFrom >= @StTime

SELECT   @EndTime= IsNull(Min(dateFrom),@EndTime)
FROM         tblDt
WHERE     (Flg = 0) AND dateFrom <= @EndTime AND  dateFrom >= @StTime

RETURN @EndTime

END

Select DateFrom,dbo.fnc_GetEndTime(DateFrom) from tblDt
where flg = 1  AND DateFrom <> dbo.fnc_GetEndTime(DateFrom)

0

Commented:
Try this:

DECLARE @frDate DATETIME, @FLAGS int, @prevFLAGSTat int,
@toDate DATETIME, @frStart DATETIME,
@toEnd DATETIME

DECLARE curs1 CURSOR FOR
SELECT dtFrom, dtTo, FLAG FROM tmprng
order by dtFrom, dtTo

OPEN curs1

CREATE TABLE #tmptbl(frDte DATETIME,tDate DATETIME)

FETCH NEXT FROM curs1
INTO @frDate, @toDate,@FLAGS
SET @prevFLAGSTat = 0

WHILE (@@FETCH_STATUS = 0)
BEGIN

IF @FLAGS = 1 and @prevFLAGSTat = 0
BEGIN
SET @frStart = @frDate
END

IF @FLAGS = 1
BEGIN
SET @toEnd = @toDate
END

IF @FLAGS = 0 and @prevFLAGSTat = 1
BEGIN
INSERT into #tmptbl VALUES (@frStart,@toEnd)
END

SET @prevFLAGSTat = @FLAGS

FETCH NEXT FROM curs1
INTO @frDate, @toDate,@FLAGS

END

CLOSE curs1
DEALLOCATE curs1

select * from #tmptbl

drop table #tmptbl
0

Author Commented:
hi rajesh_mj

this looks promising however my table is infact a temporary table of data type table created within a store procedure.

I can't seem to create the function and pass this data type??!?!?

Is there a way around it?? (Other than creating a perm table in the database)

0

Commented:
Hi,
Just try:
(I just tried to convert logic in function to inner query)

select dateFrom,
(SELECT   Min(T2.dateFrom)
FROM         #tblTmpDt T2 WHERE     (T2.Flg = 0)
AND T2.dateFrom <= (SELECT    Max(T3.dateTo)
FROM         #tblTmpDt T3
WHERE     (T3.Flg = 1) AND T3.dateFrom >= T1.DateFrom) AND
T2.dateFrom >= T1.DateFrom )
from #tblTmpDt T1
where flg = 1 AND
NOT EXISTS(Select * from #tblTmpDt T4 where T4.flg = 1 AND T4.dateTo = T1.DateFrom)
0

Commented:
This does what you want in (fairly) straightforward SQL, just plug-in your table name for all the occurences of tblDates:

select td.DateFrom,
(select min(td1.DateTo)
From tblDates td1 left Join tblDates tf on td1.DateTo = tf.DateFrom
Where td1.Flags = 1
And td1.DateFrom >= td.DateFrom
and isnull(tf.flags, 0) = 0 ) as DateTo

From tblDates td left Join tblDates tp on tp.DateTo = td.DateFrom
Where td.Flags = 1 and isnull(tp.flags, 0) = 0
0

Author Commented:
Hi Rajesh_mj

This is almost there....

if my table has the following

dateFrom                    dateTo                       flags
------------                  ---------                       ------
1/11/2007 23:30:00     1/11/2007 23:45:00         1
1/11/2007 23:45:00     2/11/2007 0:00:00         1
2/11/2007 0:00:00     2/11/2007 0:15:00         1
2/11/2007 0:15:00     2/11/2007 0:30:00         1

startRange                    endRange
--------------                   -------------
1/11/2007 23:30:00     2/11/2007 0:30:00

... and I would like it to break over midnight....

startRange                    endRange
--------------                   -------------
1/11/2007 23:30:00     2/11/2007 0:00:00
2/11/2007 0:00:00     2/11/2007 0:30:00

Is this possible...?
0

Commented:
The following will do it:

CREATE PROCEDURE getDates AS
DECLARE @frDate DATETIME, @FLAGS int, @prevFLAGSTat int,
@toDate DATETIME, @frStart DATETIME,
@toEnd DATETIME

DECLARE curs1 CURSOR FOR
SELECT dtFrom, dtTo, FLAG FROM tmprng
order by dtFrom, dtTo

OPEN curs1

CREATE TABLE #tmptbl(frDte DATETIME,tDate DATETIME)

FETCH NEXT FROM curs1
INTO @frDate, @toDate,@FLAGS
SET @prevFLAGSTat = 0

WHILE (@@FETCH_STATUS = 0)
BEGIN

IF @FLAGS = 1 and @prevFLAGSTat = 0
BEGIN
SET @frStart = @frDate
END

IF @FLAGS = 1
BEGIN
SET @toEnd = @toDate
END

IF @FLAGS = 0 and @prevFLAGSTat = 1 OR datepart(day,@frStart) <> datepart(day,@toEnd)
BEGIN
INSERT into #tmptbl VALUES (@frStart,@toEnd)
SET @FLAGS=0
END

SET @prevFLAGSTat = @FLAGS

FETCH NEXT FROM curs1
INTO @frDate, @toDate,@FLAGS

PRINT @frDate + @toDate+@FLAGS

END

GO

eg.

EXEC getDates
0

Author Commented:
Hi Rajesh_mj

I have spoken to the users and the midnight issue is no longer a problem so your solution works a treat!

thanks.
0

Commented:
cubixSoftware,
Any particular reason you didn't try my 8-line, no temp table, no cursor, no loop approach?
Mike
0

Author Commented:
Mike

Rajesh was quicker to answer and therefore I gave time to his solution. If it hadn't of worked I would have moved on to yours.

His solution doesn't use temp tables, cursors etc. and I could easily substitute my table data type variable like I could for yours.

Both of your solutions are similar although yours is less lines and doesn't re-join to itself as many times.

Having given yours a quick test it seems to come up with the same results as Rajesh's.

I am happy with what I have gone with, and there are no speed issues.

0

Commented:
Thanks, guess I looked at the wrong post!  ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.