Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Location
    Melbourne, Australia
    Posts
    2

    Unanswered: SQL newbie needing help with query!

    In what I'm hoping is a fairly straightforward query for someone who knows what they're doing, I'm trying to retrieve customer spending summary details by month as follows:

    CustomerID CustomerName JanSales FebSales MarSales ... DecSales

    The relevant customer table ("custrec") fields are "cuscode" and "cusname", and the invoice table ("invoice") has the fields "date" and "invtot".

    Now for an extra restriction: The database I'm using (Advantage Database Server via its ODBC driver) doesn't seem to allow derived tables (i.e. no nested SELECTs within the FROM clause).

    At present, I've managed to get the query working by creating views for each month and combining them into a single query along the following lines:

    CREATE VIEW janview AS
    SELECT c.cuscode, SUM(i.invtot - i.invtax) as jansales
    FROM custrec c, invoice i
    WHERE c.cuscode = i.acccode
    AND i.date >= '1/1/2003'
    AND i.date <= '1/31/2003'
    GROUP BY c.cuscode

    SELECT c.cuscode, c.cusname, jansales, febsales, ...
    FROM custrec c LEFT OUTER JOIN jansales jan ON c.cuscode = jan.cuscode LEFT OUTER JOIN febsales feb ON c.cuscode = feb.cuscode ...
    ORDER BY c.cusname

    This is working but it's impressively slow on the data I need to query. If anyone can offer a suggestion or two as to how to do this query more efficiently, I'd be most grateful.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    PHP Code:
    select c.cuscode
         
    sum(case when month(i.date) = 1
                    then i
    .invtot i.invtax
                    
    else 0 end
              
    ) as jansales
         
    sum(case when month(i.date) = 2
                    then i
    .invtot i.invtax
                    
    else 0 end
              
    ) as febsales
        
    ...      
         , 
    sum(case when month(i.date) = 12
                    then i
    .invtot i.invtax
                    
    else 0 end
              
    ) as decsales
      from custrec c
    inner
      join invoice i
        on c
    .cuscode i.acccode
    group
        by c
    .cuscode 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    Melbourne, Australia
    Posts
    2
    Thank-you!

    That's made an incredible difference to the speed of the query... 'takes about 1/7th the time to execute.

Posting Permissions

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