SQL Server: querying cross referenced against dataset not stored in tables?

Question

I have (adjusted for the sake of simplicity) a table containing 2 columns - start and end, both of which are time stamps. These represent phone calls.

I need to produce what boils down to a graph, with time along the X axis, and simultaneous calls along the Y axis.

I can extract the number of concurrent calls at any given time quite easily, of course - with something like this:

select
    count(*)
from
    calls_table
where
    time_started < 1282854000 and time_ended > 1282854000

That just gives me the numbers back for the particular time. What I need to be able to do is include a selection of times in my query (for example every hour on the hour) and pull the count of simultaneous calls for that time stamp? All great ideas gratefully received!

Answer 1

If SQL Server 2005+ you can use a recursive CTE that you then join on. Example follows.

declare @start int
declare @end int
declare @increment int

set @start = 1282854000
set @increment = 10000
set @end = 1282954000

;with nums as
(
select @start as i
 UNION ALL
 select i+@increment 
 FROM nums
 where i+@increment <= @end
)
select i 
from nums
option (maxrecursion 0)

Answer 2

There are several possible approaches, some that come to mind:

1. use a loop, in each iteration insert your select result in a temp table / table variable
2. create a temp table / table variable beforehand and populate it with your X axis values on the first column and an empty second column; then do an update on it filling the second column using the query based on the value in the first column
3. use what they call "calendar tables" - updated with triggers, help a lot with performance too as they act like a cache