Solved

Find gaps in date/time sequence

Posted on 2007-11-14
14
385 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
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.

 
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

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…
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
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

734 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