Solved

Find gaps in date/time sequence

Posted on 2007-11-14
14
380 Views
Last Modified: 2008-02-01
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        

Thanks in advance.

0
Comment
Question by:cubixSoftware
  • 4
  • 3
  • 3
  • +3
14 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 20278805
select startRange,endRange from YourTablename WHERE     (flags= '1')
0
 
LVL 18

Expert Comment

by:JR2003
ID: 20278825
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
 
LVL 6

Expert Comment

by:Rajesh_mj
ID: 20279136
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
 
LVL 6

Expert Comment

by:Spot_The_Cat
ID: 20279165
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
 
LVL 6

Author Comment

by:cubixSoftware
ID: 20279260
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
 
LVL 6

Expert Comment

by:Rajesh_mj
ID: 20279359
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
 
LVL 6

Accepted Solution

by:
Rajesh_mj earned 500 total points
ID: 20279402
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 27

Expert Comment

by:MikeToole
ID: 20279423
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
 
LVL 6

Author Comment

by:cubixSoftware
ID: 20279776
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

your query returns

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
 
LVL 6

Expert Comment

by:Spot_The_Cat
ID: 20280159
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

then simply call it from within your stored procedure referencing your temporary table instead.
eg.

EXEC getDates
0
 
LVL 6

Author Comment

by:cubixSoftware
ID: 20281144
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
 
LVL 27

Expert Comment

by:MikeToole
ID: 20281381
cubixSoftware,
Any particular reason you didn't try my 8-line, no temp table, no cursor, no loop approach?
Mike
0
 
LVL 6

Author Comment

by:cubixSoftware
ID: 20282796
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
 
LVL 27

Expert Comment

by:MikeToole
ID: 20284498
Thanks, guess I looked at the wrong post!  ;-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now