Solved

SQL Select Pivot?

Posted on 2013-06-21
8
220 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 9

Assisted Solution

by:gt2847c
gt2847c earned 250 total points
ID: 39265844
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
ID: 39265848
SELECT CALLDATE, HOUR,  SUM(MINUTES) /60   -- For hours
GROUP BY CALLDATE
0
 
LVL 11

Accepted Solution

by:
Louis01 earned 250 total points
ID: 39265963
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:lrbrister
ID: 39266084
Away from my DEV PC.  Will test when I get back
0
 

Author Comment

by:lrbrister
ID: 39266403
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
ID: 39266408
No complaints here
0
 
LVL 11

Expert Comment

by:Louis01
ID: 39266448
Fine by me
0
 

Author Closing Comment

by:lrbrister
ID: 39266458
Perfect guys.
Thanks
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

734 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