0

I have a database which contains job numbers, each job has a list of invoices and a list of journals. Its an IRQ request accounting system.
In order to see which jobs have a zero balance I'm subtracting a sum of values from another sum of values. I'd like to see the result per job number.

I currently have a query:

 DECLARE @job_nos AS TABLE ( job_no INT )

    INSERT  INTO @job_nos ( job_no )
            SELECT  jm_job.job_no
            FROM    [FCU_LIVE].[dbo].[jm_job]
                    INNER JOIN [FCU_LIVE].[dbo].[xe_log] ON ( CAST(jm_job.job_no AS VARCHAR(8000)) = xe_log.keystring )
                    INNER JOIN [FCU_LIVE].[dbo].[jm_job_status] ON xe_log.to_value = jm_job_status.status_code
                    INNER JOIN [FCU_LIVE].[dbo].[jm_division] ON jm_job.division_no = jm_division.division_no
                    INNER JOIN [FCU_LIVE].[dbo].[sys_phone_list] ON jm_job.cust_id = sys_phone_list.list_id
            WHERE   field_id = 'active'
                    AND jm_job.division_no = 4
                    AND jm_job.billing_type = 'I'

    SELECT  ( SELECT    SUM(jm_installment.billing_amount)
              FROM      [FCU_LIVE].[dbo].[jm_installment]
              WHERE     jm_installment.job_no IN ( SELECT   job_no
                                                   FROM     @job_nos )
            )
            - ( SELECT  SUM(jm_wo_transaction.billing_amount)
                FROM    [FCU_LIVE].[dbo].[jm_work_order]
                        INNER JOIN [FCU_LIVE].[dbo].[JM_WO_TRANSACTION] ON jm_work_order.wo_no = jm_wo_transaction.wo_no
                                                                           AND jm_work_order.wo_seq = jm_wo_transaction.wo_seq
                WHERE   jm_work_order.job_no IN ( SELECT    job_no
                                                  FROM      @job_nos )
                        AND jm_wo_transaction.override = 'N'
              ) AS WIP

Which gives me a single amount. The TOTAL sum of all job numbers returned by the multiple variable.
How can this be changed to output, the sum result for each job number on a separate row?

I'm blindly using this approach after googling possible options. If you have a better approach overall please let me know.

1

This query should do what you want and avoid any issues with 1 to many relationships messing up the SUMs

SELECT i.job_no, installment_billing_amount - transaction_billing_amount
FROM (
        SELECT job_no, SUM(jm_installment.billing_amount) installment_billing_amount 
        FROM [FCU_LIVE].[dbo].[jm_installment] 
        GROUP BY job_no
    ) i
    INNER JOIN (
        SELECT jm_work_order.job_no, SUM(jm_wo_transaction.billing_amount) transaction_billing_amount
        FROM [FCU_LIVE].[dbo].[jm_work_order]
            INNER JOIN [FCU_LIVE].[dbo].[JM_WO_TRANSACTION] ON jm_work_order.wo_no = jm_wo_transaction.wo_no
                AND jm_work_order.wo_seq = jm_wo_transaction.wo_seq
                AND jm_wo_transaction.override = 'N'
        GROUP BY jm_work_order.job_no
    ) t ON i.job_no = t.job_no
WHERE   i.job_no IN ( SELECT    job_no
                      FROM      @job_nos )

It is easier to follow as a CTE query.

WITH 
    installmentSum AS 
    (  
    SELECT job_no, SUM(jm_installment.billing_amount) installment_billing_amount 
    FROM [FCU_LIVE].[dbo].[jm_installment] 
    GROUP BY job_no
    )
    ,
    transactionSum AS 
    (
    SELECT jm_work_order.job_no, SUM(jm_wo_transaction.billing_amount) transaction_billing_amount
    FROM [FCU_LIVE].[dbo].[jm_work_order]
        INNER JOIN [FCU_LIVE].[dbo].[JM_WO_TRANSACTION] ON jm_work_order.wo_no = jm_wo_transaction.wo_no
            AND jm_work_order.wo_seq = jm_wo_transaction.wo_seq
    WHERE jm_wo_transaction.override = 'N'
    GROUP BY jm_work_order.job_no
    )
SELECT j.job_no, installment_billing_amount - transaction_billing_amount
FROM @job_nos j
    INNER JOIN installmentSum i ON j.job_no = i.job_no
    INNER JOIN transactionSum t ON i.job_no = t.job_no;
  • Thanks so much for this. The first solution works for me. – ColinA Nov 5 '14 at 9:57
  • Can I ask though, the 'i'.job_no is an alias? Is it this that is allowing you to add the job numbers to the output? – ColinA Nov 5 '14 at 9:59
  • I have a couple of other fields which I need to include in the output which are made available through the first bunch of inner joins. Thanks again, I've been on this for 2 days – ColinA Nov 5 '14 at 10:01
  • @ColinA Basically this query is summing up the installment billings per job (alias i), summing up the transaction billings per job (alias t), joining the two subqueries on the job number and restricting it to the job_number rows in the table variable. This can be done on either the i or t aliased job_number. Adding additional columns shouldn't be too difficult. – MickyT Nov 5 '14 at 17:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.