Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

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        

Thanks in advance.

0
cubixSoftware
Asked:
cubixSoftware
  • 4
  • 3
  • 3
  • +3
1 Solution
 
James MurrellProduct SpecialistCommented:
select startRange,endRange from YourTablename WHERE     (flags= '1')
0
 
JR2003Commented:
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
 
Rajesh_mjCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Spot_The_CatCommented:
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
 
cubixSoftwareAuthor 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
 
Rajesh_mjCommented:
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
 
Rajesh_mjCommented:
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
 
MikeTooleCommented:
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
 
cubixSoftwareAuthor 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

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
 
Spot_The_CatCommented:
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
 
cubixSoftwareAuthor 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
 
MikeTooleCommented:
cubixSoftware,
Any particular reason you didn't try my 8-line, no temp table, no cursor, no loop approach?
Mike
0
 
cubixSoftwareAuthor 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
 
MikeTooleCommented:
Thanks, guess I looked at the wrong post!  ;-)
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 4
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now