Ticket Custom Field Report - Time Entry

[rcamron]

Good Morning,

I have a custom report that I have billed that captures our time tracking on tickets. We recently added a custom field to signal if its a billable item back to the department. When I try to modify the report to include the custom field in the report (only want to see the results from that custom field) it either shows all custom fields data or it show only if there is a result of the custom field. Is there a way to have it only show the results of that specific field even if no data is in the field?

Here is the current report:

Select Top 1000000 '#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
htblticket.ticketid,
htbltickettypes.typename As Type,
htblticket.subject As Subject,
htblusers.name As [User],
htblusers.department As Department,
htblnotes.timeworkeddate As [Date Entered],
htblnotes.timeworked As [Time Worked (Minutes)],
'../helpdesk/icons/' + htbltickettypes.icon As Icon,
htblusers1.name As Agent
From htblticket
Inner Join htblnotes On htblticket.ticketid = htblnotes.ticketid
Inner Join htblusers On htblusers.userid = htblticket.fromuserid
Inner Join htbltickettypes On htblticket.tickettypeid =
htbltickettypes.tickettypeid
Inner Join htblusers htblusers1 On htblnotes.timeworkeduserid =
htblusers1.userid
Inner Join htblticketcustomfield On htblticket.ticketid =
htblticketcustomfield.ticketid
Where htblnotes.timeworkeddate > '01/01/2023 00:00:00' And
htblnotes.timeworked > 1
Group By '#' + Cast(htblticket.ticketid As nvarchar),
htblticket.ticketid,
htbltickettypes.typename,
htblticket.subject,
htblusers.name,
htblusers.department,
htblnotes.timeworkeddate,
htblnotes.timeworked,
'../helpdesk/icons/' + htbltickettypes.icon,
htblusers1.name
Order By htblticket.ticketid

[rom]

I'm not in front of my computer, but what you want to do is change INNER JOIN to LEFT JOIN.  an INNER JOIN is an 'exact match' so if there is not an entry for the custom field for TicketA, it won't show.  a LEFT JOIN starts with the primary table A, and gets Matches and No-Matches from table B.  

So, in this case, not all tickets will have the billable field filled in/entered.  You'll want to LEFT JOIN that.  Keep in mind that any other INNER JOINs to any table that may have no record for a ticket, will also filter/impact the results.

Aaannnd.... so that means if you LEFT JOIN the custom field table thingie...  then you will get NULLS (i.e. tickets with no record)... you could then make "No-Match" queries (i.e. tickets with no custom field entered) by LEFT JOIN table B  and then put in the WHERE clause  "WHERE TABLEB.TicketID IS NULL"

Sorry if this is confusing, i'm on my smartphone... but I like to try to show fundamentals versus fixing a query