Hi
I have a small database on MS SQL 2008 R2 for holding call log data from our phone system. Recently I setup a small call centre and they need to see how many calls have come in during each hour of the day across a whole calendar month.
I immediately thought that a Pivot table would do the trick, but I'm having a lot of trouble trying to create one. I started out with the following query to extract the relavent data:
This produces the following result:
But try as I might, I cannot turn this query into a Pivot table. Has anyone done something similar or have any of you got any ideas that might help?
I have a small database on MS SQL 2008 R2 for holding call log data from our phone system. Recently I setup a small call centre and they need to see how many calls have come in during each hour of the day across a whole calendar month.
I immediately thought that a Pivot table would do the trick, but I'm having a lot of trouble trying to create one. I started out with the following query to extract the relavent data:
SQL:
DECLARE @DATEFROM DATETIME
DECLARE @DATETO DATETIME
DECLARE @LIST TABLE (Value VARCHAR(8))
Set @DATEFROM = '2014-01-01T00:00:00'
Set @DATETO = '2014-01-31T23:59:59'
INSERT INTO @LIST VALUES ('17351')
INSERT INTO @LIST VALUES ('17141')
INSERT INTO @LIST VALUES ('17401')
INSERT INTO @LIST VALUES ('17186')
INSERT INTO @LIST VALUES ('17264')
INSERT INTO @LIST VALUES ('17124')
Select Convert(VarChar, LogDate, 103) As "Date",
Convert(VarChar, DatePart(Hour, LogDate)) + ':00' As "Hour",
COUNT(TermID) As "No. Of Calls"
From Calls
WHERE Calls.TermID IN (SELECT Value FROM @LIST)
AND Calls.LogDate BETWEEN @DATEFROM AND @DATETO
GROUP BY Convert(VarChar, LogDate, 103), DatePart(Hour, LogDate)
ORDER BY Convert(VarChar, LogDate, 103), DatePart(Hour, LogDate)
This produces the following result:
Code:
Date :Hour No. Of Calls
------------------------------ --------------------------------- ------------
13/01/2014 8:00 2
13/01/2014 9:00 4
13/01/2014 10:00 8
13/01/2014 11:00 5
13/01/2014 12:00 7
13/01/2014 13:00 5
13/01/2014 14:00 4
13/01/2014 15:00 3
13/01/2014 16:00 2
14/01/2014 9:00 9
14/01/2014 10:00 7
14/01/2014 11:00 4
14/01/2014 12:00 4
14/01/2014 13:00 6
14/01/2014 14:00 4
14/01/2014 15:00 3
14/01/2014 16:00 1
But try as I might, I cannot turn this query into a Pivot table. Has anyone done something similar or have any of you got any ideas that might help?