Help finding a date

  • 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.

    Given the data below, I would want to return

    time_sched_start_dt = 2014-06-23 10:45:00.000

    time_sched_end_dt = 2014-06-23 11:45:00.000

    create table #return_schedule

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

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('1', '6/23/2014 08:30:00', '6/23/2014 09:15:00')

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '6/23/2014 09:15:00', '6/23/2014 10:00:00')

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('2', '6/23/2014 10:00:00', '6/23/2014 10:45:00')

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '6/23/2014 10:45:00', '6/23/2014 11:30:00')

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '6/23/2014 11:30:00', '6/23/2014 12:15:00')

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('3', '6/23/2014 12:15:00', '6/23/2014 13:00:00')

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '6/23/2014 13:00:00', '6/23/2014 13:45:00')

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '6/23/2014 13:45:00', '6/23/2014 14:30:00')

    select * from #return_schedule

    drop table #return_schedule

  • Something like this?

    select TOP 1 t.* from #return_schedule t

    where COALESCE(t.col_val,0)=0

    AND DATEDIFF(minute, t.time_sched_end_dt,t.time_sched_start_dt)>= 60

    ORDER BY t.time_sched_start_dt ASC, t.time_sched_end_dt ASC

  • Not the most efficient way to do it, but I'm sure someone will come along with a better solution (or I may given more time):

    with base as (

    select

    rs1.time_sched_start_dt,

    rs2.time_sched_end_dt,

    rn = row_number() over (order by datediff(minute,rs1.time_sched_start_dt,rs2.time_sched_end_dt) asc, rs1.time_sched_start_dt)

    from

    #return_schedule rs1

    cross join #return_schedule rs2

    where

    (rs1.col_val = '' and rs2.col_val = '') and

    datediff(minute,rs1.time_sched_start_dt,rs2.time_sched_end_dt) >= 60

    )

    select time_sched_start_dt, time_sched_end_dt from base where rn = 1;

  • BOR15K (6/23/2014)


    Something like this?

    select TOP 1 t.* from #return_schedule t

    where COALESCE(t.col_val,0)=0

    AND DATEDIFF(minute, t.time_sched_end_dt,t.time_sched_start_dt)>= 60

    ORDER BY t.time_sched_start_dt ASC, t.time_sched_end_dt ASC

    I will give this a try

  • Lynn Pettis (6/23/2014)


    Not the most efficient way to do it, but I'm sure someone will come along with a better solution (or I may given more time):

    with base as (

    select

    rs1.time_sched_start_dt,

    rs2.time_sched_end_dt,

    rn = row_number() over (order by datediff(minute,rs1.time_sched_start_dt,rs2.time_sched_end_dt) asc, rs1.time_sched_start_dt)

    from

    #return_schedule rs1

    cross join #return_schedule rs2

    where

    (rs1.col_val = '' and rs2.col_val = '') and

    datediff(minute,rs1.time_sched_start_dt,rs2.time_sched_end_dt) >= 60

    )

    select time_sched_start_dt, time_sched_end_dt from base where rn = 1;

    This is OK until I change the query to >=70. Then it returns 9:15, 11:00. I cannot use 9:15 because there is a value in the col_val column at 10:00.

    I need the earliest start time and the earliest end time where there is no value in col_val. If I change the datediff to 70 I would expect a return of 12:30, 02:00.

  • Rog Saber (6/23/2014)


    Lynn Pettis (6/23/2014)


    Not the most efficient way to do it, but I'm sure someone will come along with a better solution (or I may given more time):

    with base as (

    select

    rs1.time_sched_start_dt,

    rs2.time_sched_end_dt,

    rn = row_number() over (order by datediff(minute,rs1.time_sched_start_dt,rs2.time_sched_end_dt) asc, rs1.time_sched_start_dt)

    from

    #return_schedule rs1

    cross join #return_schedule rs2

    where

    (rs1.col_val = '' and rs2.col_val = '') and

    datediff(minute,rs1.time_sched_start_dt,rs2.time_sched_end_dt) >= 60

    )

    select time_sched_start_dt, time_sched_end_dt from base where rn = 1;

    This is OK until I change the query to >=70. Then it returns 9:15, 11:00. I cannot use 9:15 because there is a value in the col_val column at 10:00.

    I need the earliest start time and the earliest end time where there is no value in col_val. If I change the datediff to 70 I would expect a return of 12:30, 02:00.

    Well, you really need to be more specific in your requirements. From what I read I did not take into account values in col_val that separated values in start_time and end-time. Based on my reading I simply excluded those rows of data that col_val was not ''.

    Guess you should see what mods are needed to make this code meet the change in specs.

  • Lynn Pettis (6/23/2014)


    Rog Saber (6/23/2014)


    Lynn Pettis (6/23/2014)


    Not the most efficient way to do it, but I'm sure someone will come along with a better solution (or I may given more time):

    with base as (

    select

    rs1.time_sched_start_dt,

    rs2.time_sched_end_dt,

    rn = row_number() over (order by datediff(minute,rs1.time_sched_start_dt,rs2.time_sched_end_dt) asc, rs1.time_sched_start_dt)

    from

    #return_schedule rs1

    cross join #return_schedule rs2

    where

    (rs1.col_val = '' and rs2.col_val = '') and

    datediff(minute,rs1.time_sched_start_dt,rs2.time_sched_end_dt) >= 60

    )

    select time_sched_start_dt, time_sched_end_dt from base where rn = 1;

    This is OK until I change the query to >=70. Then it returns 9:15, 11:00. I cannot use 9:15 because there is a value in the col_val column at 10:00.

    I need the earliest start time and the earliest end time where there is no value in col_val. If I change the datediff to 70 I would expect a return of 12:30, 02:00.

    Well, you really need to be more specific in your requirements. From what I read I did not take into account values in col_val that separated values in start_time and end-time. Based on my reading I simply excluded those rows of data that col_val was not ''.

    Guess you should see what mods are needed to make this code meet the change in specs.

    I will try this again. I think I screwed up in my example. Thank you.

    What I am trying to do is fill in a time slot. For example, from 8:30 - 9:15 my time slot is taken with col_val = '1'. From 10:00 - 10:45 my time slot is taken with col_val = '2'. from 11:45 - 12:30 my time slot is taken with col_val = '3'. I should have given better tables values as in below.

    If the duration of my appointment is 60 minutes, I would expect 10:45 - 11:45.

    If the duration of my appointment is 70 minutes, I would expect 12:30 - 14:00.

    create table #return_schedule

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

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('1', '6/23/2014 08:30:00', '6/23/2014 09:15:00')

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '6/23/2014 09:15:00', '6/23/2014 10:00:00')

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('2', '6/23/2014 10:00:00', '6/23/2014 10:45:00')

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '6/23/2014 10:45:00', '6/23/2014 11:00:00')

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '6/23/2014 11:00:00', '6/23/2014 11:45:00')

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('3', '6/23/2014 11:45:00', '6/23/2014 12:30:00')

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '6/23/2014 12:30:00', '6/23/2014 13:15:00')

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '6/23/2014 13:15:00', '6/23/2014 14:00:00')

  • The query is purely based on the sample data provided and all the assumptions stated in the earlier posts.

    See if this helps.

    DECLARE @duration INT=70

    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

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

  • Sachin Nandanwar (6/24/2014)


    The query is purely based on the sample data provided and all the assumptions stated in the earlier posts.

    See if this helps.

    DECLARE @duration INT=70

    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

    With a duration of 20, this returns an incorrect timeslot (2014-06-23 08:30:00.000 - 2014-06-23 09: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

  • 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.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

    “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)


    With a duration of 20, this returns an incorrect timeslot (2014-06-23 08:30:00.000 - 2014-06-23 09:15:00.000)

    No it returns

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

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

  • Sachin Nandanwar (6/24/2014)


    ChrisM@Work (6/24/2014)


    With a duration of 20, this returns an incorrect timeslot (2014-06-23 08:30:00.000 - 2014-06-23 09:15:00.000)

    No it returns

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

    Sorry, my mistake, I extended the dataset to add an extra day.

    I like your solution. For this type of problem it shows some promise compared to the method I posted, which is derived from an IBG method - and is far more complex, though only about 2x slower. There are some issues with your solution however. Using the parameter of 20, it returns 7 rows with the same value of diff - and TOP is only returning the correct row by accident, not by design. You can check this by commenting out TOP and including diff in the output set. I'd be interested to see if you can fix this.

    “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

  • Sachin Nandanwar (6/24/2014)


    ChrisM@Work (6/24/2014)


    With a duration of 20, this returns an incorrect timeslot (2014-06-23 08:30:00.000 - 2014-06-23 09:15:00.000)

    No it returns

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

    Try also DECLARE @duration INT = 120, which should return no rows.

    “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

  • Here is what I tried but I believe I am still having an issue. If I put the duration at 120 minutes it returns:

    time_sched_start_dtenddt

    2014-06-23 09:15:00.0002014-06-23 11:30:00.000

    I don't want it to return this result because a timeslot ('2') is taken at from 10:00 to 10:45.

    create table #return_schedule

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

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('1', '6/23/2014 08:30:00', '6/23/2014 09:15:00')

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '6/23/2014 09:15:00', '6/23/2014 10:00:00')

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('2', '6/23/2014 10:00:00', '6/23/2014 10:45:00')

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '6/23/2014 10:45:00', '6/23/2014 11:30:00')

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '6/23/2014 11:30:00', '6/23/2014 12:15:00')

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('3', '6/23/2014 12:15:00', '6/23/2014 13:00:00')

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '6/23/2014 13:00:00', '6/23/2014 13:45:00')

    Insert Into #return_schedule

    (col_val , time_sched_start_dt, time_sched_end_dt)

    Values

    ('', '6/23/2014 13:45:00', '6/23/2014 14:30:00')

    select * from #return_schedule

    DECLARE @duration INT=120

    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

    drop table #return_schedule

  • Rog Saber (6/24/2014)


    Here is what I tried but I believe I am still having an issue. If I put the duration at 120 minutes it returns:

    time_sched_start_dtenddt

    2014-06-23 09:15:00.0002014-06-23 11:30:00.000

    I don't want it to return this result because a timeslot ('2') is taken at from 10:00 to 10:45..

    .

    .

    There are some issues with Sachin's query, as pointed out earlier.

    “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

Viewing 15 posts - 1 through 15 (of 26 total)

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