Results 1 to 7 of 7

032612, 15:56 #1Registered User
 Join Date
 Mar 2012
 Posts
 2
Unanswered: Is there a more efficient way to write this?
Hi, I wrote a SQL Statement that seems like it can be more efficient. It works without issue, and it seems to be pretty quick, but I wanted to make sure it was at it's peak performance. What I'm doing is getting the total for each month of 2010, then the total for the whole year.
Here is my code:
Code:SELECT SUM(CASE WHEN [ORDER_DATE] >= '20100101' AND [ORDER_DATE] < '20100201' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as January, SUM(CASE WHEN [ORDER_DATE] >= '20100201' AND [ORDER_DATE] < '20100301' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as February, SUM(CASE WHEN [ORDER_DATE] >= '20100301' AND [ORDER_DATE] < '20100401' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as March, SUM(CASE WHEN [ORDER_DATE] >= '20100401' AND [ORDER_DATE] < '20100501' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as April, SUM(CASE WHEN [ORDER_DATE] >= '20100501' AND [ORDER_DATE] < '20100601' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as May, SUM(CASE WHEN [ORDER_DATE] >= '20100601' AND [ORDER_DATE] < '20100701' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as June, SUM(CASE WHEN [ORDER_DATE] >= '20100701' AND [ORDER_DATE] < '20100801' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as July, SUM(CASE WHEN [ORDER_DATE] >= '20100801' AND [ORDER_DATE] < '20100901' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as August, SUM(CASE WHEN [ORDER_DATE] >= '20100901' AND [ORDER_DATE] < '20101001' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as September, SUM(CASE WHEN [ORDER_DATE] >= '20101001' AND [ORDER_DATE] < '20101101' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as October, SUM(CASE WHEN [ORDER_DATE] >= '20101101' AND [ORDER_DATE] < '20101201' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as November, SUM(CASE WHEN [ORDER_DATE] >= '20101201' AND [ORDER_DATE] < '20110101' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as December, /*Gets the total for the whole year*/ SUM(CASE WHEN [ORDER_DATE] >= '20100101' AND [ORDER_DATE] < '20110101' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as Total FROM [SALES_ORDERS] GROUP BY [CUSTOMER_NUMBER] HAVING [CUSTOMER_NUMBER]= 6871

032612, 16:06 #2Registered User
 Join Date
 Jan 2003
 Location
 Massachusetts
 Posts
 5,860
Provided Answers: 17The database engine will not likely be able to be pushed any faster for this sort of query. You may get a slight boost, if you have many years of orders, and you partition on year, but the query will still require a full scan of that partition. You may want to look into creating an Analysis Services cube of this table, and running these queries on that. Analysis Services specializes in aggregating data in just this sort of way.

032612, 16:09 #3Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54I would expect this code to be significantly more efficient:
Code:SELECT SUM(CASE WHEN [ORDER_DATE] >= '20100101' AND [ORDER_DATE] < '20100201' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as January , SUM(CASE WHEN [ORDER_DATE] >= '20100201' AND [ORDER_DATE] < '20100301' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as February , SUM(CASE WHEN [ORDER_DATE] >= '20100301' AND [ORDER_DATE] < '20100401' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as March , SUM(CASE WHEN [ORDER_DATE] >= '20100401' AND [ORDER_DATE] < '20100501' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as April , SUM(CASE WHEN [ORDER_DATE] >= '20100501' AND [ORDER_DATE] < '20100601' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as May , SUM(CASE WHEN [ORDER_DATE] >= '20100601' AND [ORDER_DATE] < '20100701' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as June , SUM(CASE WHEN [ORDER_DATE] >= '20100701' AND [ORDER_DATE] < '20100801' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as July , SUM(CASE WHEN [ORDER_DATE] >= '20100801' AND [ORDER_DATE] < '20100901' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as August , SUM(CASE WHEN [ORDER_DATE] >= '20100901' AND [ORDER_DATE] < '20101001' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as September , SUM(CASE WHEN [ORDER_DATE] >= '20101001' AND [ORDER_DATE] < '20101101' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as October , SUM(CASE WHEN [ORDER_DATE] >= '20101101' AND [ORDER_DATE] < '20101201' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as November , SUM(CASE WHEN [ORDER_DATE] >= '20101201' AND [ORDER_DATE] < '20110101' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as December , SUM([TOTAL_ORDER_VALUE]) as Total /*Gets the total for the whole year*/ FROM [SALES_ORDERS] WHERE 6871 = [CUSTOMER_NUMBER] AND '20100101' <= [ORDER_DATE] AND [ORDER_DATE] < '20110101'
In theory, theory and practice are identical. In practice, theory and practice are unrelated.

032612, 16:36 #4Registered User
 Join Date
 Mar 2012
 Posts
 2
a
When I run it I get this as my error:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'TOTAL_ORDER_VALUE'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'TOTAL_ORDER_VALUE'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'TOTAL_ORDER_VALUE'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'TOTAL_ORDER_VALUE'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'TOTAL_ORDER_VALUE'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'TOTAL_ORDER_VALUE'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'TOTAL_ORDER_VALUE'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'TOTAL_ORDER_VALUE'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'TOTAL_ORDER_VALUE'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'TOTAL_ORDER_VALUE'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'TOTAL_ORDER_VALUE'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'TOTAL_ORDER_VALUE'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'TOTAL_ORDER_VALUE'.

032612, 16:42 #5Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54In theory, theory and practice are identical. In practice, theory and practice are unrelated.

032612, 16:59 #6Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54Just on the odd chance that I'd lost my mind again, try this:
Code:CREATE TABLE [SALES_ORDERS] ( [CUSTOMER_NUMBER] INT , [ORDER_DATE] DATETIME , [TOTAL_ORDER_VALUE] MONEY ) INSERT INTO [SALES_ORDERS] SELECT z2.CUSTOMER_NUMBER , DateAdd(month, z1.number, '20090109') , z2.CUSTOMER_NUMBER / 100 + 10.01 * z1.number FROM master.dbo.spt_values AS z1 CROSS JOIN (SELECT 5000 AS CUSTOMER_NUMBER UNION SELECT 6871 UNION SELECT 7000) AS z2 WHERE z1.type = 'P' AND z1.number BETWEEN 0 AND 35 SELECT SUM(CASE WHEN [ORDER_DATE] >= '20100101' AND [ORDER_DATE] < '20100201' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as January, SUM(CASE WHEN [ORDER_DATE] >= '20100201' AND [ORDER_DATE] < '20100301' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as February, SUM(CASE WHEN [ORDER_DATE] >= '20100301' AND [ORDER_DATE] < '20100401' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as March, SUM(CASE WHEN [ORDER_DATE] >= '20100401' AND [ORDER_DATE] < '20100501' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as April, SUM(CASE WHEN [ORDER_DATE] >= '20100501' AND [ORDER_DATE] < '20100601' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as May, SUM(CASE WHEN [ORDER_DATE] >= '20100601' AND [ORDER_DATE] < '20100701' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as June, SUM(CASE WHEN [ORDER_DATE] >= '20100701' AND [ORDER_DATE] < '20100801' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as July, SUM(CASE WHEN [ORDER_DATE] >= '20100801' AND [ORDER_DATE] < '20100901' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as August, SUM(CASE WHEN [ORDER_DATE] >= '20100901' AND [ORDER_DATE] < '20101001' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as September, SUM(CASE WHEN [ORDER_DATE] >= '20101001' AND [ORDER_DATE] < '20101101' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as October, SUM(CASE WHEN [ORDER_DATE] >= '20101101' AND [ORDER_DATE] < '20101201' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as November, SUM(CASE WHEN [ORDER_DATE] >= '20101201' AND [ORDER_DATE] < '20110101' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as December, /*Gets the total for the whole year*/ SUM(CASE WHEN [ORDER_DATE] >= '20100101' AND [ORDER_DATE] < '20110101' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as Total FROM [SALES_ORDERS] GROUP BY [CUSTOMER_NUMBER] HAVING [CUSTOMER_NUMBER]= 6871 SELECT SUM(CASE WHEN [ORDER_DATE] >= '20100101' AND [ORDER_DATE] < '20100201' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as January , SUM(CASE WHEN [ORDER_DATE] >= '20100201' AND [ORDER_DATE] < '20100301' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as February , SUM(CASE WHEN [ORDER_DATE] >= '20100301' AND [ORDER_DATE] < '20100401' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as March , SUM(CASE WHEN [ORDER_DATE] >= '20100401' AND [ORDER_DATE] < '20100501' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as April , SUM(CASE WHEN [ORDER_DATE] >= '20100501' AND [ORDER_DATE] < '20100601' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as May , SUM(CASE WHEN [ORDER_DATE] >= '20100601' AND [ORDER_DATE] < '20100701' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as June , SUM(CASE WHEN [ORDER_DATE] >= '20100701' AND [ORDER_DATE] < '20100801' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as July , SUM(CASE WHEN [ORDER_DATE] >= '20100801' AND [ORDER_DATE] < '20100901' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as August , SUM(CASE WHEN [ORDER_DATE] >= '20100901' AND [ORDER_DATE] < '20101001' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as September , SUM(CASE WHEN [ORDER_DATE] >= '20101001' AND [ORDER_DATE] < '20101101' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as October , SUM(CASE WHEN [ORDER_DATE] >= '20101101' AND [ORDER_DATE] < '20101201' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as November , SUM(CASE WHEN [ORDER_DATE] >= '20101201' AND [ORDER_DATE] < '20110101' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as December , SUM([TOTAL_ORDER_VALUE]) as Total /*Gets the total for the whole year*/ FROM [SALES_ORDERS] WHERE 6871 = [CUSTOMER_NUMBER] AND '20100101' <= [ORDER_DATE] AND [ORDER_DATE] < '20110101' DROP TABLE [SALES_ORDERS]
In theory, theory and practice are identical. In practice, theory and practice are unrelated.

032712, 11:02 #7World Class Flame Warrior
 Join Date
 Jun 2003
 Location
 Ohio
 Posts
 12,595
Provided Answers: 1If you need this to run lightning fast, consider add computed columns to your table for Year and Month, or create an indexed view against the table with those fields precalculated and indexed.
If it's not practically useful, then it's practically useless.
blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com