Dynamic date range for each row?
in [Using MS SQL Server]
|
Prev: Error message on using union statement with order by using reserved words
Next: Default storage space - DB engine tuning advisor
From: lee.richmond on 29 Jan 2008 13:46 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!
From: Roy Harvey (SQL Server MVP) on 29 Jan 2008 15:08 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" <Richmolj(a)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!
From: jefftyzzer on 31 Jan 2008 12:55 On Jan 29, 10:46 am, "lee.richmond" <Richm...(a)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
From: Gert-Jan Strik on 31 Jan 2008 14:30 jefftyzzer wrote: > > On Jan 29, 10:46 am, "lee.richmond" <Richm...(a)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
From: Erland Sommarskog on 31 Jan 2008 17:23
Gert-Jan Strik (sorry(a)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/ViewFeedback.aspx?FeedbackID=254387 There is also one for RANGE and ROWS https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.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/SearchResults.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(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |