Query to get values by user by day, week, month...

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

SQL Server Development (2000)

Query to get values by user by day, week, month...

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-07-27 : 06:09:12

Let's say I have a table "items", with a schema like this:

create table items (
   i INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
   i_users INT NOT NULL,
   datetime SMALLDATETIME NOT NULL DEFAULT(GETDATE()))
   )

Say I want to report on how many entries there are per i_users on a daily, weekly, and monthly basis.

Right now, I have an ASP page which looks something like this pseudocode:

szSQL="select distinct i_users from items order by i_users desc"
Set oRS=oDB.Execute(szSQL)
Do while not oRS.EOF
  iUsers=CLng(oRS("i_users"))
  szSQL2="select count(*) as x from items where i_users=" & iUsers & " and datetime>=dateadd(hour,-24,getdate())"
  Set oQS=oDB.execute(szSQL2)
  iDaily=CLng(oQS("x"))
  szSQL2="select count(*) as x from items where i_users=" & iUsers & " and datetime>=dateadd(hour,-168,getdate())"
  Set oQS=oDB.execute(szSQL2)
  iWeekly=CLng(oQS("x"))
  szSQL2="select count(*) as x from items where i_users=" & iUsers & " and datetime>=dateadd(hour,-720,getdate())"
  Set oQS=oDB.execute(szSQL2)
  iMonthly=CLng(oQS("x"))
  Response.Write("User: " & iUsers & ", " & iDaily & "," & iWeekly & "," & iMonthly & vbCrLF
 oRs.MoveNext
Loop

...it works, it it causes 4 select queries per record. The is a nonclustered index on the [datetime] field, so it's not horrendously expensive, but still, it feels pretty inefficient.

Is there a way to rewrite that into a single query that will return the daily, weekly, and monthly values?

Cheers
-b

Edited by - aiken on 07/27/2002 06:29:23

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-07-27 : 07:19:04

I would have thought that counting cases would be your best bet here, since it has to look at all the values to get the monthlies:


SELECT i_users,
  SUM(CASE WHEN datetime >= DATEADD(hour, -24, GETDATE()) THEN 1 ELSE 0 END) AS daily,
  SUM(CASE WHEN datetime >= DATEADD(hour, -168, GETDATE()) THEN 1 ELSE 0 END) AS weekly,
  COUNT(*) as monthly
FROM items
WHERE datetime >= DATEADD(hour, -720, GETDATE())
GROUP BY i_users
ORDER BY i_users DESC

Edited by - Arnold Fribble on 07/27/2002 07:20:39

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-08-04 : 04:22:17

Thank you -- this is a fantastic solution. Didn't know you could do that.

Cheers
-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources