Solved

Find gaps in date/time sequence

Posted on 2007-11-14
14
386 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

 
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

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.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

627 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