Report on the number of tickets per source for a given month. Keeping track of the number of tickets that are in the helpdesk is an important metric when it comes to forecasting and for estimating the amount of work. The report gives an overview of the number of tickets per source and month so you know where the tickets are coming from.
Count of Tickets per Source and Month Query
Select Top 1000000 DatePart(yyyy, htblticket.date) As Year,
DatePart(mm, htblticket.date) As Month,
htblsource.icon As icon,
htblsource.name As Source,
Count(htblticket.ticketid) As TicketCount,
Cast((Count(htblticket.ticketid) / Cast(TicketCount.Amount As decimal) *
100) As decimal(10,2)) As [Source%]
From htblticket
Inner Join htblsource On htblsource.sourceid = htblticket.sourceid
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)
Where htblticket.spam <> 'True'
Group By DatePart(yyyy, htblticket.date),
DatePart(mm, htblticket.date),
htblsource.icon,
htblsource.name,
TicketCount.Amount
Order By Year Desc,
Month Desc,
Source
