Solved

SQL Select Pivot?

Posted on 2013-06-21
8
207 Views
Last Modified: 2013-06-21
I have a table with the following phone call data fro our sales team

CallDTTM                                              Minutes
2013-06-10 00:23:30.000                        21
etc...



What I want to get is the calls by hour total minutes

CallDate          00AM    01AM    02AM   etc...
06/10/2013       123        251      14
0
Comment
Question by:lrbrister
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 9

Assisted Solution

by:gt2847c
gt2847c earned 250 total points
Comment Utility
This doesn't use the PIVOT keyword, but accomplishes the same thing.  I tend to like this format better as it is more portable between server versions...

SELECT CONVERT(VARCHAR, [CallDTTM], 110) AS [CallDate]
	, ISNULL(SUM(CASE WHEN DATEPART(hour,[CallDTTM]) = 0 THEN [Minutes] END), 0) AS [00AM]
	, ISNULL(SUM(CASE WHEN DATEPART(hour,[CallDTTM]) = 1 THEN [Minutes] END), 0) AS [01AM]
	, ISNULL(SUM(CASE WHEN DATEPART(hour,[CallDTTM]) = 2 THEN [Minutes] END), 0) AS [02AM]
	, ISNULL(SUM(CASE WHEN DATEPART(hour,[CallDTTM]) = 3 THEN [Minutes] END),  0)AS [03AM]
	-- 4-21 skipped for brevity
	, ISNULL(SUM(CASE WHEN DATEPART(hour,[CallDTTM]) = 22 THEN [Minutes] END), 0) AS [10PM]
	, ISNULL(SUM(CASE WHEN DATEPART(hour,[CallDTTM]) = 23 THEN [Minutes] END), 0) AS [11PM]
FROM [MyTable]
GROUP BY CONVERT(VARCHAR, [CallDTTM], 110)
ORDER BY CONVERT(VARCHAR, [CallDTTM], 110)         

Open in new window



Gives output like this:
CallDate	00AM	01AM	02AM	03AM	10PM	11PM
06-09-2013	0	0	0	15	0	0
06-10-2013	21	10	0	0	0	0

Open in new window

0
 
LVL 22

Expert Comment

by:plusone3055
Comment Utility
SELECT CALLDATE, HOUR,  SUM(MINUTES) /60   -- For hours
GROUP BY CALLDATE
0
 
LVL 11

Accepted Solution

by:
Louis01 earned 250 total points
Comment Utility
declare @CallsTable table(CallDTTM datetime, [Minutes] int);
insert into @CallsTable values ('2013-06-10 00:23:30.000', 1);
insert into @CallsTable values ('2013-06-10 00:24:30.000', 10);
insert into @CallsTable values ('2013-06-10 10:23:30.000', 2);
insert into @CallsTable values ('2013-06-10 12:23:30.000', 3);
insert into @CallsTable values ('2013-06-10 14:23:30.000', 4);
insert into @CallsTable values ('2013-06-11 00:23:30.000', 1);
insert into @CallsTable values ('2013-06-11 10:23:30.000', 2);
insert into @CallsTable values ('2013-06-11 12:23:30.000', 3);
insert into @CallsTable values ('2013-06-11 14:23:30.000', 4);


select CallDate	
	 , [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23]
  from (select replace(left(CONVERT(varchar, t1.CallDTTM, 13), 11), ' ', '-') as CallDate
			 , datepart(hour,t1.CallDTTM) as HourFrom
			 , t1.[Minutes] 
		  from @CallsTable t1
		) p1
 PIVOT (SUM([Minutes])
		FOR HourFrom in ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23])
	   ) as pt1	

Open in new window

0
 

Author Comment

by:lrbrister
Comment Utility
Away from my DEV PC.  Will test when I get back
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:lrbrister
Comment Utility
I would like to split points between gt2847c and Louis01

Any violent objections?

Both viable solutions and Louis01 answered with PIVOT even though a minute or so later
0
 
LVL 9

Expert Comment

by:gt2847c
Comment Utility
No complaints here
0
 
LVL 11

Expert Comment

by:Louis01
Comment Utility
Fine by me
0
 

Author Closing Comment

by:lrbrister
Comment Utility
Perfect guys.
Thanks
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now