Results 1 to 7 of 7
  1. #1
    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] >= '2010-01-01' AND [ORDER_DATE] < '2010-02-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as January,
    SUM(CASE WHEN [ORDER_DATE] >= '2010-02-01' AND [ORDER_DATE] < '2010-03-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as February,
    SUM(CASE WHEN [ORDER_DATE] >= '2010-03-01' AND [ORDER_DATE] < '2010-04-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as March,
    SUM(CASE WHEN [ORDER_DATE] >= '2010-04-01' AND [ORDER_DATE] < '2010-05-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as April,
    SUM(CASE WHEN [ORDER_DATE] >= '2010-05-01' AND [ORDER_DATE] < '2010-06-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as May,
    SUM(CASE WHEN [ORDER_DATE] >= '2010-06-01' AND [ORDER_DATE] < '2010-07-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as June,
    SUM(CASE WHEN [ORDER_DATE] >= '2010-07-01' AND [ORDER_DATE] < '2010-08-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as July,
    SUM(CASE WHEN [ORDER_DATE] >= '2010-08-01' AND [ORDER_DATE] < '2010-09-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as August,
    SUM(CASE WHEN [ORDER_DATE] >= '2010-09-01' AND [ORDER_DATE] < '2010-10-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as September,
    SUM(CASE WHEN [ORDER_DATE] >= '2010-10-01' AND [ORDER_DATE] < '2010-11-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as October,
    SUM(CASE WHEN [ORDER_DATE] >= '2010-11-01' AND [ORDER_DATE] < '2010-12-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as November,
    SUM(CASE WHEN [ORDER_DATE] >= '2010-12-01' AND [ORDER_DATE] < '2011-01-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as December,
    
    /*Gets the total for the whole year*/
    SUM(CASE WHEN [ORDER_DATE] >= '2010-01-01' AND [ORDER_DATE] < '2011-01-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as Total
    FROM [SALES_ORDERS] GROUP BY [CUSTOMER_NUMBER] HAVING [CUSTOMER_NUMBER]= 6871
    Thanks in advance

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The 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.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would expect this code to be significantly more efficient:
    Code:
    SELECT
       SUM(CASE WHEN [ORDER_DATE] >= '2010-01-01' AND [ORDER_DATE] < '2010-02-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as January
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-02-01' AND [ORDER_DATE] < '2010-03-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as February
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-03-01' AND [ORDER_DATE] < '2010-04-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as March
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-04-01' AND [ORDER_DATE] < '2010-05-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as April
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-05-01' AND [ORDER_DATE] < '2010-06-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as May
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-06-01' AND [ORDER_DATE] < '2010-07-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as June
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-07-01' AND [ORDER_DATE] < '2010-08-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as July
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-08-01' AND [ORDER_DATE] < '2010-09-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as August
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-09-01' AND [ORDER_DATE] < '2010-10-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as September
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-10-01' AND [ORDER_DATE] < '2010-11-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as October
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-11-01' AND [ORDER_DATE] < '2010-12-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as November
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-12-01' AND [ORDER_DATE] < '2011-01-01' 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 '2010-01-01' <= [ORDER_DATE]
          AND [ORDER_DATE] < '2011-01-01'
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Mar 2012
    Posts
    2

    a

    Quote Originally Posted by Pat Phelan View Post
    I would expect this code to be significantly more efficient:
    Code:
    SELECT
       SUM(CASE WHEN [ORDER_DATE] >= '2010-01-01' AND [ORDER_DATE] < '2010-02-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as January
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-02-01' AND [ORDER_DATE] < '2010-03-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as February
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-03-01' AND [ORDER_DATE] < '2010-04-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as March
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-04-01' AND [ORDER_DATE] < '2010-05-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as April
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-05-01' AND [ORDER_DATE] < '2010-06-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as May
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-06-01' AND [ORDER_DATE] < '2010-07-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as June
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-07-01' AND [ORDER_DATE] < '2010-08-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as July
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-08-01' AND [ORDER_DATE] < '2010-09-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as August
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-09-01' AND [ORDER_DATE] < '2010-10-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as September
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-10-01' AND [ORDER_DATE] < '2010-11-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as October
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-11-01' AND [ORDER_DATE] < '2010-12-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as November
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-12-01' AND [ORDER_DATE] < '2011-01-01' 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 '2010-01-01' <= [ORDER_DATE]
          AND [ORDER_DATE] < '2011-01-01'
    -PatP
    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'.
    I didn't think you could use a WHERE Statement in a aggregated function.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by vandel212 View Post
    When I run it I get this as my error:
    If your statement worked as you posted it, then mine ought to work too.
    Quote Originally Posted by vandel212 View Post
    I didn't think you could use a WHERE Statement in a aggregated function.
    I'm not sure what you mean by this statement, but you can certainly use a WHERE clause in a SELECT staement that uses aggregate functions.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just 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, '2009-01-09')
    ,     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] >= '2010-01-01' AND [ORDER_DATE] < '2010-02-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as January,
    SUM(CASE WHEN [ORDER_DATE] >= '2010-02-01' AND [ORDER_DATE] < '2010-03-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as February,
    SUM(CASE WHEN [ORDER_DATE] >= '2010-03-01' AND [ORDER_DATE] < '2010-04-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as March,
    SUM(CASE WHEN [ORDER_DATE] >= '2010-04-01' AND [ORDER_DATE] < '2010-05-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as April,
    SUM(CASE WHEN [ORDER_DATE] >= '2010-05-01' AND [ORDER_DATE] < '2010-06-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as May,
    SUM(CASE WHEN [ORDER_DATE] >= '2010-06-01' AND [ORDER_DATE] < '2010-07-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as June,
    SUM(CASE WHEN [ORDER_DATE] >= '2010-07-01' AND [ORDER_DATE] < '2010-08-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as July,
    SUM(CASE WHEN [ORDER_DATE] >= '2010-08-01' AND [ORDER_DATE] < '2010-09-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as August,
    SUM(CASE WHEN [ORDER_DATE] >= '2010-09-01' AND [ORDER_DATE] < '2010-10-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as September,
    SUM(CASE WHEN [ORDER_DATE] >= '2010-10-01' AND [ORDER_DATE] < '2010-11-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as October,
    SUM(CASE WHEN [ORDER_DATE] >= '2010-11-01' AND [ORDER_DATE] < '2010-12-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as November,
    SUM(CASE WHEN [ORDER_DATE] >= '2010-12-01' AND [ORDER_DATE] < '2011-01-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as December,
    
    /*Gets the total for the whole year*/
    SUM(CASE WHEN [ORDER_DATE] >= '2010-01-01' AND [ORDER_DATE] < '2011-01-01' 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] >= '2010-01-01' AND [ORDER_DATE] < '2010-02-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as January
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-02-01' AND [ORDER_DATE] < '2010-03-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as February
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-03-01' AND [ORDER_DATE] < '2010-04-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as March
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-04-01' AND [ORDER_DATE] < '2010-05-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as April
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-05-01' AND [ORDER_DATE] < '2010-06-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as May
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-06-01' AND [ORDER_DATE] < '2010-07-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as June
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-07-01' AND [ORDER_DATE] < '2010-08-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as July
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-08-01' AND [ORDER_DATE] < '2010-09-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as August
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-09-01' AND [ORDER_DATE] < '2010-10-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as September
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-10-01' AND [ORDER_DATE] < '2010-11-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as October
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-11-01' AND [ORDER_DATE] < '2010-12-01' THEN [TOTAL_ORDER_VALUE] ELSE 0 END) as November
    ,  SUM(CASE WHEN [ORDER_DATE] >= '2010-12-01' AND [ORDER_DATE] < '2011-01-01' 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 '2010-01-01' <= [ORDER_DATE]
          AND [ORDER_DATE] < '2011-01-01'
    
    DROP TABLE [SALES_ORDERS]
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If 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 pre-calculated and indexed.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •