I have a data table that contains weekly data.
The table has a period date column which tells the start of the week.
Some users would like to view the data as monthly numbers...

Are there any SQL tricks to allow me to easily show weekly data as
monthly data?

Here's a sample of the table format...

Item | Date | Value
123 | 02/03/2003 | 100
123 | 02/09/2003 | 60
...
123 | 09/19/2003 | 60
124 | 02/03/2003 | 80
124 | 02/09/2003 | 40
...
124 | 09/19/2003 | 40

i'm not sure that I undestood you need...
why don't you make a group by on first colun and month of the second column the summing the third?
There is the problem of the year...then you have to add year ....

You don't say what DBMS you are using, and the solution to this is DBMS-specific. I will show you the Oracle solution, other databases will probably have a similar (but different) function:

SELECT item, TRUNC( datecol, 'MONTH' ) month, SUM( value ) month_value
FROM mytable
GROUP BY item, TRUNC( datecol, 'MONTH' );

Item | Month | Month_Value
123 | 02/01/2003 | 160
...
123 | 09/01/2003 | 60
124 | 02/01/2003 | 120
...
124 | 09/01/2003 | 40

This has "truncated" the date to the 1st of the month.
If you wanted the month displayed differently, you would format it. e.g.

TO_CHAR( datecol, 'MM/YYYY' )

would show 02/2003, 09/2003 etc. Again, this is Oracle-specific, other DBMSs' functions will be a bit different.

If you are not on Oracle, search your DBMS's documentation for "date functions".

I am using ORACLE 8.1.4,

With the solution given, I am unable to account for a week that falls between two months.
For example if the week started on 09/26/2003 and qty was 70, then
because 4 days of the week fall in September and three days fall in October, I would like to get some allocation between....Currently, I am not very picky on the allocation used....

Is there something I can do here......

Thanks in advance!!!

You will need to do the UNION of 2 queries:
1) Query for month in which the week begins - returning value * fraction of week if it goes over the month end
2) Query for month in which the week ends (if that will be different) - returning value * fraction of week

I believe this will do it:

SELECT month, SUM(month_value) FROM
(
SELECT TRUNC(datecol,'MONTH') month, value*LEAST( LAST_DAY(datecol)-datecol+1, 7 ) / 7 month_value
FROM mytable
UNION ALL
SELECT TRUNC(datecol+6,'MONTH') month, value*(datecol+6-TRUNC(datecol+6,'MONTH')+1) / 7 month_value
FROM mytable
WHERE TRUNC(datecol+6,'MONTH') > TRUNC(datecol,'MONTH')
)
GROUP BY month;

That was a perfect solution, thanks for all your help....