Creating Helpdesk ticket report with customfields do not show all the tickets.

[KurtHeyvaert]

i created a report to show the tickets of the helpdesk with customfields.
how ever when i run the report i have a count of 231 tickets, the all ticket report give me 493 tickets so i miss a lot of tickets in my report.

this is the SQL code i use :

Select Top 1000000 htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
htblticket.date As CreationDate,
htblticket.updated As LastUpdated,
htbltickettypes.typename As Type,
htblticketstates.statename As State,
htblpriorities.name As Priority,
htblsource.name As Source,
htblusers.name As [User],
htblusers1.name As AssignedAgent,
htblusers2.name As UserLastNote,
'../helpdesk/icons/' + htbltickettypes.icon As icon,
htblticket.subject As Subject,
htblticketcustomfield.data As [Configuration Category],
htblticketcustomfield2.data As Category,
htblticketcustomfield1.data As Location,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,WorkTime.MinutesWorked) /
60 / 24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,WorkTime.MinutesWorked) /
60 % 24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,WorkTime.MinutesWorked) %
60))) + ' minutes' As TimeWorked
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
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
Inner Join htblsource On htblticket.sourceid = htblsource.sourceid
Inner Join htblusers htblusers2 On htblticket.userid_lastnote =
htblusers2.userid
Inner Join htblticketcustomfield On htblticket.ticketid =
htblticketcustomfield.ticketid
Inner Join htblticketcustomfield htblticketcustomfield1 On
htblticket.ticketid = htblticketcustomfield1.ticketid
Inner Join (Select Top 1000000 htblnotes.ticketid,
Sum(htblnotes.timeworked) As MinutesWorked
From htblnotes
Group By htblnotes.ticketid) WorkTime On WorkTime.ticketid =
htblticket.ticketid
Inner Join htblticketcustomfield htblticketcustomfield2 On
htblticket.ticketid = htblticketcustomfield2.ticketid
Where htblticketcustomfield2.tickettypefieldid = 47 And
htblticketcustomfield.tickettypefieldid = 45 And
htblticketcustomfield1.tickettypefieldid = 50 And htblticket.spam <> 'True'
Order By htblticket.ticketid

[Katgroup]

This usually happens because some tickets are missing a field, such as UserLastNote. Therefore, the Inner Join filters them out. You should be able to fix this by changing the relevant Inner Join to a Left Outer Join.