I have a table say CLFILE which has many entries each one has a date and an amount which can be either positive or negative.

To sum the balance up to a certain date I would write -
SELECT SUM(CLCASH) AS WEEK30 FROM CLFILE WHERE CLDATE < '20070628'

This would give say -

WEEK30
--------
203.45

Do any of you SQL wizards know of a way to display what the balance was each week without having to do 52 queries?
Any help would be appreciated.
Dave

Hi,

Try this:
SELECT sum(clcash),weekofyear(cldate) FROM clfile GROUP BY weekofyear(cldate);

Please, consult your database engine's documentation about the WEEKOFYEAR function.

Hope this helps,

Dimitar