List:General Discussion« Previous MessageNext Message »
From:mos Date:October 28 2006 6:22am
Subject:How to summarize weekly totals?
View as plain text  
I can't get my head around this, but I think I should be able to do it 
using just SQL without writing a program.

I have a simple table that looks like:

Rcd_Id: Integer;
Sale_Date: Date;
Sale_Amt: Double;

Each date will have 1 row with the total sales for that day.

and I want to sum the weekly totals so it looks like:

WeekEnding    TotalAmt
2006-10-14            3899.34
2006-10-21              222.12
2006-10-28          33122.12

So for each week, it will sum the sales for Monday through Saturday and 
provides a total column. It will do this for all the rows in the table. If 
there was a years worth of data, there would be 52 weeks hence 52 rows of 
data would be produced.

Some days may be missing if it is a holiday, which means I can't guarantee 
each week will have a sales row for Monday or Saturday.

Do I need to write a program to do this? Or can it be done with SQL? TIA

How to summarize weekly totals?mos28 Oct
  • Re: How to summarize weekly totals?Peter Brawley28 Oct