Solved

Find gaps in date/time sequence

Posted on 2007-11-14
14
381 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

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…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

773 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