# 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 StartSlotfrom #T),cte2 as (select *,       sum(StartSlot) over(partition by CwsCalID order by StartDateTime rows unbounded preceding) as grpfrom cte1)select CwsCalID,       min(StartDateTime) as ApptStart,       max(EndDateTime) as ApptEnd,	   datediff(minute,min(StartDateTime),max(EndDateTime)) as ApptLength,	   count(*) as NumberOfSlotsfrom cte2where TimeSlotAvailable = 1group by CwsCalID,grphaving count(*) > 1 -- Require at least 2 consecutive slotsorder by ApptStart;`

____________________________________________________

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

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) dORDER 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]