I saw the Dynamic pivot of the request and wrote this to pivot the data, its similar to dunstans, there are some simplifications optimisations that can be made like splitting the Date and Time parts into separate tables and that would remove the SELECT DISTINCT from the dynamic case build, I also think you can get rid of the LEFT(string, Lenf(String)-1) to remove the last comma, but its been a long day.

/*

Build working set

*/

CREATE TABLE #Hist

(

CallDateTime DateTime

,HistId int

)

Insert into #Hist

VALUES ('2013-02-01 06:14:41.000',2671058)

,('2013-02-01 07:02:15.000', 2671057)

,('2013-02-01 07:06:41.000', 2671059)

,('2013-02-01 07:07:20.000', 2671069)

,('2013-02-01 07:22:57.000', 2671068)

,('2013-02-01 07:38:15.000', 2671067)

,('2013-02-01 07:42:48.000', 2671066)

,('2013-02-01 07:53:47.000', 2671065)

,('2013-02-01 08:02:37.000', 2671064)

,('2013-02-01 08:07:04.000', 2671063)

,('2013-02-01 08:11:09.000', 2671062)

,('2013-02-01 08:22:12.000', 2671061)

,('2013-02-01 08:22:48.000', 2671060)

Insert into #Hist

VALUES ('2013-02-02 06:14:41.000',2671058)

,('2013-02-02 09:02:15.000', 2671057)

,('2013-02-02 09:06:41.000', 2671059)

,('2013-02-02 09:07:20.000', 2671069)

,('2013-02-02 09:22:57.000', 2671068)

,('2013-02-02 09:38:15.000', 2671067)

,('2013-02-02 09:42:48.000', 2671066)

,('2013-02-02 07:53:47.000', 2671065)

,('2013-02-02 10:02:37.000', 2671064)

,('2013-02-02 11:07:04.000', 2671063)

,('2013-02-02 12:11:09.000', 2671062)

,('2013-02-02 12:22:12.000', 2671061)

,('2013-02-02 15:22:48.000', 2671060)

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

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

/*

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)*30,@StartDate) StartTime

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

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

+convert(varchar(5),Convert(Time(0),DateAdd(minute,(rn)*30,@StartDate))) timepart

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

,rn

from CTE

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

)

/*

Build the data set to group into time slice

*/

Select datep

,timepart

,rn % 48 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,rn

order by datep,timepart

/*

Get ready to build the dynamic pivot

*/

Declare @dm_ct varchar(max)

Declare @pvt varchar(4000)

/*

build dynamic CASE for use in pivot using a for XML

*/

Select @pvt=(Select 'SUM(CASE WHEN modulous=' +convert(varchar(2),modulous) +' Then TotalCalls' +' Else 0 END) AS ['+timepart+'], '

from

(Select distinct timepart,Modulous

from #prepivot) x

FOR XML PATH(''))

/*

build final Select

*/

Set @dm_ct='Select Datep,'+ Left(@pvt,len(@pvt)-1)+ 'FROM #prepivot GROUP BY DAtep'

/*

Execute dynamic SQL

*/

EXEC (@dm_ct)

/*

Clean up

*/

Drop Table #Hist

Drop Table #PrePivot

Edited : added comments.