Create a Dynamic Pivot Table 1

[DWheater]

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:

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:

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?

 

[simian336]

can you give a data example of what you have and what you want it to look like

Simi

 

[DWheater]

This is a sample of the raw date from the Calls table:

OrigID   TermID   LogDate                 Duration
-------- -------- ----------------------- -----------------------
17120    A008030  2013-11-20 17:04:41.000 1900-01-01 00:00:08.000
17120    A116332  2013-12-11 13:59:06.000 1900-01-01 00:00:12.000
17120    A116333  2013-12-10 09:36:51.000 1900-01-01 00:00:50.000
17124    A008026  2014-01-13 12:45:31.000 1900-01-01 00:03:38.000
17124    A116330  2014-01-13 14:22:21.000 1900-01-01 00:03:14.000
17124    A116331  2014-01-14 10:18:00.000 1900-01-01 00:06:04.000
17124    A116332  2014-01-14 10:26:42.000 1900-01-01 00:00:38.000
17124    A116334  2014-01-13 13:38:36.000 1900-01-01 00:00:38.000
17141    A008030  2013-11-20 17:00:43.000 1900-01-01 00:00:18.000
17141    A116333  2014-01-13 10:49:29.000 1900-01-01 00:01:22.000
17141    A116335  2014-01-13 13:55:35.000 1900-01-01 00:03:26.000
17264    A116327  2014-01-14 08:45:08.000 1900-01-01 00:00:02.000
17264    A116328  2014-01-13 15:13:38.000 1900-01-01 00:17:06.000
17264    A116330  2013-12-04 10:43:00.000 1900-01-01 00:02:00.000
17264    A116330  2014-01-13 10:38:10.000 1900-01-01 00:00:02.000
17264    A116330  2014-01-14 08:43:24.000 1900-01-01 00:00:32.000
17264    A116331  2014-01-13 10:42:32.000 1900-01-01 00:00:12.000
17264    A116331  2014-01-13 12:27:23.000 1900-01-01 00:00:20.000
17264    A116331  2014-01-14 12:51:54.000 1900-01-01 00:00:10.000
17264    A116332  2013-12-04 12:15:09.000 1900-01-01 00:00:52.000

(20 row(s) affected)

The OrigID and TermID are type VarChar(8) the LogDate is DateTime

I'd like to to come out something like this:

             08:00   09:00   10:00   11:00   12:00   13:00   14:00   15:00   16:00
13/01/2014       2       4       8       5       7       5       4       3       2
14/01/2014       9       7       4       4       6       4       3       1       0

I'd like to produce this for each month (I've only got two days worth of data at the moment)

 

[simian336]

ah, Makes more since. Send your results from your query above to a Temp table. Then Pivot that.

insert into mypivot values ('01/13/2014','8:00',2)
insert into mypivot values ('01/13/2014','9:00',4)
insert into mypivot values ('01/13/2014','10:00',8)
insert into mypivot values ('01/13/2014','11:00',5)
insert into mypivot values ('01/13/2014','12:00',7)
insert into mypivot values ('01/13/2014','13:00',5)
insert into mypivot values ('01/13/2014','14:00',4)
insert into mypivot values ('01/13/2014','15:00',3)
insert into mypivot values ('01/13/2014','16:00',2)
insert into mypivot values ('01/14/2014','9:00',9)
insert into mypivot values ('01/14/2014','10:00',7)
insert into mypivot values ('01/14/2014','11:00',4)
insert into mypivot values ('01/14/2014','12:00',4)
insert into mypivot values ('01/14/2014','13:00',6)
insert into mypivot values ('01/14/2014','14:00',4)
insert into mypivot values ('01/14/2014','15:00',3)
insert into mypivot values ('01/14/2014','16:00',1)

select * from mypivot
pivot (max (numcalls)
for [hour] in ([1:00],[2:00],[3:00],[4:00],[5:00],[6:00],[7:00],[9:00],[10:00],[11:00],[12:00],[13:00],[14:00],[15:00],[16:00],[17:00],[18:00],[19:00],[20:00],[21:00],[22:00],[23:00]))

Simi

 

[DWheater]

Thanks, I'll look at that.

 

[DWheater]

Just wanted to say thanks, with your suggestion I managed to get the query working.

Use CallLog

DECLARE @DATEFROM DATETIME
DECLARE @DATETO DATETIME
DECLARE @LIST TABLE (Value VARCHAR(8))
--CREATE TABLE MYPIVOT (MyDate NVARCHAR(10),MyHour NVARCHAR(10),numcalls Int)

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 MyDate,
			Convert(VarChar, DatePart(Hour, LogDate)) + ':00' As MyHour,
			COUNT(TermID) As NumCalls
INTO		MYPIVOT
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) 


select * from MYPIVOT
pivot (MAX (numcalls) for MyHour in ([9:00],[10:00],[11:00],[12:00],[13:00],[14:00],[15:00],[16:00]) ) as NoCallsPerDay


DROP TABLE MYPIVOT

Now gives me:

MyDate                         9:00        10:00       11:00       12:00       13:00       14:00       15:00       16:00
------------------------------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
13/01/2014                     4           8           5           7           5           4           3           2
14/01/2014                     9           7           4           4           5           4           3           1
15/01/2014                     3           6           8           7           2           6           2           1