Confirm

Expand all | Collapse all

Enquiry Commitments by Subject Responsible Officer

  • 1.  Enquiry Commitments by Subject Responsible Officer

    Posted 12-04-2020 12:28
    Wondering if anyone can help with some SQL. I have been asked to make a widget that shows overdue escalated complaints. The escalation officer is the subjects "responsible officer" and the commitment is how we display if something is overdue or not. Every time I try and left join the commitment table in the SQL the result is that every responsible officer in the system is pulled out for one enquiry.

    As you can imagine this crashes my computer and brings out hundreds of results. Has any one got the commitment, enquiry subject and central enquiry table in the same place?

    (I have tried variations of inspecting_officer, Action_officer_2 and Resp_Officer - all have the same result)

    Thank you.

    ------------------------------
    Rebecca Clark
    Hull City Council
    HULL
    ------------------------------


  • 2.  RE: Enquiry Commitments by Subject Responsible Officer

    Posted 12-09-2020 10:13
    Hi Rebecca,

    From memory, you'd need to be linking the following tables:

    • central_enquiry
    • enquiry_commitment
    • enquiry_subject

    Something like:

    SELECT
       central_enquiry.enquiry_number,
       enquiry_commitment.commitment_code,
       enquiry_commitment.commit_target_time,
       enquiry_commitment.commit_actual_time,
       enquiry_subject.subject_code,
       enquiry_subject.subject_name,
       enquiry_subject.resp_officer_code
    FROM central_enquiry
       LEFT JOIN enquiry_commitment

          ON
          central_enquiry.enquiry_number = enquiry_commitment.enquiry_number
       JOIN enquiry_subject
          ON
          central_enquiry.service_code = enquiry_subject.service_code AND
          central_enquiry.subject_code = enquiry_subject.subject_code;

    Note that all Enquiries have a Service and Subject, but not all will have a Commitment (hence the LEFT JOIN).

    I hope this helps further your report.

    Kind regards,
    Steve

    ------------------------------
    Steve Bish
    Senior Software Support Analyst
    Confirm
    ------------------------------



  • 3.  RE: Enquiry Commitments by Subject Responsible Officer

    Posted 12-10-2020 06:16
    Hi Steve,

    Thanks for this, but I still can't get what I want. I am sure this is a user error, but I have tried your suggested SQL before, the JOINS definitely work because I have them working in another report. But when I add the enquiry responsible officer code and filter the data source by the commitment (using the blue and green dots), the result is 100s of rows for one enquiry. I think its returning all of the responsible officers in the system rather than for that enquiry.

    I would also like the responsible officer's name, I am planning on making this into a widget. 


    ------------------------------
    Rebecca Clark
    Hull City Council
    HULL
    ------------------------------



  • 4.  RE: Enquiry Commitments by Subject Responsible Officer

    Posted 12-10-2020 08:27
    Hi Rebecca,

    Have you joined the enquiry_subject table to the action_officer table?

    Something like:

    enquiry_subject.resp_officer_code = action_officer.officer_code

    Kind regards,
    Steve


    ------------------------------
    Steve Bish
    Senior Software Support Analyst
    Confirm
    ------------------------------



  • 5.  RE: Enquiry Commitments by Subject Responsible Officer

    Posted 12-10-2020 11:57
    Hi Steve,

    Finally got it to work, thank you for your help. I ended up doing this: 0

    enquiry_subject.resp_officer_code = action_officer_2.officer_code

    Who knew?! Thanks again.

    ------------------------------
    Rebecca Clark
    Hull City Council
    HULL
    ------------------------------