Re: Aggregating by time period

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Greg Linwood (g_linwoodQhotmail.com)
Date: 03/09/04 

Date: Tue, 9 Mar 2004 15:45:02 +1100

Hi David.

It might help if we had the rest of your query, but I suspect you're after
the DATEPART() function..

eg: select datepart(wk, getdate())

or in a query:

set nocount on
go
create table trades (
   tradeid int not null identity (1, 1)
 , tradedt smalldatetime not null
 , tradeamnt money not null
)
go
insert into trades (tradedt, tradeamnt) values ('20031216', 40)
insert into trades (tradedt, tradeamnt) values ('20040101', 5)
insert into trades (tradedt, tradeamnt) values ('20040102', 5)
insert into trades (tradedt, tradeamnt) values ('20040115', 20)
insert into trades (tradedt, tradeamnt) values ('20040201', 30)
go
select datepart(wk, tradedt) as tradewk, sum(tradeamnt) as tradeamnt
from trades
where tradedt between '20040101 00:00:00' and '20041231 11:59:59'
group by datepart(wk, tradedt)
go
drop table trades
go
--giving:
tradewk tradeamnt
       1 10.0000
       3 20.0000
       6 30.0000

Regards,
Greg Linwood
SQL Server MVP

"David F" <davef@nksj.ru> wrote in message
news:e8sw29YBEHA.576@TK2MSFTNGP11.phx.gbl...
> I have a list of daily stock prices extending over a period of a year.
>
> I want to return the average for each week.
>
> IOW, I want 52 rows showing the average price for each 5 day trading week
> for the entire 1 year period.
>
> How do I write the GROUP BY to get the average for a week?
>
>