Confirm

Expand all | Collapse all

Job Costing Report

  • 1.  Job Costing Report

    Posted 09-18-2020 05:58
      |   view attached
    Hello

    I created a report to show all the costing for all jobs raised using the SQL below, however, when i try and to select and filter dates i get the attached error message.  I want to be able to filter out dates for the costing for example, i want see all the spend for august which i cant at the moment and i trying to use status date to filter them.  Can you please have a look at my sql and let me where i have gone wrong.

    SELECT
    central_site.site_name,
    job.job_location,
    job.job_notes,
    job.job_number,
    SUM(
    job_item.job_item_quantity * contract_boq.contract_rate
    ) Cost,
    job_status_log.login_name,
    MIN(job_status_log.log_effective_date) as Status_date,
    job_status_log.status_code,
    job.contract_code,
    contract.contract_name,
    job_type.job_type_code,
    job_type.job_type_name

    FROM
    job,
    job_item,
    contract_boq,
    central_site,
    job_status_log,
    contract,
    job_type

    WHERE
    job.job_number = job_item.job_number
    AND central_site.site_code = job.site_code
    AND job.job_number = job_status_log.job_number
    AND contract_boq.contract_code = job.contract_code
    AND contract_boq.sor_item_code = job_item.sor_item_code
    AND contract_boq.price_factor_code = job.price_factor_code
    AND contract.contract_code = job.contract_code
    AND job_type.job_type_key = job.job_type_key

    GROUP BY
    job.job_number,
    central_site.site_name,
    job.job_location,
    job_status_log.login_name,
    job_status_log.status_code,
    job.contract_code,
    contract.contract_name,
    job_type.job_type_code,
    job_type.job_type_name,
    job.job_notes

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


  • 2.  RE: Job Costing Report

    Posted 10-16-2020 08:47
    Hi Afzal,

    I would consider using the Order Items rather than the Job Items. This mitigates the need to involve the Contract BOQ table to ascertain the correct rate for the Contract and Price Factor combination.

    I would also link the Job and Job Status Log tables on the Job Log Number column, which will give you the current status log entry for the Job. This will also allow you to select the Status Log's Effective Date without the need to wrap a function around it (MIN, in your case).

    You are receiving the error when you try to qualify the date that is wrapped in the MIN() function, so making the alterations above should avoid this.

    I hope this helps.

    Kind regards,
    Steve

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