SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Counting Transactions per Hour using a Pivot Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 09/09/2001 :  18:32:30  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
One of the FAQs on the SQL Server newsgroups concerns creating a pivot table using T-SQL. A previous article, Dynamic Cross-Tabs/Pivot Tables, covered some more advanced ways to create a pivot table, but I want to show a simpler solution that I have used on one of my current projects.

Article Link.

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 09/10/2001 :  11:46:35  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Great article, Garth! Thanks very much for simplifying this. I've used Pivot tables in Excel quite a bit, but doing them in SQL was a bit confusing, until now. Again, Thanks!

-------------------------------------------------------------------
Just because you CAN do something does not mean that you SHOULD!
Go to Top of Page

aiken
Aged Yak Warrior

USA
525 Posts

Posted - 11/05/2002 :  23:57:27  Show Profile  Send aiken an ICQ Message  Reply with Quote
I'm going to dredge this one up more than a year later.

I found this article helpful, however I find that while the data is more clearly presented, performance (in my application) is actually worse than issuing a bunch of individual queries.

My application is a logfile that I want to show per-hour stats on, very much like this article uses.

My schema is thus (simplified, but all that's relevant to this):

CREATE TABLE log
(i int identity,
i_users int,
datetime datetime,
i_servers tinyint,
i_sites tinyint)

CREATE INDEX [idx_log_run_datesite] ON [dbo].[log] ([i_sites], [datetime] desc )

CREATE UNIQUE
INDEX [PK_log] ON [dbo].[log] ([i])

CREATE CLUSTERED
INDEX [idx_log_iusers] ON [dbo].[log] ([i_users])


...My crosstab query is this:

SELECT i_servers,
SUM(CASE WHEN DATEPART(hour,datetime) = 0 THEN 1 ELSE 0 END) AS '0',
SUM(CASE WHEN DATEPART(hour,datetime) = 1 THEN 1 ELSE 0 END) AS '1',
SUM(CASE WHEN DATEPART(hour,datetime) = 2 THEN 1 ELSE 0 END) AS '2',
SUM(CASE WHEN DATEPART(hour,datetime) = 3 THEN 1 ELSE 0 END) AS '3',
SUM(CASE WHEN DATEPART(hour,datetime) = 4 THEN 1 ELSE 0 END) AS '4',
SUM(CASE WHEN DATEPART(hour,datetime) = 5 THEN 1 ELSE 0 END) AS '5',
SUM(CASE WHEN DATEPART(hour,datetime) = 6 THEN 1 ELSE 0 END) AS '6',
SUM(CASE WHEN DATEPART(hour,datetime) = 7 THEN 1 ELSE 0 END) AS '7',
SUM(CASE WHEN DATEPART(hour,datetime) = 8 THEN 1 ELSE 0 END) AS '8',
SUM(CASE WHEN DATEPART(hour,datetime) = 9 THEN 1 ELSE 0 END) AS '9',
SUM(CASE WHEN DATEPART(hour,datetime) = 10 THEN 1 ELSE 0 END) AS '10',
SUM(CASE WHEN DATEPART(hour,datetime) = 11 THEN 1 ELSE 0 END) AS '11',
SUM(CASE WHEN DATEPART(hour,datetime) = 12 THEN 1 ELSE 0 END) AS '12',
SUM(CASE WHEN DATEPART(hour,datetime) = 13 THEN 1 ELSE 0 END) AS '13',
SUM(CASE WHEN DATEPART(hour,datetime) = 14 THEN 1 ELSE 0 END) AS '14',
SUM(CASE WHEN DATEPART(hour,datetime) = 15 THEN 1 ELSE 0 END) AS '15',
SUM(CASE WHEN DATEPART(hour,datetime) = 16 THEN 1 ELSE 0 END) AS '16',
SUM(CASE WHEN DATEPART(hour,datetime) = 17 THEN 1 ELSE 0 END) AS '17',
SUM(CASE WHEN DATEPART(hour,datetime) = 18 THEN 1 ELSE 0 END) AS '18',
SUM(CASE WHEN DATEPART(hour,datetime) = 19 THEN 1 ELSE 0 END) AS '19',
SUM(CASE WHEN DATEPART(hour,datetime) = 20 THEN 1 ELSE 0 END) AS '20',
SUM(CASE WHEN DATEPART(hour,datetime) = 21 THEN 1 ELSE 0 END) AS '21',
SUM(CASE WHEN DATEPART(hour,datetime) = 22 THEN 1 ELSE 0 END) AS '22',
SUM(CASE WHEN DATEPART(hour,datetime) = 23 THEN 1 ELSE 0 END) AS '23'
FROM log WITH(NOLOCK)
WHERE DATETIME>=dateadd(hour,-24,getdate()) and i_sites=1
group by i_servers


Performance isn't completely miserable, but it's pretty bad. Can I improve things with, say, an index on a computed column based on datepart(hour,datetime)? Any other suggestions?

Thanks
-b

In case it's useful, here's the showplan_all from that query:

SELECT i_servers, SUM(CASE WHEN DATEPART(hour,datetime) = 0 THEN 1 ELSE 0 END) AS '0', SUM(CASE WHEN DATEPART(hour,datetime) = 1 THEN 1 ELSE 0 END) AS '1', SUM(CASE WHEN DATEPART(hour,datetime) = 2 THEN 1 ELSE 0 END) AS '2', SUM(CASE WHEN DATEPART(hour,datetime) = 3 THEN 1 ELSE 0 END) AS '3', SUM(CASE WHEN DATEPART(hour,datetime) = 4 THEN 1 ELSE 0 END) AS '4', SUM(CASE WHEN DATEPART(hour,datetime) = 5 THEN 1 ELSE 0 END) AS '5', SUM(CASE WHEN DATEPART(hour,datetime) = 6 THEN 1 ELSE 0 END) AS '6', SUM(CASE WHEN DATEPART(hour,datetime) = 7 THEN 1 ELSE 0 END) AS '7', SUM(CASE WHEN DATEPART(hour,datetime) = 8 THEN 1 ELSE 0 END) AS '8', SUM(CASE WHEN DATEPART(hour,datetime) = 9 THEN 1 ELSE 0 END) AS '9', SUM(CASE WHEN DATEPART(hour,datetime) = 10 THEN 1 ELSE 0 END) AS '10', SUM(CASE WHEN DATEPART(hour,datetime) = 11 THEN 1 ELSE 0 END) AS '11', SUM(CASE WHEN DATEPART(hour,datetime) = 12 THEN 1 ELSE 0 END) AS '12', SUM(CASE WHEN DATEPART(hour,datetime) = 13 THEN 1 ELSE 0 END) AS '13', SUM(CASE WHEN DATEPART(hour,datetime) = 14 THEN 1 ELSE 0 END) AS '14', SUM(CASE WHEN DATEPART(hour,datetime) = 15 THEN 1 ELSE 0 END) AS '15', SUM(CASE WHEN DATEPART(hour,datetime) = 16 THEN 1 ELSE 0 END) AS '16', SUM(CASE WHEN DATEPART(hour,datetime) = 17 THEN 1 ELSE 0 END) AS '17', SUM(CASE WHEN DATEPART(hour,datetime) = 18 THEN 1 ELSE 0 END) AS '18', SUM(CASE WHEN DATEPART(hour,datetime) = 19 THEN 1 ELSE 0 END) AS '19', SUM(CASE WHEN DATEPART(hour,datetime) = 20 THEN 1 ELSE 0 END) AS '20', SUM(CASE WHEN DATEPART(hour,datetime) = 21 THEN 1 ELSE 0 END) AS '21', SUM(CASE WHEN DATEPART(hour,datetime) = 22 THEN 1 ELSE 0 END) AS '22', SUM(CASE WHEN DATEPART(hour,datetime) = 23 THEN 1 ELSE 0 END) AS '23' FROM log WITH(NOLOCK) WHERE DATETIME>=dateadd(hour,-24,getdate()) and i_sites=1 group by i_servers 1 1 0 NULL NULL 1 NULL 6.1503868 NULL NULL NULL 39.658604 NULL NULL SELECT 0 NULL
|--Stream Aggregate(GROUP BY:([log].[i_Servers]) DEFINE:([Expr1002]=SUM([partialagg1028]), [Expr1003]=SUM([partialagg1029]), [Expr1004]=SUM([partialagg1030]), [Expr1005]=SUM([partialagg1031]), [Expr1006]=SUM([partialagg1032]), [Expr1007]=SUM([partialagg1033]), [Expr1008]=SUM([partialagg1034]), [Expr1009]=SUM([partialagg1035]), [Expr1010]=SUM([partialagg1036]), [Expr1011]=SUM([partialagg1037]), [Expr1012]=SUM([partialagg1038]), [Expr1013]=SUM([partialagg1039]), [Expr1014]=SUM([partialagg1040]), [Expr1015]=SUM([partialagg1041]), [Expr1016]=SUM([partialagg1042]), [Expr1017]=SUM([partialagg1043]), [Expr1018]=SUM([partialagg1044]), [Expr1019]=SUM([partialagg1045]), [Expr1020]=SUM([partialagg1046]), [Expr1021]=SUM([partialagg1047]), [Expr1022]=SUM([partialagg1048]), [Expr1023]=SUM([partialagg1049]), [Expr1024]=SUM([partialagg1050]), [Expr1025]=SUM([partialagg1051]))) 1 2 1 Stream Aggregate Aggregate GROUP BY:([log].[i_Servers]) [Expr1002]=SUM([partialagg1028]), [Expr1003]=SUM([partialagg1029]), [Expr1004]=SUM([partialagg1030]), [Expr1005]=SUM([partialagg1031]), [Expr1006]=SUM([partialagg1032]), [Expr1007]=SUM([partialagg1033]), [Expr1008]=SUM([partialagg1034]), [Expr1009]=SUM([partialagg1035]), [Expr1010]=SUM([partialagg1036]), [Expr1011]=SUM([partialagg1037]), [Expr1012]=SUM([partialagg1038]), [Expr1013]=SUM([partialagg1039]), [Expr1014]=SUM([partialagg1040]), [Expr1015]=SUM([partialagg1041]), [Expr1016]=SUM([partialagg1042]), [Expr1017]=SUM([partialagg1043]), [Expr1018]=SUM([partialagg1044]), [Expr1019]=SUM([partialagg1045]), [Expr1020]=SUM([partialagg1046]), [Expr1021]=SUM([partialagg1047]), [Expr1022]=SUM([partialagg1048]), [Expr1023]=SUM([partialagg1049]), [Expr1024]=SUM([partialagg1050]), [Expr1025]=SUM([partialagg1051]) 6.1503868 0.0 9.7483629E-5 110 39.658604 [log].[i_servers], [Expr1002], [Expr1003], [Expr1004], [Expr1005], [Expr1006], [Expr1007], [Expr1008], [Expr1009], [Expr1010], [Expr1011], [Expr1012], [Expr1013], [Expr1014], [Expr1015], [Expr1016], [Expr1017], [Expr1018], [Expr1019], [Expr1020], [Expr1021], [Expr1022], [Expr1023], [Expr1024], [Expr1025] NULL PLAN_ROW 0 1.0
|--Sort(ORDER BY:([log].[i_servers] ASC)) 1 3 2 Sort Sort ORDER BY:([log].[i_servers] ASC) NULL 24.601547 1.1261261E-2 2.7744425E-4 110 39.658508 [log].[i_servers], [partialagg1028], [partialagg1029], [partialagg1030], [partialagg1031], [partialagg1032], [partialagg1033], [partialagg1034], [partialagg1035], [partialagg1036], [partialagg1037], [partialagg1038], [partialagg1039], [partialagg1040], [partialagg1041], [partialagg1042], [partialagg1043], [partialagg1044], [partialagg1045], [partialagg1046], [partialagg1047], [partialagg1048], [partialagg1049], [partialagg1050], [partialagg1051] NULL PLAN_ROW 0 1.0
|--Parallelism(Gather Streams) 1 4 3 Parallelism Gather Streams NULL NULL 24.601547 0.0 2.8999325E-2 110 39.646969 [log].[i_servers], [partialagg1028], [partialagg1029], [partialagg1030], [partialagg1031], [partialagg1032], [partialagg1033], [partialagg1034], [partialagg1035], [partialagg1036], [partialagg1037], [partialagg1038], [partialagg1039], [partialagg1040], [partialagg1041], [partialagg1042], [partialagg1043], [partialagg1044], [partialagg1045], [partialagg1046], [partialagg1047], [partialagg1048], [partialagg1049], [partialagg1050], [partialagg1051] NULL PLAN_ROW -1 1.0
|--Hash Match(Partial Aggregate, HASH:([log].[i_servers]) DEFINE:([partialagg1028]=SUM(If (datepart(hour, Convert([log].[datetime]))=0) then 1 else 0), [partialagg1029]=SUM(If (datepart(hour, Convert([log].[datetime]))=1) then 1 else 0), [partialagg1030]=SUM(If (datepart(hour, Convert([log].[datetime]))=2) then 1 else 0), [partialagg1031]=SUM(If (datepart(hour, Convert([log].[datetime]))=3) then 1 else 0), [partialagg1032]=SUM(If (datepart(hour, Convert([log].[datetime]))=4) then 1 else 0), [partialagg1033]=SUM(If (datepart(hour, Convert([log].[datetime]))=5) then 1 else 0), [partialagg1034]=SUM(If (datepart(hour, Convert([log].[datetime]))=6) then 1 else 0), [partialagg1035]=SUM(If (datepart(hour, Convert([log].[datetime]))=7) then 1 else 0), [partialagg1036]=SUM(If (datepart(hour, Convert([log].[datetime]))=8) then 1 else 0), [partialagg1037]=SUM(If (datepart(hour, Convert([log].[datetime]))=9) then 1 else 0), [partialagg1038]=SUM(If (datepart(hour, Convert([log].[datetime]))=10) then 1 else 0), [partialagg1039]=SUM(If (datepart(hour, Convert([log].[datetime]))=11) then 1 else 0), [partialagg1040]=SUM(If (datepart(hour, Convert([log].[datetime]))=12) then 1 else 0), [partialagg1041]=SUM(If (datepart(hour, Convert([log].[datetime]))=13) then 1 else 0), [partialagg1042]=SUM(If (datepart(hour, Convert([log].[datetime]))=14) then 1 else 0), [partialagg1043]=SUM(If (datepart(hour, Convert([log].[datetime]))=15) then 1 else 0), [partialagg1044]=SUM(If (datepart(hour, Convert([log].[datetime]))=16) then 1 else 0), [partialagg1045]=SUM(If (datepart(hour, Convert([log].[datetime]))=17) then 1 else 0), [partialagg1046]=SUM(If (datepart(hour, Convert([log].[datetime]))=18) then 1 else 0), [partialagg1047]=SUM(If (datepart(hour, Convert([log].[datetime]))=19) then 1 else 0), [partialagg1048]=SUM(If (datepart(hour, Convert([log].[datetime]))=20) then 1 else 0), [partialagg1049]=SUM(If (datepart(hour, Convert([log].[datetime]))=21) then 1 else 0), [partialagg1050]=SUM(If (datepart(hour, Convert([log].[datetime]))=22) then 1 else 0), [partialagg1051]=SUM(If (datepart(hour, Convert([log].[datetime]))=23) then 1 else 0))) 1 5 4 Hash Match Partial Aggregate HASH:([log].[i_servers]), DEFINE:([partialagg1028]=SUM(If (datepart(hour, Convert([log].[datetime]))=0) then 1 else 0), [partialagg1029]=SUM(If (datepart(hour, Convert([log].[datetime]))=1) then 1 else 0), [partialagg1030]=SUM(If (datepart(hour, Convert([log].[datetime]))=2) then 1 else 0), [partialagg1031]=SUM(If (datepart(hour, Convert([log].[datetime]))=3) then 1 else 0), [partialagg1032]=SUM(If (datepart(hour, Convert([log].[datetime]))=4) then 1 else 0), [partialagg1033]=SUM(If (datepart(hour, Convert([log].[datetime]))=5) then 1 else 0), [partialagg1034]=SUM(If (datepart(hour, Convert([log].[datetime]))=6) then 1 else 0), [partialagg1035]=SUM(If (datepart(hour, Convert([log].[datetime]))=7) then 1 else 0), [partialagg1036]=SUM(If (datepart(hour, Convert([log].[datetime]))=8) then 1 else 0), [partialagg1037]=SUM(If (datepart(hour, Convert([log].[datetime]))=9) then 1 else 0), [partialagg1038]=SUM(If (datepart(hour, Convert([log].[datetime]))=10) then 1 else 0), [partialagg1039]=SUM(If (datepart(hour, Convert([log].[datetime]))=11) then 1 else 0), [partialagg1040]=SUM(If (datepart(hour, Convert([log].[datetime]))=12) then 1 else 0), [partialagg1041]=SUM(If (datepart(hour, Convert([log].[datetime]))=13) then 1 else 0), [partialagg1042]=SUM(If (datepart(hour, Convert([log].[datetime]))=14) then 1 else 0), [partialagg1043]=SUM(If (datepart(hour, Convert([log].[datetime]))=15) then 1 else 0), [partialagg1044]=SUM(If (datepart(hour, Convert([log].[datetime]))=16) then 1 else 0), [partialagg1045]=SUM(If (datepart(hour, Convert([log].[datetime]))=17) then 1 else 0), [partialagg1046]=SUM(If (datepart(hour, Convert([log].[datetime]))=18) then 1 else 0), [partialagg1047]=SUM(If (datepart(hour, Convert([log].[datetime]))=19) then 1 else 0), [partialagg1048]=SUM(If (datepart(hour, Convert([log].[datetime]))=20) then 1 else 0), [partialagg1049]=SUM(If (datepart(hour, Convert([log].[datetime]))=21) then 1 else 0), [partialagg1050]=SUM(If (datepart(hour, Convert([log].[datetime]))=22) then 1 else 0), [partialagg1051]=SUM(If (datepart(hour, Convert([log].[datetime]))=23) then 1 else 0)) [partialagg1028]=SUM(If (datepart(hour, Convert([log].[datetime]))=0) then 1 else 0), [partialagg1029]=SUM(If (datepart(hour, Convert([log].[datetime]))=1) then 1 else 0), [partialagg1030]=SUM(If (datepart(hour, Convert([log].[datetime]))=2) then 1 else 0), [partialagg1031]=SUM(If (datepart(hour, Convert([log].[datetime]))=3) then 1 else 0), [partialagg1032]=SUM(If (datepart(hour, Convert([log].[datetime]))=4) then 1 else 0), [partialagg1033]=SUM(If (datepart(hour, Convert([log].[datetime]))=5) then 1 else 0), [partialagg1034]=SUM(If (datepart(hour, Convert([log].[datetime]))=6) then 1 else 0), [partialagg1035]=SUM(If (datepart(hour, Convert([log].[datetime]))=7) then 1 else 0), [partialagg1036]=SUM(If (datepart(hour, Convert([log].[datetime]))=8) then 1 else 0), [partialagg1037]=SUM(If (datepart(hour, Convert([log].[datetime]))=9) then 1 else 0), [partialagg1038]=SUM(If (datepart(hour, Convert([log].[datetime]))=10) then 1 else 0), [partialagg1039]=SUM(If (datepart(hour, Convert([log].[datetime]))=11) then 1 else 0), [partialagg1040]=SUM(If (datepart(hour, Convert([log].[datetime]))=12) then 1 else 0), [partialagg1041]=SUM(If (datepart(hour, Convert([log].[datetime]))=13) then 1 else 0), [partialagg1042]=SUM(If (datepart(hour, Convert([log].[datetime]))=14) then 1 else 0), [partialagg1043]=SUM(If (datepart(hour, Convert([log].[datetime]))=15) then 1 else 0), [partialagg1044]=SUM(If (datepart(hour, Convert([log].[datetime]))=16) then 1 else 0), [partialagg1045]=SUM(If (datepart(hour, Convert([log].[datetime]))=17) then 1 else 0), [partialagg1046]=SUM(If (datepart(hour, Convert([log].[datetime]))=18) then 1 else 0), [partialagg1047]=SUM(If (datepart(hour, Convert([log].[datetime]))=19) then 1 else 0), [partialagg1048]=SUM(If (datepart(hour, Convert([log].[datetime]))=20) then 1 else 0), [partialagg1049]=SUM(If (datepart(hour, Convert([log].[datetime]))=21) then 1 else 0), [partialagg1050]=SUM(If (datepart(hour, Convert([log].[datetime]))=22) then 1 else 0), [partialagg1051]=SUM(If (datepart(hour, Convert([log].[datetime]))=23) then 1 else 0) 24.601547 0.0 2.2327244 110 39.61797 [log].[i_servers], [partialagg1028], [partialagg1029], [partialagg1030], [partialagg1031], [partialagg1032], [partialagg1033], [partialagg1034], [partialagg1035], [partialagg1036], [partialagg1037], [partialagg1038], [partialagg1039], [partialagg1040], [partialagg1041], [partialagg1042], [partialagg1043], [partialagg1044], [partialagg1045], [partialagg1046], [partialagg1047], [partialagg1048], [partialagg1049], [partialagg1050], [partialagg1051] NULL PLAN_ROW -1 1.0
|--Clustered Index Scan(OBJECT:([database].[dbo].[log].[idx_log_iusers]), WHERE:([log].[i_sites]=1 AND Convert([log].[datetime])>=dateadd(hour, -24, getdate()))) 1 6 5 Clustered Index Scan Clustered Index Scan OBJECT:([database].[dbo].[log].[idx_log_iusers]), WHERE:([log].[i_sites]=1 AND Convert([log].[datetime])>=dateadd(hour, -24, getdate())) [log].[i_sites], [log].[datetime], [log].[i_servers] 968224.06 33.929432 1.8276123 75 35.757042 [log].[i_sites], [log].[datetime], [log].[i_servers] NULL PLAN_ROW -1 1.0



Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 11/06/2002 :  00:03:57  Show Profile  Visit graz's Homepage  Reply with Quote
(I edited the previous post to remove the CODE tag. That may not be more readable but all the rest of the posts are)

-graz

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/13/2002 :  14:24:38  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
To increase performance:

Remember that DATEPART is evaluated over and over. You may get better performance by creating a subquery returning your data, grouped by Hour in a field called 'Hour'. Then, do a cross-tab using that field. I do this all the time for Utilization reports here at the office, where we list employees as rows and different types of labor as columns and hours as values.

BTW -- this also makes your query shorter and easier to edit and read.

SELECT Server,
SUM(CASE WHEN Hr = 1 THEN Value ELSE 0 END) as '01',
SUM(CASE WHEN Hr = 2 then Value ELSE 0 END) as '02',
..etc...
FROM
(SELECT server, DatePart(hour,datetime) Hr, SUM(1) as value FROM
log
GROUP BY server, DatePart(hour,datetime)) A

I have had good luck really increasing query performance by using subqueries this way.


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000