How to get datediff between two different columns and rows

Question

How to get datediff between two different columns and rows

SSChampion

Points: 12536

March 5, 2020 at 12:39 pm

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

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

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

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: 186127 More actions · Answer 2

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

^{“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