Iterating through a schedule with recursions and factors

Transact-SQL
1

I have a scenario in which a schedule is recorded like the top table below. Notice the start and end times, the meeting length, and the fact that you could book more than 1 meeting (book factor) during the times slot. The second table is the result needed. I have it working using the dreaded cursor, but I know there's got to be a more elegant solutions. Surely someone else has run into something similar?

empID	bookFactor	mtgLen	mtgStart	  mtgEnd
    1	2	         15	7/1/2015 8:00	   7/1/2015 8:15
    1	1	         15	 7/1/2015 10:00    7/1/2015 11:00
    2	1	         30	 7/1/2015 10:00	   7/1/2015 11:00
    2	2	         20	 7/2/2015 9:00	   7/2/2015 9:20
    3	1	         20	 7/2/2015 10:00	   7/1/2015 10:20

empID	schedule
  1	7/1/2015 8:00
  1	7/1/2015 8:00
  1	7/1/2015 10:00
  1	7/1/2015 10:15
  1	7/1/2015 10:30
  1	7/1/2015 10:45
  2	7/1/2015 10:00
  2	7/1/2015 10:30
  2	7/2/2015 9:00
  3	7/2/2015 10:00
2

Started working on this then realized I don't understand why these entries are in the results. You have a 15 minute meeting booked at 10 for empid=1. what's up with 10:15, 10:30 and 10:45?

3

Sorry - should have pointed it out a little more concisely. The schedule could be entered as a time block, but needs to be broken up by the indicated meeting length and then times the book factor. Thus, a meeting starting at 10:00 but ending at 10:30 with a 15 minute length is actually two meetings - 1 starting at 10:00 and one starting at 10:15. If the booking factor was 2, that would mean 4 meetings 2 at 10:00 and 2 at 10:15.

I apologize for any confusion. Does that help?

4

that's what I figured, but the meeting for empid 1 at 10 am is just 15 minutes long. so where do the 10:15, 10:30 and 10:45 meetings come from?

5

It ends at 11:00....

6

Then column mtglen is wrong?

7

OK -- this is a possible solution:

declare @ table(empID int,	bookFactor int,	mtgLen int,	mtgStart datetime,mtgEnd datetime);
insert into @(empID, bookFactor, mtgLen, mtgStart, mtgEnd) values    
    (1, 2, 15, '7/1/2015 8:00','7/1/2015 8:15   '),
    (1, 1, 15, '7/1/2015 10:00','7/1/2015 11:00 '),
    (2, 1, 30, '7/1/2015 10:00','7/1/2015 11:00 '),
    (2, 2, 20, '7/2/2015 9:00','7/2/2015 9:20   '),
    (3, 1, 20, '7/2/2015 10:00','7/1/2015 10:20 ')
;

with N(n) as 
    ( select (row_number() over(order by (select 1))) -1
      from sys.columns, sys.columns _
    )

select empID
     , dateadd(minute, q.n*mtgLen, mtgStart) 
        AS Schedule
from @ t
join N on t.bookFactor > N.n
Join N q on q.n < floor((datediff(minute, mtgStart, mtgEnd)+mtgLen-1)/mtgLen)
order by empid, Schedule

Note that the data for emp 3 looks wrong, since the end date precedes the start date.

1 Like
8

You're so right - it was a typo on the last one. Thanks - this is lovely. Sorry for the delay - I've been going through it and making sense. Some days my head feels like the rotating Reagan from the Exorcist. Thanks so much.

9

Reagan wasn't in The Exorcist, you must be thinking of Hillary.

1 Like