Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2011
    Posts
    2

    Unanswered: Weekly sales data query help

    I am not a complete newbie to SQL or query writing, but this one has got me stumped.

    I have a weekly sales gathering database, sales_data, that comes in as such:

    location_id, reporting_date, sales

    I generate a report that takes the format:

    location_id | week_date1 | week_date2 | .....
    853 | 12000 | 13444 | .....
    989 | 32400 | 36762 | .....

    I have been manually building and extending a report that pulls the data by location into date columns like this:

    Code:
    SELECT location_id, 
    SUM(if(reporting_date = '2010-11-30',sales, 0)) as '2010-11-30',
    SUM(if(reporting_date = '2010-12-07',sales, 0)) as '2010-12-07'
    ....
    FROM sales_data
    GROUP BY location_id
    The problem is that I don't want to continue writing and extending this report for each week date. Looking for suggestions to make the SUM(IF(...)) generate with some sort of grouping command.

    Any assistance or insight on this would be helpful.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider creating a table which identifies what comprises a week then join that tabel to the main data, and it gives you the Week ID. group on that....

    then parametrise your query, using, say a stored procedure. supply whatever parameter(s) you need. those parameters could be the end date AND the number of weeks.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2011
    Posts
    2

    clarification

    For clarification, there is really only one entry per location per period. I am only using the sum command to pull that single entry.

    I have thought of making a date dimension table, but not sure how that helps, per se, but will reread your comment to try to understand better.

    Thanks for the reply.

Posting Permissions

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