Posted by Gert-Jan Strik on 01/30/08 19:30

jefftyzzer wrote:
>
> On Jan 29, 10:46 am, "lee.richmond" <Richm...@gmail.com> wrote:
> > Hi,
> >
> > I'm trying to group data by date range, but each row of data could
> > have a different date range based on a variable.
> >
> > I want to say "look at the date range the paste five orders were
> > placed" for each row individually. As an example, think of the rows as
> > keywords in a Search Marketing program. Keyword X had 5 orders placed
> > in the last week, Keyword Y had 5 orders placed in the last 2 weeks. I
> > want each keyword to display its average impressions over the course
> > of its respective date range.
> >
> > Is this possible?
> >
> > Thanks in advance!
>
> Lee,
>
> If (your version of) SQL Server implements them, you may want to look
> at the windowing functions, and specifically the framing clause.
> Here's an example of a simple moving average:
>
> SELECT
> keyword,
> avg(qty) over (order by orderdate range between 5 preceding and
> current row) as n
> from
> orders
>
> The important part is the "range between 5 preceding and current row"
>
> --Jeff

Jeff,

According to SQL Server 2005's Books Online, aggregate window functions
only support partitioning by a column. IOW, AFAIK SQL Server does not
(yet?) support PRECEDING or CURRENT ROW as windowing selectors.

--
Gert-Jan

• Next in forum: Create Table with Variable included in Table Name
• Prev in forum: Re: Dynamic date range for each row?
• Thread view: Re: Dynamic date range for each row?

[Reply to this message]