Just to demonstrate a couple of possibilities. The first example doesn't show the actual dates in the column headers, only the day of week, but is very simple. The second has the actual dates in the column headings, but is more complex. To test the examples, a table was created named [calls], with columns: Extension, Name, DateTimeStamp, and Results.
DECLARE @WeekStartDate as smalldatetime
, @WeekEndDate as smalldatetime
SET @WeekStartDate = '4/16/2017'
SET @WeekEndDate = '4/22/2017'
SELECT Extension + ' - ' + Name as ExtName
, SUM(case when dWeekday = 2 then 1 else 0 end) as 'Mon '
, SUM(case when dWeekday = 3 then 1 else 0 end) as 'Tue '
, SUM(case when dWeekday = 4 then 1 else 0 end) as 'Wed '
, SUM(case when dWeekday = 5 then 1 else 0 end) as 'Thr '
, SUM(case when dWeekday = 6 then 1 else 0 end) as 'Fri '
, SUM(case when dWeekday = 7 then 1 else 0 end) as 'Sat '
, SUM(case when dWeekday = 1 then 1 else 0 end) as 'Sun '
FROM calls
CROSS APPLY(SELECT datepart(weekday, DateTimeStamp) AS dWeekday) _
GROUP BY Extension + ' - ' + Name
HAVING COUNT(*) > 0
ORDER BY Extension + ' - ' + Name
Returns:
ExtName Mon Tue Wed Thr Fri Sat Sun
101 - John Doe 2 0 0 1 1 0 0
102 - Sam Smith 1 0 0 1 0 0 0
DECLARE @WeekStartDate as smalldatetime
, @WeekEndDate as smalldatetime
, @SQL as nvarchar(4000)
SET @WeekStartDate = '4/16/2017'
SET @WeekEndDate = '4/22/2017'
SET @SQL = '
SELECT Extension + '' - '' + Name as ExtName
, SUM(case when dWeekday = 2 then 1 else 0 end) as ''Mon dateadd(d,0, @WeekStartDate)''
, SUM(case when dWeekday = 3 then 1 else 0 end) as ''Tue dateadd(d,1, @WeekStartDate)''
, SUM(case when dWeekday = 4 then 1 else 0 end) as ''Wed dateadd(d,2, @WeekStartDate)''
, SUM(case when dWeekday = 5 then 1 else 0 end) as ''Thr dateadd(d,3, @WeekStartDate)''
, SUM(case when dWeekday = 6 then 1 else 0 end) as ''Fri dateadd(d,4, @WeekStartDate)''
, SUM(case when dWeekday = 7 then 1 else 0 end) as ''Sat dateadd(d,5, @WeekStartDate)''
, SUM(case when dWeekday = 1 then 1 else 0 end) as ''Sun dateadd(d,6, @WeekStartDate)''
FROM calls
CROSS APPLY(SELECT datepart(weekday, DateTimeStamp) AS dWeekday) _
GROUP BY Extension + '' - '' + Name
HAVING COUNT(*) > 0
ORDER BY Extension + '' - '' + Name
'
SET @SQL = replace(@SQL,'dateadd(d,0, @WeekStartDate)', Convert(varchar(20),Convert(date, dateadd(d,0, @WeekStartDate))) )
SET @SQL = replace(@SQL,'dateadd(d,1, @WeekStartDate)', Convert(varchar(20),Convert(date, dateadd(d,1, @WeekStartDate))) )
SET @SQL = replace(@SQL,'dateadd(d,2, @WeekStartDate)', Convert(varchar(20),Convert(date, dateadd(d,2, @WeekStartDate))) )
SET @SQL = replace(@SQL,'dateadd(d,3, @WeekStartDate)', Convert(varchar(20),Convert(date, dateadd(d,3, @WeekStartDate))) )
SET @SQL = replace(@SQL,'dateadd(d,4, @WeekStartDate)', Convert(varchar(20),Convert(date, dateadd(d,4, @WeekStartDate))) )
SET @SQL = replace(@SQL,'dateadd(d,5, @WeekStartDate)', Convert(varchar(20),Convert(date, dateadd(d,5, @WeekStartDate))) )
SET @SQL = replace(@SQL,'dateadd(d,6, @WeekStartDate)', Convert(varchar(20),Convert(date, dateadd(d,6, @WeekStartDate))) )
SET @SQL = replace(@SQL,'@WeekStartDate', Convert(varchar(20),Convert(date, @WeekStartDate)) )
SET @SQL = replace(@SQL,'@WeekEndDate', Convert(varchar(20),Convert(date, @WeekEndDate)) )
exec(@SQL)
Returns:
ExtName Mon 2017-04-16 Tue 2017-04-17 Wed 2017-04-18 Thr 2017-04-19 Fri 2017-04-20 Sat 2017-04-21 Sun 2017-04-22
101 - John Doe 2 0 0 1 1 0 0
102 - Sam Smith 1 0 0 1 0 0 0