Dynamic date range for each row?

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!

Re: Dynamic date range for each row?

I assume you are looking for something beyond just retrieving the last
five orders - say using the data in the Orders table to control the
data being retrieved from some other table entirely.

This gets the date range for the most recent five orders by customer.

SELECT CustomerID,
MIN(OrderDate) as StartDate,
MAX(OrderDate) as EndDate
FROM Orders as A
WHERE OrderID IN
(SELECT TOP 5 B.OrderID
FROM Orders as B
WHERE A.CustomerID = B.CustomerID
ORDER BY B.OrderDate DESC)
GROUP BY CustomerID

I will leave it to you to apply that data to whatever table is
required.

Roy Harvey
Beacon Falls, CT

On Tue, 29 Jan 2008 10:46:47 -0800 (PST), "lee.richmond"
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!

Re: Dynamic date range for each row?

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

Re: Dynamic date range for each row?

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

Re: Dynamic date range for each row?

Gert-Jan Strik (sorry@toomuchspamalready.nl) writes:
> 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.

Yep. There is a request on ORDER BY for aggregates on Connect:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedbac k.aspx?FeedbackID=254387

There is also one for RANGE and ROWS
https://connect.microsoft.com/SQLServer/feedback/ViewFeedbac k.aspx?FeedbackID=254392

There are also a whole more requests for enhancements to the OVER clause,
most of them taken from the ANSI standard. You view a list on:
https://connect.microsoft.com/SQLServer/feedback/SearchResul ts.aspx?SearchQuery=%22OVER+clause+enhancement+request%22

Would this come in SQL 2008 (it does not seem so), SQL 2008 would be
really hot.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx