Re: Dynamic date range for each row?

jefftyzzer wrote:

On Jan 29, 10:46 am, "lee.richmond" <Richm...@xxxxxxxxx> 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
.

Relevant Pages

  • Re: Dynamic date range for each row?
    ... I'm trying to group data by date range, but each row of data could ... Keyword Y had 5 orders placed in the last 2 weeks. ... want each keyword to display its average impressions over the course ... The important part is the "range between 5 preceding and current row" ...
    (comp.databases.ms-sqlserver)
  • Dynamic date range for each row?
    ... I'm trying to group data by date range, but each row of data could ... Keyword Y had 5 orders placed in the last 2 weeks. ... want each keyword to display its average impressions over the course ...
    (comp.databases.ms-sqlserver)
  • Re: Dynamic date range for each row?
    ... I assume you are looking for something beyond just retrieving the last ... This gets the date range for the most recent five orders by customer. ... I'm trying to group data by date range, but each row of data could ... Keyword Y had 5 orders placed in the last 2 weeks. ...
    (comp.databases.ms-sqlserver)