Need help with a counting query.

  • Thread starter Thread starter Tbird2340
  • Start date Start date
T

Tbird2340

I am using the following query:

SELECT UserName, Sum(Abs(DateSubmitted = Date())) as SubCurrentDay,
Sum(Abs(DateSubmitted >= Date()-7)) as SubPast7, Sum(Abs(DateSubmitted >=
Date()-30)) as SubPast30
FROM HelpDesk GROUP BY UserName

This query works fine and gives me the results I want but what it doesn't do
is order them the way I want. I tried adding "ORDER BY SubPast30 DESC" to
the end of the query but it doesn't work. I am trying to display the top 10
users that submit help requests and the amount they have Today, for the past
7 days, and the past 30 days.

Please let me know if there is anyway I can get my results sorted the way I
want.

Thank you very much. Tom
 
You need to use

ORDER BY Sum(Abs([DateSubmitted]>=Date()-30)) DESC

as the fieldname is not defined until one of the last
parts of running the query and therefore cannot be sorted
on.
 
Sorry, I forgot to mention that using SELECT TOP 10
instead of SELECT will limit the search results to the
top 10.
 
Back
Top