Confirm

Expand all | Collapse all

Job Report

  • 1.  Job Report

    Posted 11-12-2019 16:57
    Hello

    I have written this sql for the current jobs allocated to a team but seem to getting duplicates.

    Can you some please assist me with this, i want this to display the current jobs allocated to a Team based on the estimated date.

    Sql.

    SELECT
    central_site.site_name,
    feature.feature_location,
    job.job_location,
    gang_job.order_number,
    gang_job.order_job_number,
    gang.gang_code,
    gang.gang_name,
    job.job_start_date,
    job.actual_start_date,
    job.actual_comp_date

    FROM
    gang,
    gang_job,
    job,
    feature,
    central_site
    WHERE
    gang.gang_code = 'MAR1'
    AND gang_job.order_job_number = '40000010'
    AND gang.gang_code = gang_job.gang_code
    AND gang_job.contract_code = job.contract_code
    AND job.site_code = central_site.site_code
    AND central_site.site_code = feature.site_code

    ------------------------------
    Afzal Hussain
    Specialist Application Support Officer
    London Borough of Barking and Dagenham
    ------------------------------


  • 2.  RE: Job Report

    Employee
    Posted 11-13-2019 06:23
    Hi Afzal

    Jobs are on Features so if you add,
       ...AND job.plot_number = feature.plot_number...
    to the WHERE clause does that return the data you want?

    ------------------------------
    Chris Wareham
    Senior Technical Support Analyst
    Pitney Bowes
    ------------------------------



  • 3.  RE: Job Report

    Posted 11-13-2019 06:57
      |   view attached
    Hello Chris,

    I have tried this method and has reduced has some but not all of this.  I have attached a spreadsheet and what i am thinking of replicating and result of the result.

    ------------------------------
    Afzal Hussain
    Specialist Application Support Officer
    London Borough of Barking and Dagenham
    ------------------------------

    Attachment(s)



  • 4.  RE: Job Report

    Employee
    Posted 11-14-2019 04:17
    Hi Afzal

    Try this.

    SELECT
    gang_job.contract_code,
    gang_job.order_number,
    gang_job.order_job_number,
    gang.gang_code,
    gang.gang_name,
    job.job_number,
    job.site_code,
    central_site.site_name,
    job.plot_number,
    feature.feature_location,
    job.job_location,
    job.order_number,
    job.job_complete_date,
    job.actual_comp_date,
    job.actual_start_date
    FROM
    gang,
    gang_job,
    central_site,
    feature,
    job
    WHERE
    gang.gang_code = 'MAR1'
    AND gang_job.order_job_number = '40000010'
    AND gang.gang_code = gang_job.gang_code
    AND gang_job.order_job_number = job.job_number
    AND central_site.site_code = job.site_code
    AND feature.site_code = job.site_code
    AND feature.plot_number = job.plot_number;

    ------------------------------
    Chris Wareham
    Senior Technical Support Analyst
    Pitney Bowes
    ------------------------------