9 Replies Latest reply on Jul 14, 2016 3:18 AM by JaimeK

    How to bring back latest priority (SSRS incidents query)

    Apprentice

      Hi - I am creating a query in SSRS for reporting. We are bringing back every incident and the figures are spot on. The problem comes when we add the 'Priority' column i.e. lc_response_level.lc_title

       

      This can return more than one row if the priority level has been changed through the lifecycle. Anyone know how I can just bring back the latest priority? SQL below. Many thanks.

       

       

      SELECT        im_incident.im_id AS [Incident Number], tps_user.tps_title, pm_process.pm_creation_date, lc_response_level.lc_title

      FROM            tps_group FULL OUTER JOIN

                               im_incident_resolution RIGHT OUTER JOIN

                               im_incident ON im_incident_resolution.im_incident_guid = im_incident.pm_guid LEFT OUTER JOIN

                               lc_escalation_action LEFT OUTER JOIN

                               lc_response_level RIGHT OUTER JOIN

                               lc_status_escalation ON lc_response_level.lc_guid = lc_status_escalation.lc_response_level_guid ON

                               lc_escalation_action.lc_status_escalation_guid = lc_status_escalation.lc_guid LEFT OUTER JOIN

                               tps_escalation_status RIGHT OUTER JOIN

                               im_incident_esc_point ON tps_escalation_status.tps_guid = im_incident_esc_point.im_status_guid ON

                               lc_escalation_action.lc_guid = im_incident_esc_point.im_escalation_action_guid ON

                               im_incident.pm_guid = im_incident_esc_point.im_incident_guid LEFT OUTER JOIN

                               tps_assignment_status RIGHT OUTER JOIN

                               pm_process_assignment ON tps_assignment_status.tps_guid = pm_process_assignment.pm_status_guid LEFT OUTER JOIN

                               tps_assignment_status AS tps_assignment_status_1 ON pm_process_assignment.pm_status_guid = tps_assignment_status_1.tps_guid LEFT OUTER JOIN

                               tps_user ON pm_process_assignment.pm_user_guid = tps_user.tps_guid ON im_incident.im_latest_assignment_guid = pm_process_assignment.pm_guid ON

                               tps_group.tps_guid = pm_process_assignment.pm_group_guid FULL OUTER JOIN

                               pm_process FULL OUTER JOIN

                               lc_status ON pm_process.pm_status_guid = lc_status.lc_guid ON im_incident.pm_guid = pm_process.pm_guid FULL OUTER JOIN

                               im_incident_assignment ON pm_process_assignment.pm_guid = im_incident_assignment.pm_guid AND

                               im_incident.pm_guid = im_incident_assignment.im_incident_guid

      GROUP BY im_incident.im_id, tps_user.tps_title, pm_process.pm_creation_date, lc_response_level.lc_title

      HAVING        (NOT (tps_user.tps_title IS NULL))

      ORDER BY [Incident Number]