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:

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.

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.

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.