Geek Answers Handbook

Combining unused time slots

I have a query result like

|----------------------------------------------| |StaffId|BranchId|StartTime|EndTime |PatientId | |----------------------------------------------| |1 |1 |09:30:00 |09:35:00|Null | |1 |1 |09:35:00 |09:40:00|Null | |1 |1 |09:40:00 |09:55:00|1 | |1 |1 |09:55:00 |10:00:00|Null | |1 |1 |10:00:00 |10:05:00|Null | |1 |1 |10:05:00 |10:20:00|2 | |1 |1 |10:20:00 |10:25:00|NULL | |1 |1 |10:25:00 |10:40:00|3 | |1 |1 |10:40:00 |10:45:00|Null | |1 |1 |10:45:00 |10:50:00|Null | |1 |1 |10:50:00 |10:55:00|Null | |1 |1 |10:55:00 |11:00:00|Null | |----------------------------------------------|

but I want to be able to group all unused time intervals (those that do not have a patient ID), so the result

 |----------------------------------------------| |StaffId|BranchId|StartTime|EndTime |PatientId | |----------------------------------------------| |1 |1 |09:30:00 |09:40:00|Null | |1 |1 |09:40:00 |09:55:00|1 | |1 |1 |09:55:00 |10:05:00|Null | |1 |1 |10:05:00 |10:20:00|2 | |1 |1 |10:20:00 |10:25:00|NULL | |1 |1 |10:25:00 |10:40:00|3 | |1 |1 |10:40:00 |11:00:00|Null | |----------------------------------------------|

Not quite sure how to do this, but any recommendations would be appreciated. Not sure if this is simpler, but query results were obtained by connecting the table at 5 minute intervals with patients using sql

 INSERT INTO @Results(BranchId, StaffId, StartTime, EndTime) SELECT us.BranchId, us.StaffId, us.StartTime, us.EndTime FROM @UnusedSlots us left join @Results r on (NOT ((r.StartTime >= us.EndTime) OR (r.EndTime <= us.StartTime))) AND (us.StaffId = r.StaffId) where r.BranchId is Null

Where @UnusedSlots are available 5-minute slots and @Results contains reserved slots (with patient identifiers). If there is a way to build a merge into this statement, it will be much better.

I thought that I could use <T23> and Max(EndTime) , if I can highlight adjacent results without PatientId, but I'm not sure how to do this, I will end up with the one that merged with 09: from 30 until 11:00

0
source share
2 answers
 select StaffId, BranchId, MIN(StartTime), MAX(EndTime), PatientId from ( select *, ROW_NUMBER() over (order by starttime) - ROW_NUMBER() over (partition by isnull(PatientID,-1) order by starttime) rn from @results ) v group by staffid, branchid, patientid, rn order by MIN (starttime)

I'm not sure if StaffID and BranchID may differ in the @results table, so you might need to configure it if they can.

+3
source

you can use the cursor, performance will not scale too well though:

  create table #tempTimes ( staffid int, branchid int, startTime datetime, endTime datetime, patientid int) insert into #temptimes values (1,1,'2012-09-09 09:30', '2012-09-09 09:35', null) insert into #temptimes values (1,1,'2012-09-09 09:35', '2012-09-09 09:40', null) insert into #temptimes values (1,1,'2012-09-09 09:40', '2012-09-09 09:45', null) insert into #temptimes values (1,1,'2012-09-09 09:45', '2012-09-09 09:55', 1) insert into #temptimes values (1,1,'2012-09-09 09:55', '2012-09-09 10:00', null) insert into #temptimes values (1,1,'2012-09-09 10:00', '2012-09-09 10:05', null) insert into #temptimes values (1,1,'2012-09-09 10:05', '2012-09-09 10:20', 2) declare @currentPeriodStart datetime declare @currentPeriodEnd datetime declare @startTime datetime declare @endTime datetime declare @lastEndTime datetime declare @patientId int declare myCurs cursor for select startTime, endTime, patientid from #tempTimes order by startTime open myCurs fetch next from myCurs into @startTime, @endTime, @patientId while @@fetch_status = 0 begin if @patientId is null begin if (@currentPeriodStart is null) set @currentPeriodStart = @startTime set @currentPeriodEnd = @endTime end else begin print cast(@currentPeriodStart as nvarchar) + ' to ' + cast(@currentPeriodEnd as nvarchar) set @currentPeriodStart = null end fetch next from myCurs into @startTime, @endTime, @patientId end close myCurs deallocate myCurs
0
source

More articles:


All Articles