How to get datediff between two different columns and rows

  • NineIron

    SSChampion

    Points: 12523

    The temp table represents a physician's schedule. I need to find available time slots for 30 and 45 minute appointments. Available time slots are identified by the number 1. If you order by the StartDateTime, then you can see consecutive available time slots, i.e. a number 1 in two consecutive rows. So, how do you get a datediff between the StartDateTime and EndDateTime between two consecutive rows for 30 minute appointment. Then, how to get datediff between three consecutive rows for 45 minute appointments?

    create table #T
    (
    CwsCalID varchar(50),
    StartDateTime datetime,
    EndDateTime datetime,
    TimeSlotAvailable int
    )

    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 06:00:00','04/13/2020 06:15:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 06:15:00','04/13/2020 06:30:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 06:30:00','04/13/2020 06:45:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 06:45:00','04/13/2020 07:00:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 07:00:00','04/13/2020 07:15:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 07:15:00','04/13/2020 07:30:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 07:30:00','04/13/2020 07:45:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 08:15:00','04/13/2020 08:30:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 08:30:00','04/13/2020 08:45:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 08:45:00','04/13/2020 09:00:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 09:00:00','04/13/2020 09:15:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 09:15:00','04/13/2020 09:30:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 09:30:00','04/13/2020 09:45:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 09:45:00','04/13/2020 10:00:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 10:00:00','04/13/2020 10:15:00','1')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 10:15:00','04/13/2020 10:30:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 10:30:00','04/13/2020 10:45:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 10:45:00','04/13/2020 11:00:00','1')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 11:00:00','04/13/2020 11:15:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 11:15:00','04/13/2020 11:30:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 11:30:00','04/13/2020 11:45:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 11:45:00','04/13/2020 12:00:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 12:00:00','04/13/2020 12:15:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 12:15:00','04/13/2020 12:30:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 12:30:00','04/13/2020 12:45:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 12:45:00','04/13/2020 13:00:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 13:00:00','04/13/2020 13:15:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 13:15:00','04/13/2020 13:30:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 13:30:00','04/13/2020 13:45:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 13:45:00','04/13/2020 14:00:00','1')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 14:00:00','04/13/2020 14:15:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 14:15:00','04/13/2020 14:30:00','1')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 14:30:00','04/13/2020 14:45:00','1')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 14:45:00','04/13/2020 15:00:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 15:00:00','04/13/2020 15:15:00','1')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 15:15:00','04/13/2020 15:30:00','1')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 15:30:00','04/13/2020 15:45:00','0')
    insert into #T(CwsCalID,StartDateTime, EndDateTime, TimeSlotAvailable) values('PAPETTIN_20200413','04/13/2020 15:45:00','04/13/2020 16:00:00','0')
  • Mark Cowne

    One Orange Chip

    Points: 26748

    Looks like a gaps and islands problem

    with cte1 as (
    select *,case when TimeSlotAvailable = LAG(TimeSlotAvailable) over(partition by CwsCalID order by StartDateTime) then 0 else 1 end as StartSlot
    from #T
    ),
    cte2 as (
    select *,
    sum(StartSlot) over(partition by CwsCalID order by StartDateTime rows unbounded preceding) as grp
    from cte1)
    select CwsCalID,
    min(StartDateTime) as ApptStart,
    max(EndDateTime) as ApptEnd,
    datediff(minute,min(StartDateTime),max(EndDateTime)) as ApptLength,
    count(*) as NumberOfSlots
    from cte2
    where TimeSlotAvailable = 1
    group by CwsCalID,grp
    having count(*) > 1 -- Require at least 2 consecutive slots
    order by ApptStart;

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • ChrisM@Work

    SSC Guru

    Points: 186094

    SELECT 
    CwsCalID, StartDateTime, EndDateTime, TimeSlotAvailable,
    [ConsecutiveAvailableTimeslots] = COUNT(NULLIF(TimeSlotAvailable,0)) OVER(PARTITION BY Grouper)
    FROM (
    SELECT *,
    Grouper = ROW_NUMBER() OVER(ORDER BY TimeSlotAvailable, StartDateTime) - ROW_NUMBER() OVER(ORDER BY StartDateTime)
    FROM #T
    ) d
    ORDER BY StartDateTime

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


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

Viewing 3 posts - 1 through 3 (of 3 total)

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