Re: Dynamic date range for each row?

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

.

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. ... at the windowing functions, ... 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)