sql server date recursive

Question

based on another question SQL Server: How can you turn a series of consecutive begin and end datetimes into a single begin-end range?

I search a recursive way to get the last date of downtime. I have 3 downtime linked. I want to know when my service will be available.

Sample
    DECLARE @Downtime AS TABLE(id int, beginDT datetime, endDT datetime)
    INSERT INTO @Downtime VALUES('a','09:00','11:00')
    INSERT INTO @Downtime VALUES('b','10:00','12:00')
    INSERT INTO @Downtime VALUES('c','12:00','13:00')
    INSERT INTO @Downtime VALUES('d','15:00','16:00')


DECLARE @TimeParam AS DATETIME
SET @TimeParam ='1900-01-01 11:00:00.000'

At 11:00, I'm in downtime (id=1 or 2, doesn't matter). My question is, "When the service will be available (not down)?"

the answer, on the sample, is 13:00. Because we got a downtime from 9 to 11 (id=1), the id=2 give us 11 to 12, and id=3 show 12 to 13. So , it will be down from 9 to 13.

I need an sql query to found that. A recursive while the begin/end is between the end of one of the other.

I try something like that but its illegal syntax

;WITH myDates(beginDT,endDT)
AS
(
    SELECT beginDT, enddt
    FROM @Downtime D1
    WHERE @TimeParam BETWEEN begindt AND endDT

    UNION ALL

    SELECT beginDT, endDT 
    FROM @Downtime D2
    WHERE 
        (SELECT TOP 1  Max(enddt)
        FROM myDates )      
        BETWEEN begindt AND endDT
)
SELECT * FROM myDates

More sample

    INSERT INTO @Downtime VALUES('e','12:00','17:00') 
    INSERT INTO @Downtime VALUES('f','15:00','19:00') 
    INSERT INTO @Downtime VALUES('g','19:00','20:00') 
    INSERT INTO @Downtime VALUES('h','20:00','21:00') 
    INSERT INTO @Downtime VALUES('i','21:00','22:00') 
    INSERT INTO @Downtime VALUES('j','22:00','23:00')
    INSERT INTO @Downtime VALUES('k','23:00','23:01')
    INSERT INTO @Downtime VALUES('l','22:00','23:05')
    INSERT INTO @Downtime VALUES('m','22:00','23:06')
    INSERT INTO @Downtime VALUES('n','22:00','23:05')
    INSERT INTO @Downtime VALUES('o','22:00','23:17')
    INSERT INTO @Downtime VALUES('p','23:17','23:22')
    INSERT INTO @Downtime VALUES('q','23:22','23:23')
    INSERT INTO @Downtime VALUES('r','23:25','23:30')

the end of downtime should be 23:23.

Why not write code instead of work hard with SQL to do it? a few lines of code can do it in a much easier and probably bug-free way — Noam Rathaus
– Noam Rathaus, Commented Nov 25, 2013 at 20:48
I search an sql query way, I can do it on code, but its on a webpage often called. — forX
– forX, Commented Nov 25, 2013 at 21:13
I wish I knew, I just think that SQL in general isn't good in taking rows data and making into one "row" which is what you are asking, whereas code can easily make what you return from the DB into a linked list and then find "holes" in that linked list (i.e. 9->10, 10->11, 12->13, etc, and a hole at 11->12) — Noam Rathaus
– Noam Rathaus, Commented Nov 25, 2013 at 21:13
So store the results in a table, so it gets done only once ever hour? — Noam Rathaus
– Noam Rathaus, Commented Nov 25, 2013 at 21:13

Michael · Accepted Answer · 2013-12-12 04:23:13Z

1

I'm making an assumption that the date columns in @DownTime are not actually time only, because that would mean they default to 1/1/1900. If this assumption is wrong, please let me know.

SQL Fiddle Demo

DECLARE @TimeParam AS DATETIME
SET @TimeParam ='1900-01-01 11:00:00.000'


;WITH myDates(id, begindt, endDT)
AS
(
    Select top 1 id, begindt, EndDT    
    from DownTime 
    where @TimeParam not between beginDT and endDT        

    UNION ALL

    SELECT DownTime.id, DownTime.beginDT, DownTime.endDT    
    FROM myDates D2,DownTime
    Where D2.endDT >= DownTime.beginDT
    and D2.beginDT <= DownTime.endDT    
    and DownTime.ID > D2.ID
)
Select top 1 EndDT from myDates order by endDT desc

This is also assuming that the data in the table is already sorted by beginDT (as you have provided in your sample data). If that is not the case, you'll likely have to sort the records and insert them into a temp table, and modify the CTE to use the temp table instead. You could instead try adding ROW_NUMBER to the CTE, but that gets a bit tricky with recursive CTEs.

edited Dec 12, 2013 at 4:23

answered Dec 11, 2013 at 4:17

Michael

5996 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

forX Over a year ago

Your right, the downtime is a full date. Its just easier looking only at time.

forX Over a year ago

your answer is wrong. think you want to know till when your system will be down.

Michael Over a year ago

Sorry, @forX. My original solution was based on the limited sample data you had initially supplied. I have updated my solution with a recursive CTE that gets the job done based on the new data provided with an assumption or two.

Michael Over a year ago

I did some more testing on my new solution and it's not quite 100%, but I think it's close. A little more tweaking and it should be good.

forX Over a year ago

I found an error. I cant supposed that id are ordered correctly. May think the ID is random alpha numeric. But I cant just remove this validation because that made the recursive infinite. You can easely test with your sqlFiddle and replace id's

Collectives™ on Stack Overflow

sql server date recursive

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related