Another attempt - BIG Query - 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

SQL Server Development (2000)

Another attempt - BIG Query

Author

Topic

jesus4u
Posting Yak Master

204 Posts

Posted - 2002-10-17 : 13:54:56

The first pic is what I am after.
The second pic is the view I currently have.
How do I get the other 2 columns of data per Domain?
Thanks

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-10-17 : 16:15:47

This isn't tested...


select tu.DomainName,
  (select count(TrackID) from the TheTracking tt
    inner join TheUserDetails tud on tt.TrackID = tud.TrackID
    where tud.UserID = tu.UserID and
    month(tt.HitDate) = month(getdate()) ) MTD,

  (select count(TrackID) from the TheTracking tt1
    inner join TheUserDetails tud1 on tt1.TrackID = tud1.TrackID
    where tud1.UserID = tu.UserID and
    year(tt1.HitDate) = year(getdate()) ) YTD,

  (select count(TrackID) from the TheTracking tt2
    inner join TheUserDetails tud2 on tt2.TrackID = tud2.TrackID
    where tud2.UserID = tu.UserID and
    month(tt2.HitDate) - 1 = month(getdate()) -1 ) [Last Month]
  
from TheUsers tu

hth,
Justin

Have you hugged your SQL Server today?

Edited by - justinbigelow on 10/17/2002 16:16:06

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-10-18 : 04:19:02

The month criteria need fixing. At the moment they test if the calendar date of the hit is the same as the current date (irrespective of year). During January, the one for 'last month' doesn't work at all.
This month:
DATEDIFF(month, tt.HitDate, getdate()) = 0
Last month:
DATEDIFF(month, tt.HitDate, getdate()) = 1

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-10-18 : 08:53:25

quote:

The month criteria need fixing. At the moment they test if the calendar date of the hit is the same as the current date (irrespective of year). During January, the one for 'last month' doesn't work at all.
This month:
DATEDIFF(month, tt.HitDate, getdate()) = 0
Last month:
DATEDIFF(month, tt.HitDate, getdate()) = 1

Good catch, my mistake.

Justin

Have you hugged your SQL Server today?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-10-18 : 09:20:09

Don't worry -- as far as I can make out the Flyspeck 3 text, it was wrong in the original picture too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources