I've made two refinements the first is the addition of a dynamic interval slicer, and the second was to remove the modulous calculation and replace it with a ROW_NUMBER() based on the number of intervals per day.

Declare @StartDate Datetime = '01-Feb-2013 00:00:00.000'

Declare @endDate Datetime = '03-Feb-2013 00:00:00.000'

Declare @interval tinyint =60 --Interval in minutes.

/*

Create an Inline Tally Table

*/

;with CTE

AS

(

Select

Row_NUMBER() OVER (ORDER BY object_id) Rn

from sys.all_columns

)

/*

Generate my intervals (can be changed!!)

*/

,Cte_Interval AS

(

Select

DateAdd(minute,(rn-1)*@interval,@StartDate) StartTime

, DateAdd(minute,(rn)*@interval,@StartDate) EndTime

,convert(varchar(5),Convert(Time(0),DateAdd(minute,(rn-1)*@interval,@StartDate)))+'-'+convert(varchar(5),Convert(Time(0),DateAdd(minute,(rn)*@interval,@StartDate))) timepart

,convert(varchar(10),convert(date,DateAdd(minute,(rn-1)*@interval,@StartDate))) datep

from CTE

Where DateAdd(minute,(rn)*@interval,@StartDate)<=@EndDate

)

/*

Build the data set to group into time slice

*/

Select datep

,timepart

,Row_Number() OVER(PARTITION BY datep order by timepart) Modulous

,count(histId) TotalCalls

into #prepivot

from Cte_Interval inter

LEFT JOIN #Hist hist on hist.CallDateTime >=inter.StartTime

and hist.CallDateTime<inter.EndTime

group by timepart,datep

order by datep,timepart

Anyway time to sign off for the day, if anyone can suggest improvements then please post them.