Helpdesk Report SQL Statement

[JBS_Admin]

I've tried various SQL statements but cant get it to format correctly.

Basically, I need to see all my closed and open cases for the last 7 days with the subject, date, user name, and notes (all of them not just the initial notes). Can somebody help me with this?

Thanks!

[Tom_P · Accepted solution]

Hi,

We received and answered this question via email. For everyone else's benefit, I'm pasting the reply we sent via email below.

Select
    htblticket.ticketid,
    htblticket.subject,
    htblticket.date as creationDate,
    users.name as username,
    agents.name as currentAgent,
    htblticket.agentid,
    htblnotes.note
from htblticket
    inner join htblnotes on htblnotes.ticketid = htblticket.ticketid
    left outer join htblusers as users on users.userid = htblticket.fromuserid
    left outer join htblusers as agents on agents.userid = htblticket.agentid
Where htblticket.date >= (GetDate() - 7)
Order by htblticket.date, htblticket.ticketid, htblnotes.date