List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:October 28 2006 1:22pm
Subject:Re: How to summarize weekly totals?
View as plain text  
/>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
/
Group by WEEK(sale_date), or build a calendar table and join from it.

PB

mos wrote:
> 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
>
> Mike
>

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.16/504 - Release Date: 10/27/2006
Thread
How to summarize weekly totals?mos28 Oct
  • Re: How to summarize weekly totals?Peter Brawley28 Oct