Dynamic range of data? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic range of data?

Author

Topic

richmolj
Starting Member

3 Posts

Posted - 2008-01-29 : 13:44:09

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!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-29 : 14:22:24

Not sure what you're asking. Could you please "set up" the scenario with a CREATE TABLE statement and INSERT statements for some sample rows. Then post the expected results based on your "variable".

example of what I'm asking for:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

richmolj
Starting Member

3 Posts

Posted - 2008-01-29 : 15:01:28

Here's a tuned down version of what I have set up. I don't think I'm at the stage I can give you what you're asking:

Keyword Orders Date Impressions
KeyA 2 Jan 1 1000
KeyB 4 Jan 1 500
KeyC 1 Jan 1 500
KeyA 2 Jan 2 1000
KeyB 4 Jan 2 500
KeyC 1 Jan 2 500
KeyA 2 Jan 3 1000
KeyB 4 Jan 3 500
KeyC 1 Jan 3 500

Let's say my order threshold is 5: whenever I have 5 orders I want to know the total impressions during that date range. So for KeyA it would be Jan 1 through Jan 3, with 3000 impressions. But for KeyB the date range is only Jan 1 through Jan 2, 1000 impressions. KeyC never gets enough orders to satisfy the critera, so it returns a null.

The purpose of this is along the lines of determining a reliable conversion rate that changes over time ... I can't declare a conversion rate until I have X orders, and X varies by keyword.

I want the user to be able to input last 10, or last 50 order threshold ont he fly, as well.

Any ideas?

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-01-29 : 15:47:42

I don't think you can do it easily with a single SQL statement. There may be a better/faster way than this, but you could create a function that accepted the keyword and number of orders value and returns the earliest date. Then you could use the date returned as the beginning of the date range in your SQL statement. Your SQL in the function would look like:

CREATE FUNCTION EarliestOrderDate (@Threshold int, @Keyword varchar)
RETURNS datetime
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @EarliestDate datetime
SET @EarliestDate = (SELECT MIN(a.Date) FROM (SELECT TOP @Threshold Date FROM OrdersTable WHERE Keyword=@Keyword ORDER BY Date DESC) a)
RETURN @EarliestDate
END
GO

Then you would call the function in a query like:

SELECT Keyword, AVG(Impressions) FROM OrdersTable
WHERE Date >= dbo.EarliestOrderDate(5,Keyword)
GROUP BY Keyword

I tried this approach on one of my database and it did work. Seems like there should be a faster approach, but it may just be that I don't have my database optimized for this type of query.

Hope that helps.

Tom Rupsis
Granite Peak Systems
Phone: 406-672-8292
Email: trupsis@granitepeaksys.com
LinkedIn: www.linkedin.com/in/trupsis

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 17:31:02

[code]-- Prepare sample data
DECLARE @Sample TABLE (Keyword VARCHAR(4), Orders TINYINT, Date SMALLDATETIME, Impressions SMALLINT)

INSERT @Sample
SELECT 'KeyA', 2, 'Jan 1, 2008', 1000 UNION ALL
SELECT 'KeyB', 4, 'Jan 2, 2008', 500 UNION ALL
SELECT 'KeyC', 1, 'Jan 1, 2008', 500 UNION ALL
SELECT 'KeyA', 2, 'Jan 2, 2008', 1000 UNION ALL
SELECT 'KeyB', 4, 'Jan 3, 2008', 500 UNION ALL
SELECT 'KeyC', 1, 'Jan 2, 2008', 500 UNION ALL
SELECT 'KeyA', 2, 'Jan 3, 2008', 1000 UNION ALL
SELECT 'KeyB', 4, 'Jan 4, 2008', 500 UNION ALL
SELECT 'KeyC', 1, 'Jan 3, 2008', 500

-- Prepare threshold parameter
DECLARE @Threshold INT

SET @Threshold = 5

-- Initialize the common table expression
;WITH Yak (Keyword, Orders, Date, Impressions, RecID)
AS (
SELECT Keyword,
Orders,
Date,
Impressions,
RecID
FROM (
SELECT Keyword,
Orders,
Date,
Impressions,
ROW_NUMBER() OVER (PARTITION BY Keyword ORDER BY Date) AS RecID
FROM @Sample
) AS f
WHERE RecID = 1

UNION ALL

SELECT s.Keyword,
s.Orders + y.Orders,
s.Date,
s.Impressions + y.Impressions,
s.RecID
FROM Yak AS y
INNER JOIN (
SELECT Keyword,
Orders,
Date,
Impressions,
RecID
FROM (
SELECT Keyword,
Orders,
Date,
Impressions,
ROW_NUMBER() OVER (PARTITION BY Keyword ORDER BY Date) AS RecID
FROM @Sample
) AS f
) AS s ON s.Keyword = y.Keyword
WHERE y.RecID = s.RecID - 1
AND y.Orders < @Threshold
)

-- Show the expected output
SELECT Keyword,
MAX(Orders) AS Orders,
CASE
WHEN MAX(Orders) >= @Threshold THEN MIN(Date)
ELSE NULL
END AS minDate,
CASE
WHEN MAX(Orders) >= @Threshold THEN MAX(Date)
ELSE NULL
END AS maxDate,
CASE
WHEN MAX(Orders) >= @Threshold THEN MAX(Impressions)
ELSE NULL
END AS Impressions
FROM Yak
GROUP BY Keyword
ORDER BY Keyword[/code]

E 12°55'05.25"
N 56°04'39.16"

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-01-29 : 17:51:53

Wow, that's impressive, Peso. I re-read the question more closely and it looks like I misunderstood. But I still wouldn't have come up with that.

Tom Rupsis
Granite Peak Systems
Phone: 406-672-8292
Email: trupsis@granitepeaksys.com
LinkedIn: www.linkedin.com/in/trupsis

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 18:11:25

Thanks.

E 12°55'05.25"
N 56°04'39.16"

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-01-29 : 18:14:06

I just figured out what you did. I'd never seen recursive sql before. I have some studying to do!

Tom Rupsis
Granite Peak Systems
Phone: 406-672-8292
Email: trupsis@granitepeaksys.com
LinkedIn: www.linkedin.com/in/trupsis

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 18:20:53

CTE's are available in SQL Server 2005 and later only.

E 12°55'05.25"
N 56°04'39.16"

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-01-29 : 18:28:18

I'd used CTEs before, just never in a recursive manner. Could be quite dangerous if you're not careful. Thanks for showing us how they work!

Tom Rupsis
Granite Peak Systems
Phone: 406-672-8292
Email: trupsis@granitepeaksys.com
LinkedIn: www.linkedin.com/in/trupsis

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 18:29:51

Here is another post using CTE
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=87073

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources