Ok, this one is difficult - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Ok, this one is difficult

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-06-30 : 02:46:15

...Usually, I stump myself with queries where I feel like they're on the tip of my tongue. This time, though, I'm lost. Yes, this looks like it's best left for analysis services, but I don't think we have it licensed, and anyways I'm scared of getting into it.

Here's a table of pageviews:

CREATE TABLE log_history_pageviews
 (datetime smalldatetime, pagename varchar(20), times int)

Now, I want to know the dates that we reached certain milestones. Say, one million pageviews/month, two million pageviews month, etc.

It's easy to find the first date that we reached one million pageviews/month:

select min(datetime)
from log_history_pageviews lhp
where (select sum(times)
	from log_history_pageviews lhp2
	where lhp2.datetime>=dateadd(Day,-30,lhp.datetime)
		and lhp2.datetime<=lhp.datetime
	) 
	> 1000000

...but what I want is a single query to return the dates when we first reached 1, 2, 3 ... X million pageviews a month. So rather than a single datetime result, I'm looking for:

Pageviews    Datetime
1000000      6/21/1998 00:00:00
2000000      9/30/1998 00:00:00
3000000      1/12/1999 00:00:00

..And so on.

I can see how to do it with a while loop and a counter for the current threshold; is there any more elegant (set-based) way?

Thanks
-b

(line breaks added for robvolk)

Edited by - aiken on 06/30/2003 02:53:20

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-06-30 : 03:51:14

Is this for cumulative totals?

If so, pull out your Numbers table and start cross joining....


select min(datetime) as MinDate,  N.Number * 1000000 as PageViews
from log_history_pageviews lhp CROSS JOIN Numbers N
where  (select sum(times) from log_history_pageviews lhp2 where lhp2.datetime>=dateadd(Day,-30,lhp.datetime)
	and lhp2.datetime<=lhp.datetime ) BETWEEN N.Number * 1000000 AND (N.Number + 1)* 1000000
AND N.Number < 20
GROUP BY N.Number * 1000000
ORDER BY MinDate asc

NB: The N.Number < 50 will restrict it to 50 Million.. Increase as needed.. If you don't have it there, the cross join becomes expensive...

EDIT: This is how I generated sample data...


Insert log_history_pageviews
	Select getdate() + Number*12, 'Dingo', Number*100000
	from Numbers 
	Where Number < 100

DavidM

"SQL-3 is an abomination.."

Edited by - byrmol on 06/30/2003 03:52:17

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-07-01 : 01:53:04

Damn. I'm impressed. I was convinced that it needed the while loop.

Thank you!

-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources