Confirm

Back to discussions
Expand all | Collapse all

Job Value SQL

Jump to Best Answer

  • 1.  Job Value SQL

    Posted 11-13-2020 05:07
    Hello,

    I have got the below SQL to retrieve job costs for a certain cost code and contract, however when I try to add an extra field to the report such as job entry date, I cannot add it due to the grouping.

    Is anyone able to advise how to add extra fields to the below report?


    SELECT
    job.job_number,
    SUM( job_item.job_item_quantity * contract_boq.contract_rate) Estimated_Value


    FROM
    job,
    job_item,
    contract_boq


    WHERE
    job.job_number = job_item.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 job.contract_code = 'TREE19'
    AND job.cost_code IN 'TR: HOUSE-GARDN'

    GROUP BY
    job.job_number

    thank you

    ------------------------------
    Richard White
    Hull City Council
    HULL
    ------------------------------


  • 2.  RE: Job Value SQL
    Best Answer

    Posted 11-13-2020 07:09
    Hi Richard,

    You'd need only add the column to the GROUP BY list.

    For example:

    SELECT
       job.job_number,
       job.job_entry_date,
       SUM( job_item.job_item_quantity * contract_boq.contract_rate) Estimated_Value
    ...
    ...
    GROUP BY
       job.job_number,
       job.job_entry_date


    Hope this helps.

    Kind regards,
    Steve

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

    Original Message


  • 3.  RE: Job Value SQL

    Posted 11-13-2020 07:15
    Hi Steve,

    Fantastic thank you very much

    ------------------------------
    Richard White
    Hull City County Council
    HULL
    ------------------------------

    Original Message