Forum Replies Created

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

  • RE: Help finding a date

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

  • RE: Help finding a date

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

  • RE: Help finding a date

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

  • RE: Help finding a date

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

  • RE: Help finding a date

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

  • RE: Help finding a date

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

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

  • RE: Help finding a date

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

  • RE: Help finding a date

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

  • RE: Help finding a date

    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

    ...

  • RE: Help finding a date

    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

  • RE: Schedule table with open/closed slots

    I will give it a try thank you.

  • RE: Dynamic SQL

    These work great. Thank you all.

  • RE: Dynamic SQL

    OK I think I figured it out now.

  • RE: Are cursors hard on servers?

    OK thank you all for the suggestions.

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