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, CREATEDResulting 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

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

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:

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

Be the first to answer this question

Answer this question

Miscellaneous Questions

question