Top 10 users submitting tickets

[LGuth]

I would like to have a report showing the top 10 users that have submitted tickets by month and year.
I'm not real savvy on writing code.
Could someone help me?

Linda Guth
====

Maybe this report below that shows the top Ticket Types can be modified to show User Names instead?

Select Top 1000000 DatePart(yyyy, htblticket.date) As Year,
DatePart(mm, htblticket.date) As Month,
'../helpdesk/icons/' + htbltickettypes.icon As icon,
htbltickettypes.typename As Type,
Count(htblticket.ticketid) As TicketCount,
Cast((Count(htblticket.ticketid) / Cast(TicketCount.Amount As decimal) *
100) As decimal(10,2)) As [Type%]
From htblticket
Inner Join htbltickettypes On htbltickettypes.tickettypeid =
htblticket.tickettypeid
Inner Join (Select DatePart(yyyy, htblticket.date) As Year,
DatePart(mm, htblticket.date) As Month,
Count(htblticket.ticketid) As Amount
From htblticket
Where htblticket.spam <> 'True'
Group By DatePart(yyyy, htblticket.date),
DatePart(mm, htblticket.date)) As TicketCount On TicketCount.Year =
DatePart(yyyy, htblticket.date) And TicketCount.Month = DatePart(mm,
htblticket.date)

[ChuckS]

Well, what I have is not top 10, it's count by month, 3 month and year, but it's a starting point for ya.



per year

Select Top 1000000 htblusers.name As Name,
htblusers.username,
Count(htblticket.ticketid) As TicketCount
From htblticket
Inner Join htblusers On htblusers.userid = htblticket.fromuserid
Where htblticket.date > GetDate() - 365
Group By htblusers.name,
htblusers.username,
htblusers.userdomain,
htblticket.fromuserid
Order By Name

Per 3 Month

Select Top 1000000 htblusers.name As Name,
htblusers.username,
Count(htblticket.ticketid) As TicketCount
From htblticket
Inner Join htblusers On htblusers.userid = htblticket.fromuserid
Where htblticket.date > GetDate() - 90
Group By htblusers.name,
htblusers.username,
htblusers.userdomain,
htblticket.fromuserid
Order By Name

Per current month

Select Top 1000000 htblusers.name As Name,
htblusers.username,
Count(htblticket.ticketid) As TicketCount
From htblticket
Inner Join htblusers On htblusers.userid = htblticket.fromuserid
Where DatePart(mm, htblticket.date) = DatePart(mm, GetDate()) And
DatePart(yyyy, htblticket.date) = DatePart(yyyy, GetDate()) And
htblticket.spam <> 'True'
Group By htblusers.name,
htblusers.username,
htblusers.userdomain,
htblticket.fromuserid
Order By Name