sum weekly data into monthly

[drunkle]

I have data in weekly buckets which my column names are the in the following
format - SKU, 04262010, 05032010, 05102010 etc. where 04262010 is the
week ending 4/26/2010

I have a second table that assoicates the column name to a fiscal month in
this format -

DATE Fiscal Month
20100426 6
20100503 6
20100510 6
20100517 6

I want to sum by sku for fiscal month.

 

[Daryl S]

Drunkle -

This would be much easier if your table structure was correct. Your table
should have columns for SKU, WeekEndDate, and Amount, and the WeekEndDate
should be a date field. Since column names are not variable, no matter what
you do now for a query will need to be changed as dates are added to the
table. If you can, fix your tables, then the query will be simple.

If not, you may have to do this manually, something like this (though you
will need to sum each set of week-end-dates into a fiscal month:

SELECT SKU, [04262010] + [05032010] + [05102101] + [05172010] AS FiscalMonth6
FROM YourTableName;

Also, if you data could be blank, you will want to use nz, like this:

SELECT SKU, nz([04262010],0) + nz([05032010],0) + nz([05102101],0) +
nz([05172010],0) AS FiscalMonth6
FROM YourTableName;

 

[vvariety]

I have data in weekly buckets which my column names are the in the following
format   -   SKU, 04262010, 05032010, 05102010 etc. where 04262010 isthe
week ending 4/26/2010

I have a second table that assoicates the column name to a fiscal month in
this format -

 DATE   Fiscal Month
20100426        6
20100503        6
20100510        6
20100517        6

I want to sum by sku for fiscal month.

How are your two tables associated?

 

[mcescher]

I have data in weekly buckets which my column names are the in the following
format   -   SKU, 04262010, 05032010, 05102010 etc. where 04262010 isthe
week ending 4/26/2010

I have a second table that assoicates the column name to a fiscal month in
this format -

 DATE   Fiscal Month
20100426        6
20100503        6
20100510        6
20100517        6

I want to sum by sku for fiscal month.

So, does your table just get wider each week? Daryl S' suggestion to
have a properly normalized table would be a much better solution. In
fact, you will run out of columns at some point.

With a more correct layout, you could eliminate the second table and
just calculate the fiscal month based on your date.