The CIO wanted to know what tickets were created today so this query works fine:
SELECT T.ID AS 'Ticket #', T.TITLE AS 'Issue', S.NAME AS 'Status' FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
JOIN HD_QUEUE Q ON T.HD_QUEUE_ID=Q.ID and Q.NAME ='ASD20 Help Desk'
WHERE DATEDIFF(NOW(), CREATED) < 1
ORDER BY T.ID
This report can be run at any time and will show all the tickets created on the day the report is run.

I'd like to add the Ticket Owner to the report and can see from SQL Workbench the field in the HD_Ticket table is called OWNER_ID which is a number. The number relates to the USER table's ID field and the USER table includes the field FULL_NAME. I'd like to add the column so believe I need to add "U.FULL_NAME as 'Ticket Owner'" to the list of FROM HD_TICKET T". Then I also need to add a JOIN line but this isn't working:
JOIN USER U ON T.OWNER_ID = U.ID
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
What you've suggested should work. The following worked for me. However, it appears you need to specify the table for CREATED in your WHERE clause - it is ambiguous if more than one table has the same field (USER and HD_TICKET both have a CREATED field).

SELECT T.ID AS 'Ticket #', T.TITLE AS 'Issue', U.FULL_NAME AS 'Owner', S.NAME AS 'Status' FROM HD_TICKET T
JOIN USER U ON (T.OWNER_ID = U.ID)
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
JOIN HD_QUEUE Q ON T.HD_QUEUE_ID=Q.ID and Q.NAME ='Queue Name'
WHERE DATEDIFF(NOW(), T.CREATED) < 1
ORDER BY T.ID
Answered 04/02/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
That works great, thanks!
Answered 04/02/2010 by: RichB
Fourth Degree Green Belt

Please log in to comment
0
Is it possible to have a custom view (in the Helpdesk) that lists all tickets created today?

Is there a TODAY or Last Month sort of wildcard?
Answered 11/11/2010 by: stubox
Blue Belt

Please log in to comment
2
Stubox,

When you create a custom view a record is created in HD_TICKET_FILTER.

What i did is create a custom view called "Tickets Created Today".

As a criteria i used create date contains and i entered "2010-11-11" (without the quotes).

I purposely entered yesterdays date. Trust me there's a method to my madness.

When i did that, this was created in the HD_TICKET_FILTER.FIND_FIELDS
a:7:{s:7:"WFIELD5";s:17:"HD_TICKET.CREATED";s:11:"EXP_SELECT5";s:8:"CONTAINS";s:6:"INPUT5";s:10:"2010-11-11";s:13:"UNION_SELECT6";s:1:"0";s:13:"UNION_SELECT7";s:1:"0";s:13:"UNION_SELECT8";s:1:"0";s:11:"FILTER_NAME";s:21:"Tickets Created Today";}

Next I created a help desk run that would run every morning at 12:05 AM.

Here is my select statement:
SELECT 1 FROM DUAL

Here is my update:
UPDATE HD_TICKET_FILTER
SET FIND_FIELDS = REPLACE(FIND_FIELDS,DATE(DATE_SUB(SYSDATE(),INTERVAL 1 DAY)),DATE(SYSDATE()))
WHERE NAME = 'TICKETS CREATED TODAY'


What the update statement does is parse through the FIND_FIELDS column looking for yesterday's date.

When it finds it, it replaces yesterdays date with todays.

After setting up the rule I did a run once.

This updated yesterdays date (which i entered when i created the view) with todays.

Going forward since it runs everyday at 12:05 am i should have a rolling dynamic view.

WARNING:
Since this runs everyday I am unable to test if this is fully working. I wont be sure for another couple of days.

DO NOT IMPLEMENT THIS INTO PRODUCTION WITHOUT TESTING FOR A FEW DAYS!!!!

Also remeber that views are USER specific. Each user that will want this view will need to setup his/her own "Tickets Created Today" View using yesterday's date.

When this is done, the rule will need to be run once to catch all the new created views.
Answered 11/12/2010 by: dchristian
Red Belt

Please log in to comment
0
Hey just wanted to give everyone an update.

This has been working in the test environment for the past week.
Answered 11/19/2010 by: dchristian
Red Belt

Please log in to comment
0
Hi dchristian

I thought I had replied to your suggestion but clearly didn't! Sorry for that.

Many thanks for input it should be very useful.

Cheers,

StuBox
Answered 01/05/2011 by: stubox
Blue Belt

Please log in to comment
0
Not sure if this would be asking too much but is it possible to create a custom view that displays tickets for the current week? Current week being Monday to Sunday for that week.

Or maybe it would be easier to call it a custom view for the last 7 days (last 7 days including today). So similar to your rule above, the date would be today's date minus 7 days.
Answered 02/08/2011 by: stubox
Blue Belt

Please log in to comment
Answer this question or Comment on this question for clarity