Helpdesk report for tickets for all on one reason

[Tholmes]

I have set the HD up this way and need to run the report from this site.
https://www.lansweeper.com/forum/yaf_postst16360_Helpdesk-report---subcategory.aspx#post55047
But I want to add each ticket reason beneath, not just one, so I thought this would work but it did not, how can I add the next category.
Reason for ticket>
Hardware Related
Software Related
ETC.
I can get it if just the hardware related is there, but when I try to add software related, it shows no results.
Any advice please.

Select Distinct Top 1000000 htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
htblticket.date,
htbltickettypes.typename,
htblticketstates.statename,
htblticket.subject,
htblusers1.name As [User Name],
htblusers.name As [Help Desk Agent Name],
Reasonforticket.data As [Reason for ticket],
HardwareRelated.data As [Hardware Related],
SoftwareRelated.data As [Software Related]
From htblticket
Left Join htblagents On htblagents.agentid = htblticket.agentid
Left Join htblusers On htblusers.userid = htblagents.userid
Inner Join htblusers htblusers1 On htblusers1.userid = htblticket.fromuserid
Left Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Inner Join htbltickettypes On htbltickettypes.tickettypeid =
htblticket.tickettypeid
Inner Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like 'Reason For Ticket') As Reasonforticket
On htblticket.ticketid = Reasonforticket.ticketid
Inner Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like 'Hardware Related') As HardwareRelated On
htblticket.ticketid = HardwareRelated.ticketid
Inner Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like 'Software Related') As SoftwareRelated On
htblticket.ticketid = SoftwareRelated.ticketid

[Tholmes · Accepted solution]

Ok, so I have mashed this together and made this report, I am able to compare the values from this month to last and so on. If anyone has any better suggestions, please let me know.


Select Top 1000000 DatePart(yyyy, htblticket.date) As Year,
DatePart(mm, htblticket.date) As Month,
RFT.data As [Reason For Ticket, IT Support],
Count(RFT.data) As Count
From htblticket
Inner Join htblpriorities On htblpriorities.priority = htblticket.priority
Inner Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Inner Join htblusers On htblusers.userid = htblticket.fromuserid
Inner Join htblsource On htblticket.sourceid = htblsource.sourceid
Left Join htblagents On htblagents.agentid = htblticket.agentid
Left Join htblusers htblusers1 On htblusers1.userid = htblagents.userid
Inner Join htbltickettypes On htblticket.tickettypeid =
htbltickettypes.tickettypeid
Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like '%Reason for ticket%') As RFT On
RFT.ticketid = htblticket.ticketid
Where htbltickettypes.typename = 'IT Support' And htblticket.spam <> 'True'
Group By DatePart(yyyy, htblticket.date),
DatePart(mm, htblticket.date),
RFT.data
Order By Year Desc,
Month Desc

[Tholmes]

I had a reply from Lansweeper.
Select Top 1000000
RFT.data As [External Reference ID],
count(RFT.data) as Count
From htblticket
Inner Join htblpriorities On htblpriorities.priority = htblticket.priority
Inner Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Inner Join htblusers On htblusers.userid = htblticket.fromuserid
Inner Join htblsource On htblticket.sourceid = htblsource.sourceid
Left Join htblagents On htblagents.agentid = htblticket.agentid
Left Join htblusers htblusers1 On htblusers1.userid = htblagents.userid
Inner Join htbltickettypes On htblticket.tickettypeid =
htbltickettypes.tickettypeid
Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
htblticketcustomfield.data
From htblticketcustomfield
Inner Join htblcustomfields On htblcustomfields.fieldid =
htblticketcustomfield.fieldid
Where htblcustomfields.name Like '%Reason for ticket%') As RFT
On RFT.ticketid = htblticket.ticketid
Where htblticket.spam <> 'True' And htbltickettypes.typename =
'IT Support' and htblticket.date > getdate()-30
group by RFT.data

This does it for the last 30 days, can anyone show me how to do it for this year please, split into months, as I want to be able to compare them.