/build/static/layout/Breadcrumb_cap_w.png

Miscellaneous Question


Kace sql code for a report of tickets by a custom field by month for a given year.

12/21/2016 594 views
I currently use the following code to pull the information I need but then I use a pivot table to get the results I want.
code used is:
SELECT O.FULL_NAME AS OWNER_NAME, HD_TICKET.CREATED, 
HD_TICKET.ID, HD_TICKET.TITLE, 
HD_STATUS.NAME AS STATUS_NAME,
 S.FULL_NAME AS SUBMITTER_NAME, 
HD_TICKET.CUSTOM_FIELD_VALUE4 
 FROM HD_TICKET  LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) 
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) 
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) 
WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND ((HD_TICKET.CREATED > 
'2015-12-31 23:59:59'))  
ORDER BY OWNER_NAME, CREATED

Resulting pivot table looks like the following where the Dept is HD_TICKET.CUSTOM_FIELD_VALUE4:

Count of Ticket ID Column Labels
Row Labels 1 2 3 4 5 6 7 8 9 10 11 12 Grand Total
Actuarial 5 8 7 9 10 8 6 7 10 1 4 7 82
BFS 1 1 1 1 1 1 6
Claims 62 71 76 95 92 83 70 99 63 51 73 36 871
Corporate 1 1
Finance 21 36 23 35 27 31 19 8 20 11 15 16 262
HR 1 1 2 1 2 7
Human Resources 57 19 43 27 34 33 52 41 30 39 27 29 431
Info Technology 102 96 90 93 121 108 98 72 83 65 66 49 1043
Regulatory Affairs 23 26 56 23 40 41 41 24 24 23 17 14 352
Risk Management 10 5 8 2 6 5 3 13 14 1 67
Underwriting 98 121 95 87 99 102 92 82 60 60 77 36 1009
Grand Total 378 382 399 372 424 414 380 338 294 265 296 189 4131

Can this report be generated all within the sql code so that I don't have to manually create the pivot table?

Thanks,
Cathy Nadeau
0 Comments   [ + ] Show comments

Comments


Be the first to answer this question

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ