Help finding a date

  • If I run the following, it returns:

    time_sched_start_dttime_sched_end_dt

    2014-06-23 10:45:00.0002014-06-23 12:15:00.000

    I would expect it to return the first 45 minute available timeslot:

    time_sched_start_dttime_sched_end_dt

    2014-06-23 09:15:00.0002014-06-23 10:00:00.000

    ChrisM@Work (6/24/2014)


    DECLARE @duration INT=45

    SELECT TOP 1

    time_sched_start_dt = MAX(CASE WHEN dir = 'S' THEN dt END),

    time_sched_end_dt = MAX(CASE WHEN dir = 'E' THEN dt END)

    FROM ( -- f

    SELECT dir, dt, Grouper = (ROW_NUMBER() OVER(ORDER BY dt, dir)-1)/2

    FROM ( -- e

    SELECT dir, dt, ct = COUNT(*) OVER(PARTITION BY dt)

    FROM ( -- d

    SELECT dir = 'S', dt = time_sched_start_dt

    FROM #return_schedule sr

    WHERE sr.col_val = ''

    UNION ALL

    SELECT dir = 'E', dt = time_sched_end_dt

    FROM #return_schedule sr

    WHERE sr.col_val = ''

    ) d

    ) e

    WHERE ct = 1

    ) f

    GROUP BY Grouper

    HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) > @duration

    ORDER BY time_sched_start_dt

  • Change

    HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) > @duration

    to

    HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) >= @duration

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work (6/24/2014)


    Change

    HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) > @duration

    to

    HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) >= @duration

    This seems to have worked. I want to thank you for taking the time to look into this and help me out as it was a real challenge for me. I really appreciate it.

  • Hello guys,

    As rightly pointed out by Chris the query I posted earlier were having some issues.Please see refined one.

    DECLARE @duration INT=20;

    WITH cte

    AS (SELECT TOP 1 time_sched_start_dt,

    enddt

    FROM (SELECT T.*,

    T1.time_sched_start_dt

    strtdt,

    T1.time_sched_end_dt

    enddt,

    Datediff(minute, T1.time_sched_start_dt,

    T.time_sched_end_dt)

    - @duration diff

    FROM #return_schedule T1

    CROSS apply(SELECT *

    FROM #return_schedule T2

    WHERE T1.col_val = T2.col_val)T

    WHERE Datediff(minute, T.time_sched_start_dt,

    T1.time_sched_end_dt)

    >=

    @duration)T

    ORDER BY diff DESC)

    SELECT Min(time_sched_start_dt)time_sched_start_dt,

    Max(time_sched_end_dt) time_sched_end_dt,

    Sum(CONVERT(INT, col_val))

    FROM (SELECT *

    FROM #return_schedule

    WHERE time_sched_start_dt >= (SELECT time_sched_start_dt

    FROM cte)

    AND time_sched_end_dt <= (SELECT enddt

    FROM cte))t

    HAVING SUM(CONVERT(INT, col_val)) = 0

    Hopefully this one meets all the requirements.

    --------------------------------------------------------------------------------------------------
    I am just an another naive wannabe DBA trying to learn SQL Server

  • Removed as wrong solution

    Far away is close at hand in the images of elsewhere.
    Anon.

  • ChrisM@Work (6/24/2014)


    Change

    HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) > @duration

    to

    HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) >= @duration

    Here's more of my actual data. Results don't appear correct unless I am doing something wrong.

    create table #return_schedule

    (slot_col1 char(15), time_sched_start_dt datetime, time_sched_end_dt datetime)

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('3108337', '2014-06-23 00:00:00.000', '2014-06-23 00:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 00:45:00.000', '2014-06-23 01:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 01:30:00.000', '2014-06-23 02:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 02:15:00.000', '2014-06-23 03:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 03:00:00.000', '2014-06-23 03:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 03:45:00.000', '2014-06-23 04:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 04:30:00.000', '2014-06-23 05:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 05:15:00.000', '2014-06-23 06:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 06:00:00.000', '2014-06-23 06:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 06:45:00.000', '2014-06-23 07:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 07:30:00.000', '2014-06-23 08:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('CLOSED', '2014-06-23 08:15:00.000', '2014-06-23 09:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('CLOSED', '2014-06-23 09:00:00.000', '2014-06-23 09:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 09:45:00.000', '2014-06-23 10:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 10:30:00.000', '2014-06-23 11:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 11:15:00.000', '2014-06-23 12:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 12:00:00.000', '2014-06-23 12:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 12:45:00.000', '2014-06-23 13:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 13:30:00.000', '2014-06-23 14:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 14:15:00.000', '2014-06-23 15:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 15:00:00.000', '2014-06-23 15:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 15:45:00.000', '2014-06-23 16:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 16:30:00.000', '2014-06-23 17:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 17:15:00.000', '2014-06-23 18:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 18:00:00.000', '2014-06-23 18:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 18:45:00.000', '2014-06-23 19:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 19:30:00.000', '2014-06-23 20:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 20:15:00.000', '2014-06-23 21:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 21:00:00.000', '2014-06-23 21:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 21:45:00.000', '2014-06-23 22:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 22:30:00.000', '2014-06-23 23:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 23:15:00.000', '2014-06-24 00:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-24 00:00:00.000', '2014-06-24 00:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-24 00:45:00.000', '2014-06-24 01:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-24 01:30:00.000', '2014-06-24 02:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-24 02:15:00.000', '2014-06-24 03:00:00.000')

    select * from #return_schedule

    DECLARE @duration INT=20

    SELECT TOP 1

    time_sched_start_dt = MAX(CASE WHEN dir = 'S' THEN dt END),

    time_sched_end_dt = MAX(CASE WHEN dir = 'E' THEN dt END)

    FROM ( -- f

    SELECT dir, dt, Grouper = (ROW_NUMBER() OVER(ORDER BY dt, dir)-1)/2

    FROM ( -- e

    SELECT dir, dt, ct = COUNT(*) OVER(PARTITION BY dt)

    FROM ( -- d

    SELECT dir = 'S', dt = time_sched_start_dt

    FROM #return_schedule sr

    WHERE sr.slot_col1 = ''

    UNION ALL

    SELECT dir = 'E', dt = time_sched_end_dt

    FROM #return_schedule sr

    WHERE sr.slot_col1 = ''

    ) d

    ) e

    WHERE ct = 1

    ) f

    GROUP BY Grouper

    HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) >= @duration

    ORDER BY time_sched_start_dt

    drop table #return_schedule

  • Sachin Nandanwar (6/24/2014)


    Hello guys,

    As rightly pointed out by Chris the query I posted earlier were having some issues.Please see refined one.

    DECLARE @duration INT=20;

    WITH cte

    AS (SELECT TOP 1 time_sched_start_dt,

    enddt

    FROM (SELECT T.*,

    T1.time_sched_start_dt

    strtdt,

    T1.time_sched_end_dt

    enddt,

    Datediff(minute, T1.time_sched_start_dt,

    T.time_sched_end_dt)

    - @duration diff

    FROM #return_schedule T1

    CROSS apply(SELECT *

    FROM #return_schedule T2

    WHERE T1.col_val = T2.col_val)T

    WHERE Datediff(minute, T.time_sched_start_dt,

    T1.time_sched_end_dt)

    >=

    @duration)T

    ORDER BY diff DESC)

    SELECT Min(time_sched_start_dt)time_sched_start_dt,

    Max(time_sched_end_dt) time_sched_end_dt,

    Sum(CONVERT(INT, col_val))

    FROM (SELECT *

    FROM #return_schedule

    WHERE time_sched_start_dt >= (SELECT time_sched_start_dt

    FROM cte)

    AND time_sched_end_dt <= (SELECT enddt

    FROM cte))t

    HAVING SUM(CONVERT(INT, col_val)) = 0

    Hopefully this one meets all the requirements.

    This returns no results and I was expecting 2014-06-23 02:15:00.000, 2014-06-23 04:30:00.000

    create table #return_schedule

    (slot_col1 char(15), time_sched_start_dt datetime, time_sched_end_dt datetime)

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('3108337', '2014-06-23 00:00:00.000', '2014-06-23 00:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 00:45:00.000', '2014-06-23 01:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('123', '2014-06-23 01:30:00.000', '2014-06-23 02:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 02:15:00.000', '2014-06-23 03:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 03:00:00.000', '2014-06-23 03:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 03:45:00.000', '2014-06-23 04:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 04:30:00.000', '2014-06-23 05:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 05:15:00.000', '2014-06-23 06:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 06:00:00.000', '2014-06-23 06:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 06:45:00.000', '2014-06-23 07:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 07:30:00.000', '2014-06-23 08:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('CLOSED', '2014-06-23 08:15:00.000', '2014-06-23 09:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('CLOSED', '2014-06-23 09:00:00.000', '2014-06-23 09:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 09:45:00.000', '2014-06-23 10:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 10:30:00.000', '2014-06-23 11:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 11:15:00.000', '2014-06-23 12:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 12:00:00.000', '2014-06-23 12:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 12:45:00.000', '2014-06-23 13:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 13:30:00.000', '2014-06-23 14:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 14:15:00.000', '2014-06-23 15:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 15:00:00.000', '2014-06-23 15:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 15:45:00.000', '2014-06-23 16:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 16:30:00.000', '2014-06-23 17:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 17:15:00.000', '2014-06-23 18:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 18:00:00.000', '2014-06-23 18:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 18:45:00.000', '2014-06-23 19:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 19:30:00.000', '2014-06-23 20:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 20:15:00.000', '2014-06-23 21:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 21:00:00.000', '2014-06-23 21:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 21:45:00.000', '2014-06-23 22:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 22:30:00.000', '2014-06-23 23:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 23:15:00.000', '2014-06-24 00:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-24 00:00:00.000', '2014-06-24 00:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-24 00:45:00.000', '2014-06-24 01:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-24 01:30:00.000', '2014-06-24 02:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-24 02:15:00.000', '2014-06-24 03:00:00.000')

    select * from #return_schedule

    DECLARE @duration INT=120;

    WITH cte

    AS (SELECT TOP 1 time_sched_start_dt,

    enddt

    FROM (SELECT T.*,

    T1.time_sched_start_dt

    strtdt,

    T1.time_sched_end_dt

    enddt,

    Datediff(minute, T1.time_sched_start_dt,

    T.time_sched_end_dt)

    - @duration diff

    FROM #return_schedule T1

    CROSS apply(SELECT *

    FROM #return_schedule T2

    WHERE T1.slot_col1 = T2.slot_col1)T

    WHERE Datediff(minute, T.time_sched_start_dt,

    T1.time_sched_end_dt)

    >=

    @duration)T

    ORDER BY diff DESC)

    SELECT Min(time_sched_start_dt)time_sched_start_dt,

    Max(time_sched_end_dt) time_sched_end_dt,

    Sum(CONVERT(INT, slot_col1))

    FROM (SELECT *

    FROM #return_schedule

    WHERE time_sched_start_dt >= (SELECT time_sched_start_dt

    FROM cte)

    AND time_sched_end_dt <= (SELECT enddt

    FROM cte))t

    HAVING SUM(CONVERT(INT, slot_col1)) = 0

    drop table #return_schedule

  • David Burrows (6/24/2014)


    My solution

    ;WITH cte (RowID, col_val, time_sched_start_dt, time_sched_end_dt)

    AS (

    SELECT ROW_NUMBER() OVER (ORDER BY a.time_sched_start_dt),

    col_val, time_sched_start_dt, time_sched_end_dt

    FROM #return_schedule a

    ),

    cte2 (GroupID, time_sched_start_dt, time_sched_end_dt)

    AS (

    SELECT RowID-ROW_NUMBER() OVER (ORDER BY time_sched_start_dt),

    time_sched_start_dt, time_sched_end_dt

    FROM cte

    WHERE col_val = ''

    )

    SELECT TOP 1 GroupID,MIN(time_sched_start_dt), MAX(time_sched_end_dt)

    FROM cte2

    GROUP BY GroupID

    HAVING DATEDIFF(minute,MIN(time_sched_start_dt), MAX(time_sched_end_dt)) >= @Duration

    ORDER BY GroupID ASC

    Returns this:

    GroupID(No column name)(No column name)

    22014-06-23 02:15:00.0002014-06-23 08:15:00.000

    Would expect

    GroupID(No column name)(No column name)

    22014-06-23 02:15:00.0002014-06-23 04:30:00.000

    create table #return_schedule

    (slot_col1 char(15), time_sched_start_dt datetime, time_sched_end_dt datetime)

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('3108337', '2014-06-23 00:00:00.000', '2014-06-23 00:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 00:45:00.000', '2014-06-23 01:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('123', '2014-06-23 01:30:00.000', '2014-06-23 02:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 02:15:00.000', '2014-06-23 03:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 03:00:00.000', '2014-06-23 03:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 03:45:00.000', '2014-06-23 04:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 04:30:00.000', '2014-06-23 05:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 05:15:00.000', '2014-06-23 06:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 06:00:00.000', '2014-06-23 06:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 06:45:00.000', '2014-06-23 07:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 07:30:00.000', '2014-06-23 08:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('CLOSED', '2014-06-23 08:15:00.000', '2014-06-23 09:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('CLOSED', '2014-06-23 09:00:00.000', '2014-06-23 09:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 09:45:00.000', '2014-06-23 10:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 10:30:00.000', '2014-06-23 11:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 11:15:00.000', '2014-06-23 12:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 12:00:00.000', '2014-06-23 12:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 12:45:00.000', '2014-06-23 13:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 13:30:00.000', '2014-06-23 14:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 14:15:00.000', '2014-06-23 15:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 15:00:00.000', '2014-06-23 15:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 15:45:00.000', '2014-06-23 16:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 16:30:00.000', '2014-06-23 17:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 17:15:00.000', '2014-06-23 18:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 18:00:00.000', '2014-06-23 18:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 18:45:00.000', '2014-06-23 19:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 19:30:00.000', '2014-06-23 20:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 20:15:00.000', '2014-06-23 21:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 21:00:00.000', '2014-06-23 21:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 21:45:00.000', '2014-06-23 22:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 22:30:00.000', '2014-06-23 23:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-23 23:15:00.000', '2014-06-24 00:00:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-24 00:00:00.000', '2014-06-24 00:45:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-24 00:45:00.000', '2014-06-24 01:30:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-24 01:30:00.000', '2014-06-24 02:15:00.000')

    Insert Into #return_schedule

    (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '2014-06-24 02:15:00.000', '2014-06-24 03:00:00.000')

    select * from #return_schedule

    DECLARE @duration INT=120;

    ;WITH cte (RowID, slot_col1, time_sched_start_dt, time_sched_end_dt)

    AS (

    SELECT ROW_NUMBER() OVER (ORDER BY a.time_sched_start_dt),

    slot_col1, time_sched_start_dt, time_sched_end_dt

    FROM #return_schedule a

    ),

    cte2 (GroupID, time_sched_start_dt, time_sched_end_dt)

    AS (

    SELECT RowID-ROW_NUMBER() OVER (ORDER BY time_sched_start_dt),

    time_sched_start_dt, time_sched_end_dt

    FROM cte

    WHERE slot_col1 = ''

    )

    SELECT TOP 1 GroupID,MIN(time_sched_start_dt), MAX(time_sched_end_dt)

    FROM cte2

    GROUP BY GroupID

    HAVING DATEDIFF(minute,MIN(time_sched_start_dt), MAX(time_sched_end_dt)) >= @duration

    ORDER BY GroupID ASC

    drop table #return_schedule

  • Rog Saber (6/23/2014)


    I'm having trouble writing a query and could use some help please.

    I want to find the earliest time_sched_start_dt, time_sched_end_dt where col_val is null

    and the datediff between time_sched_start_dt and time_sched_end_dt is at least 60 minutes.

    Looks good to me:

    create table #return_schedule

    (slot_col1 char(15), time_sched_start_dt datetime, time_sched_end_dt datetime)

    Insert Into #return_schedule (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('3108337', '2014-06-23 00:00:00.000', '2014-06-23 00:45:00.000'),

    ('', '2014-06-23 00:45:00.000', '2014-06-23 01:30:00.000'),

    ('', '2014-06-23 01:30:00.000', '2014-06-23 02:15:00.000'),

    ('', '2014-06-23 02:15:00.000', '2014-06-23 03:00:00.000'),

    ('', '2014-06-23 03:00:00.000', '2014-06-23 03:45:00.000'),

    ('', '2014-06-23 03:45:00.000', '2014-06-23 04:30:00.000'),

    ('', '2014-06-23 04:30:00.000', '2014-06-23 05:15:00.000'),

    ('', '2014-06-23 05:15:00.000', '2014-06-23 06:00:00.000'),

    ('', '2014-06-23 06:00:00.000', '2014-06-23 06:45:00.000'),

    ('', '2014-06-23 06:45:00.000', '2014-06-23 07:30:00.000'),

    ('', '2014-06-23 07:30:00.000', '2014-06-23 08:15:00.000'),

    ('CLOSED', '2014-06-23 08:15:00.000', '2014-06-23 09:00:00.000'),

    ('CLOSED', '2014-06-23 09:00:00.000', '2014-06-23 09:45:00.000'),

    ('', '2014-06-23 09:45:00.000', '2014-06-23 10:30:00.000'),

    ('', '2014-06-23 10:30:00.000', '2014-06-23 11:15:00.000'),

    ('', '2014-06-23 11:15:00.000', '2014-06-23 12:00:00.000'),

    ('', '2014-06-23 12:00:00.000', '2014-06-23 12:45:00.000'),

    ('', '2014-06-23 12:45:00.000', '2014-06-23 13:30:00.000'),

    ('', '2014-06-23 13:30:00.000', '2014-06-23 14:15:00.000'),

    ('', '2014-06-23 14:15:00.000', '2014-06-23 15:00:00.000'),

    ('', '2014-06-23 15:00:00.000', '2014-06-23 15:45:00.000'),

    ('', '2014-06-23 15:45:00.000', '2014-06-23 16:30:00.000'),

    ('', '2014-06-23 16:30:00.000', '2014-06-23 17:15:00.000'),

    ('', '2014-06-23 17:15:00.000', '2014-06-23 18:00:00.000'),

    ('', '2014-06-23 18:00:00.000', '2014-06-23 18:45:00.000'),

    ('', '2014-06-23 18:45:00.000', '2014-06-23 19:30:00.000'),

    ('', '2014-06-23 19:30:00.000', '2014-06-23 20:15:00.000'),

    ('', '2014-06-23 20:15:00.000', '2014-06-23 21:00:00.000'),

    ('', '2014-06-23 21:00:00.000', '2014-06-23 21:45:00.000'),

    ('', '2014-06-23 21:45:00.000', '2014-06-23 22:30:00.000'),

    ('', '2014-06-23 22:30:00.000', '2014-06-23 23:15:00.000'),

    ('', '2014-06-23 23:15:00.000', '2014-06-24 00:00:00.000'),

    ('', '2014-06-24 00:00:00.000', '2014-06-24 00:45:00.000'),

    ('', '2014-06-24 00:45:00.000', '2014-06-24 01:30:00.000'),

    ('', '2014-06-24 01:30:00.000', '2014-06-24 02:15:00.000'),

    ('', '2014-06-24 02:15:00.000', '2014-06-24 03:00:00.000')

    select * from #return_schedule

    DECLARE @duration INT=20

    SELECT TOP 1

    time_sched_start_dt = MAX(CASE WHEN dir = 'S' THEN dt END),

    time_sched_end_dt = MAX(CASE WHEN dir = 'E' THEN dt END)

    FROM ( -- f

    SELECT dir, dt, Grouper = (ROW_NUMBER() OVER(ORDER BY dt, dir)-1)/2

    FROM ( -- e

    SELECT dir, dt, ct = COUNT(*) OVER(PARTITION BY dt)

    FROM ( -- d

    SELECT dir = 'S', dt = time_sched_start_dt

    FROM #return_schedule sr

    WHERE sr.slot_col1 = ''

    UNION ALL

    SELECT dir = 'E', dt = time_sched_end_dt

    FROM #return_schedule sr

    WHERE sr.slot_col1 = ''

    ) d

    ) e

    WHERE ct = 1

    ) f

    GROUP BY Grouper

    HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) >= @duration

    ORDER BY time_sched_start_dt

    -- Result

    -- 2014-06-23 00:45:00.000- 2014-06-23 08:15:00.000

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work (6/24/2014)


    Rog Saber (6/23/2014)


    I'm having trouble writing a query and could use some help please.

    I want to find the earliest time_sched_start_dt, time_sched_end_dt where col_val is null

    and the datediff between time_sched_start_dt and time_sched_end_dt is at least 60 minutes.

    Looks good to me:

    create table #return_schedule

    (slot_col1 char(15), time_sched_start_dt datetime, time_sched_end_dt datetime)

    Insert Into #return_schedule (slot_col1 , time_sched_start_dt, time_sched_end_dt)

    Values

    ('3108337', '2014-06-23 00:00:00.000', '2014-06-23 00:45:00.000'),

    ('', '2014-06-23 00:45:00.000', '2014-06-23 01:30:00.000'),

    ('', '2014-06-23 01:30:00.000', '2014-06-23 02:15:00.000'),

    ('', '2014-06-23 02:15:00.000', '2014-06-23 03:00:00.000'),

    ('', '2014-06-23 03:00:00.000', '2014-06-23 03:45:00.000'),

    ('', '2014-06-23 03:45:00.000', '2014-06-23 04:30:00.000'),

    ('', '2014-06-23 04:30:00.000', '2014-06-23 05:15:00.000'),

    ('', '2014-06-23 05:15:00.000', '2014-06-23 06:00:00.000'),

    ('', '2014-06-23 06:00:00.000', '2014-06-23 06:45:00.000'),

    ('', '2014-06-23 06:45:00.000', '2014-06-23 07:30:00.000'),

    ('', '2014-06-23 07:30:00.000', '2014-06-23 08:15:00.000'),

    ('CLOSED', '2014-06-23 08:15:00.000', '2014-06-23 09:00:00.000'),

    ('CLOSED', '2014-06-23 09:00:00.000', '2014-06-23 09:45:00.000'),

    ('', '2014-06-23 09:45:00.000', '2014-06-23 10:30:00.000'),

    ('', '2014-06-23 10:30:00.000', '2014-06-23 11:15:00.000'),

    ('', '2014-06-23 11:15:00.000', '2014-06-23 12:00:00.000'),

    ('', '2014-06-23 12:00:00.000', '2014-06-23 12:45:00.000'),

    ('', '2014-06-23 12:45:00.000', '2014-06-23 13:30:00.000'),

    ('', '2014-06-23 13:30:00.000', '2014-06-23 14:15:00.000'),

    ('', '2014-06-23 14:15:00.000', '2014-06-23 15:00:00.000'),

    ('', '2014-06-23 15:00:00.000', '2014-06-23 15:45:00.000'),

    ('', '2014-06-23 15:45:00.000', '2014-06-23 16:30:00.000'),

    ('', '2014-06-23 16:30:00.000', '2014-06-23 17:15:00.000'),

    ('', '2014-06-23 17:15:00.000', '2014-06-23 18:00:00.000'),

    ('', '2014-06-23 18:00:00.000', '2014-06-23 18:45:00.000'),

    ('', '2014-06-23 18:45:00.000', '2014-06-23 19:30:00.000'),

    ('', '2014-06-23 19:30:00.000', '2014-06-23 20:15:00.000'),

    ('', '2014-06-23 20:15:00.000', '2014-06-23 21:00:00.000'),

    ('', '2014-06-23 21:00:00.000', '2014-06-23 21:45:00.000'),

    ('', '2014-06-23 21:45:00.000', '2014-06-23 22:30:00.000'),

    ('', '2014-06-23 22:30:00.000', '2014-06-23 23:15:00.000'),

    ('', '2014-06-23 23:15:00.000', '2014-06-24 00:00:00.000'),

    ('', '2014-06-24 00:00:00.000', '2014-06-24 00:45:00.000'),

    ('', '2014-06-24 00:45:00.000', '2014-06-24 01:30:00.000'),

    ('', '2014-06-24 01:30:00.000', '2014-06-24 02:15:00.000'),

    ('', '2014-06-24 02:15:00.000', '2014-06-24 03:00:00.000')

    select * from #return_schedule

    DECLARE @duration INT=20

    SELECT TOP 1

    time_sched_start_dt = MAX(CASE WHEN dir = 'S' THEN dt END),

    time_sched_end_dt = MAX(CASE WHEN dir = 'E' THEN dt END)

    FROM ( -- f

    SELECT dir, dt, Grouper = (ROW_NUMBER() OVER(ORDER BY dt, dir)-1)/2

    FROM ( -- e

    SELECT dir, dt, ct = COUNT(*) OVER(PARTITION BY dt)

    FROM ( -- d

    SELECT dir = 'S', dt = time_sched_start_dt

    FROM #return_schedule sr

    WHERE sr.slot_col1 = ''

    UNION ALL

    SELECT dir = 'E', dt = time_sched_end_dt

    FROM #return_schedule sr

    WHERE sr.slot_col1 = ''

    ) d

    ) e

    WHERE ct = 1

    ) f

    GROUP BY Grouper

    HAVING DATEDIFF(MINUTE,MAX(CASE WHEN dir = 'S' THEN dt END),MAX(CASE WHEN dir = 'E' THEN dt END)) >= @duration

    ORDER BY time_sched_start_dt

    -- Result

    -- 2014-06-23 00:45:00.000- 2014-06-23 08:15:00.000

    Here is your result:

    -- Result

    -- 2014-06-23 00:45:00.000- 2014-06-23 08:15:00.000

    I would expect:

    -- Result

    -- 2014-06-23 00:45:00.000- 2014-06-23 01:30:00.000

  • ;WITH cte (RowID, slot_col1, time_sched_start_dt, time_sched_end_dt)

    AS (

    SELECT ROW_NUMBER() OVER (ORDER BY a.time_sched_start_dt),

    slot_col1, time_sched_start_dt, time_sched_end_dt

    FROM #return_schedule a

    ),

    cte2 (GroupID, time_sched_start_dt, time_sched_end_dt)

    AS (

    SELECT RowID-ROW_NUMBER() OVER (ORDER BY time_sched_start_dt),

    time_sched_start_dt, time_sched_end_dt

    FROM cte

    WHERE slot_col1 = ''

    )

    SELECT TOP 1 MIN(b.time_sched_start_dt),a.time_sched_end_dt

    FROM cte2 a

    LEFT JOIN cte2 b ON b.GroupID = a.GroupID AND b.time_sched_start_dt < a.time_sched_end_dt

    GROUP BY a.GroupID,a.time_sched_end_dt

    HAVING DATEDIFF(minute,MIN(b.time_sched_start_dt),a.time_sched_end_dt) >= @Duration

    ORDER BY a.GroupID ASC,a.time_sched_end_dt ASC

    Far away is close at hand in the images of elsewhere.
    Anon.

  • David Burrows (6/24/2014)


    ;WITH cte (RowID, slot_col1, time_sched_start_dt, time_sched_end_dt)

    AS (

    SELECT ROW_NUMBER() OVER (ORDER BY a.time_sched_start_dt),

    slot_col1, time_sched_start_dt, time_sched_end_dt

    FROM #return_schedule a

    ),

    cte2 (GroupID, time_sched_start_dt, time_sched_end_dt)

    AS (

    SELECT RowID-ROW_NUMBER() OVER (ORDER BY time_sched_start_dt),

    time_sched_start_dt, time_sched_end_dt

    FROM cte

    WHERE slot_col1 = ''

    )

    SELECT TOP 1 MIN(b.time_sched_start_dt),a.time_sched_end_dt

    FROM cte2 a

    LEFT JOIN cte2 b ON b.GroupID = a.GroupID AND b.time_sched_start_dt < a.time_sched_end_dt

    GROUP BY a.GroupID,a.time_sched_end_dt

    HAVING DATEDIFF(minute,MIN(b.time_sched_start_dt),a.time_sched_end_dt) >= @Duration

    ORDER BY a.GroupID ASC,a.time_sched_end_dt ASC

    I have run this with different criteria and it appears to work great - thank you very much.

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply