Confirm

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

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

Original Message:
Sent: 12-04-2020 12:27
From: Rebecca Clark
Subject: Enquiry Commitments by Subject Responsible Officer

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

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

Original Message:
Sent: 12-09-2020 10:12
From: Steve Bish
Subject: Enquiry Commitments by Subject Responsible Officer

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

Original Message:
Sent: 12-04-2020 12:27
From: Rebecca Clark
Subject: Enquiry Commitments by Subject Responsible Officer

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

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

Original Message:
Sent: 12-10-2020 06:15
From: Rebecca Clark
Subject: Enquiry Commitments by Subject Responsible Officer

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

Original Message:
Sent: 12-09-2020 10:12
From: Steve Bish
Subject: Enquiry Commitments by Subject Responsible Officer

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

Original Message:
Sent: 12-04-2020 12:27
From: Rebecca Clark
Subject: Enquiry Commitments by Subject Responsible Officer

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

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

Original Message:
Sent: 12-10-2020 08:27
From: Steve Bish
Subject: Enquiry Commitments by Subject Responsible Officer

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

Original Message:
Sent: 12-10-2020 06:15
From: Rebecca Clark
Subject: Enquiry Commitments by Subject Responsible Officer

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

Original Message:
Sent: 12-09-2020 10:12
From: Steve Bish
Subject: Enquiry Commitments by Subject Responsible Officer

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

Original Message:
Sent: 12-04-2020 12:27
From: Rebecca Clark
Subject: Enquiry Commitments by Subject Responsible Officer

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