SQL server total/pivot

Question

Hi fellow stackers,

I have a question about how to “pivot/total” (for want of a better word) some data around in SQL server. The data is basically staff shifts and then hours lost from those shifts. So for example I have a record in one table

Staff_ID    Shift_start    Shift_end
37        09:00          17:30

And then we would give that person a lunch in another table

Staff_ID        Start       End
37           13:00        14:00

Of course we have more agents throughout the day and the scheme above is simplified but you get the idea. This is then transformed into the number of staff in 15 minutes

Interval    Staff
09:00       5
09:15       7
09:30       6

And so on.

At the moment SQL server stores all of this but to “total” the agents up I have to bring things into Access and using arrays work out the number of staff in each 15 minute period and then save this data back to the database. It’s a quick process (<1500ms) but what I’m looking for is a way to do this in SQL server itself and not have to bring things to Access and write it back.

Am I barking up the wrong tree with this one?

EDIT:

I'm using SQL server 2008R2 Express

Answer 1

Update

Try this:

;with Intervals(start) as --00:00 - 23:45
(
select dateadd(minute, 0,0)
union all
select dateadd(minute, 15, start) from Intervals
where datediff(hour, 0, dateadd(minute, 15, start))<24
)
select convert(varchar, i.start, 108) [Interval], count(*) [Staff]
from Intervals i
join 
(
    select cast('09:31:29' as datetime) [start], cast('17:11:29' as datetime) [end] union all
    select cast('10:43:12' as datetime), cast('18:21:29' as datetime)  union all
    select cast('11:59:53' as datetime), cast('19:51:29' as datetime)  
)s
on cast(convert(varchar(10), s.start, 108)as datetime) <= i.start
and dateadd(minute, 15, i.start)  <= cast(convert(varchar(10), s.[end], 108) as datetime) 
group by convert(varchar, i.start, 108)

Answer 2

You have a few approaches you could try. One would be to take exactly what you have in Access and convert it to SQL. If there's part of that you're not sure how to do, post it here and we can help.

Another would be to use a cursor (as opposed to a single set function) to iterate through either each 15 minute time period, loading staff working during that period, or each staff person's schedule, populating all of their working 15-minute time periods. You may be doing this in Access already, I can't tell.

Since they're the same 15-minute periods each day, you can store the times in a table and do an outer join on them, but the performance could be worse than your Access process.