Jump to content

Search the Community

Showing results for tags 'sql query'.



More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • Hornbill Platform and Applications
    • Announcements
    • Blog Article Discussions
    • General Non-Product Discussions
    • Application Beta Program
    • Collaboration
    • Service Manager
    • Project Manager
    • Supplier Manager
    • Customer Manager
    • Document Manager
    • Configuration Manager
    • Timesheet Manager
    • Live Chat
    • Board Manager
    • Mobile Apps
    • System Administration
    • Integration Connectors, API & Webhooks
    • Performance Analytics
    • Hornbill Switch On & Implementation Questions
  • About the Forum
    • Announcements
    • Suggestions and Feedback
    • Problems and Questions
  • Gamers Club's Games
  • Gamers Club's LFT

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


Organisation


Location


Interests


AIM


MSN


Website URL


ICQ


Yahoo


Jabber


Skype

Found 2 results

  1. Linked to our previous post (link at the bottom of this one), we want to implement a progressive capture process which one of the fields is a dynamic list of values which we want to create using a database direct SQL query on the Hornbill Instance, rather than having to duplicate data in simple lists which is not practical. We are wanting to query a subset of organisations which have a certain industry type, to be used as the source for the dynamic drop down selection. This is in support of our previous post (link at the bottom of this one), where we are trying to implement Internal Services Requests that would be raised by our Project Management Team to manage subscription of services through Hornbill itself automatically, rather than having this to be done manually which is both a overhead and can be prone to errors. I can see that the ability to create dynamic list from a database direct sql query would be useful for other users and organisations as well. It would need to support the returning of two columns, one as the Raw Value, i.e. organisation ID, and a display value, i.e. organisation name. Cheers Martyn
  2. Hi, I am trying to build the following query using the report builder: SQL script: SELECT SR.h_custom_a as responsible_team, h_requesttype, h_pk_reference, h_summary, h_fk_user_name, REQ.h_status, h_fk_servicename, h_catalog, h_fk_priorityname, h_fk_team_name, h_ownername, h_site, h_source_type, h_source_id, h_datelogged, h_dateresolved, h_dateclosed, h_reopencount, h_isanalystunread, h_withinresponse, h_withinfix, FirstTask.h_created_on as priority_confirm_created_on, FirstTask.h_completed_on as priority_confirm_completed_on, MSG.h_msg_date as email_date_received, RESP.h_target_start as response_target_start, RESP.h_target_completed_time as response_completed_time, RESOL.h_target_start as resolution_target_start, RESOL.h_target_completed_time as resolution_completed_time FROM h_itsm_requests REQ inner join h_itsm_services SR on h_fk_serviceid = h_pk_serviceid left join (SELECT replace(h_obj_ref_urn,'urn:sys:entity:com.hornbill.servicemanager:Requests:','') as ref, h_created_on, h_completed_on FROM h_sys_tasks where h_title like '%Confirm Priority%' and not h_completed_on is null) as FirstTask on FirstTask.ref = REQ.h_pk_reference left join (SELECT h_msg_id, h_msg_date FROM h_msg_messages) MSG on REQ.h_source_id = MSG.h_msg_id left join h_itsm_request_slm_targets RESP on REQ.h_pk_reference = RESP.h_request_id and RESP.h_type = 'Response' left join h_itsm_request_slm_targets RESOL on REQ.h_pk_reference = RESOL.h_request_id and RESOL.h_type = 'Resolution' where REQ.h_requesttype in ('incident','service request') Definition of my report: monitor-slas.report.txt Output of my report (based on log files): SELECT h_itsm_requests.h_pk_reference,h_itsm_requests.h_catalog,h_itsm_requests.h_dateclosed,h_itsm_requests.h_datelastmodified,h_itsm_requests.h_datelogged,h_itsm_requests.h_dateplacedonhold,h_itsm_requests.h_dateresolved,h_itsm_requests.h_fk_priorityname,h_itsm_requests.h_fk_servicename,h_itsm_requests.h_fk_team_name,h_itsm_requests.h_fk_user_name,h_itsm_requests.h_isanalystunread,h_itsm_requests.h_iscustomerunread,h_itsm_requests.h_onholduntil,h_itsm_requests.h_ownername,h_itsm_requests.h_reopencount,h_itsm_requests.h_requesttype,h_itsm_requests.h_site,h_itsm_requests.h_status,h_itsm_requests.h_summary,h_itsm_requests.h_withinfix,h_itsm_requests.h_withinresponse,h_sys_tasks.h_created_on,h_sys_tasks.h_completed_on,h_msg_messages.h_msg_date,h_itsm_request_slm_targets.h_target_completed_time,h_itsm_request_slm_targets.h_target_start,h_itsm_services.h_custom_a FROM h_itsm_requests LEFT JOIN h_sys_tasks ON h_sys_tasks.h_obj_ref_urn LIKE h_itsm_requests.h_pk_reference LEFT JOIN h_msg_messages ON h_itsm_requests.h_source_id = h_msg_messages.h_msg_id LEFT JOIN h_itsm_request_slm_targets ON h_itsm_requests.h_pk_reference = h_itsm_request_slm_targets.h_request_id AND h_itsm_request_slm_targets.h_type = 'Response' LEFT JOIN h_itsm_request_slm_targets ON h_itsm_requests.h_pk_reference = h_itsm_request_slm_targets.h_request_id AND h_itsm_services. = 'Resolution' JOIN h_itsm_services ON h_itsm_requests.h_fk_serviceid = h_itsm_services.h_pk_serviceid WHERE ( h_itsm_requests.h_requesttype = 'incident' OR h_itsm_requests.h_requesttype = 'service request' ) AND h_itsm_services.h_custom_a = 'Finance & HR' As you can see, I am really not far off! Only problem: I need to reference the same table twice (h_itsm_request_slm_targets) and this is where the engine fails I think. Indeed, if I just alias the tables (and tweak a little bit the select) it works just fine: SELECT h_itsm_requests.h_pk_reference, h_itsm_requests.h_catalog, h_itsm_requests.h_dateclosed, h_itsm_requests.h_datelastmodified, h_itsm_requests.h_datelogged, h_itsm_requests.h_dateplacedonhold, h_itsm_requests.h_dateresolved, h_itsm_requests.h_fk_priorityname, h_itsm_requests.h_fk_servicename, h_itsm_requests.h_fk_team_name, h_itsm_requests.h_fk_user_name, h_itsm_requests.h_isanalystunread, h_itsm_requests.h_iscustomerunread, h_itsm_requests.h_onholduntil, h_itsm_requests.h_ownername, h_itsm_requests.h_reopencount, h_itsm_requests.h_requesttype, h_itsm_requests.h_site, h_itsm_requests.h_status, h_itsm_requests.h_summary, h_itsm_requests.h_withinfix, h_itsm_requests.h_withinresponse, h_sys_tasks.h_created_on, h_sys_tasks.h_completed_on, h_msg_messages.h_msg_date, A.h_target_completed_time, A.h_target_start, B.h_target_completed_time, B.h_target_start, h_itsm_services.h_custom_a FROM h_itsm_requests LEFT JOIN h_sys_tasks ON h_sys_tasks.h_obj_ref_urn LIKE h_itsm_requests.h_pk_reference LEFT JOIN h_msg_messages ON h_itsm_requests.h_source_id = h_msg_messages.h_msg_id LEFT JOIN h_itsm_request_slm_targets AS A ON h_itsm_requests.h_pk_reference = A.h_request_id AND A.h_type = 'Response' LEFT JOIN h_itsm_request_slm_targets AS B ON h_itsm_requests.h_pk_reference = B.h_request_id AND B.h_type = 'Resolution' JOIN h_itsm_services ON h_itsm_requests.h_fk_serviceid = h_itsm_services.h_pk_serviceid WHERE ( h_itsm_requests.h_requesttype = 'incident' OR h_itsm_requests.h_requesttype = 'service request' ) AND h_itsm_services.h_custom_a = 'Finance & HR' Is there something anybody can suggest? Or should I just give up and build 2 separate reports? One for response and one for resolution?
×
×
  • Create New...