Sql-server – Return rows one at a time from variable table

sql serversql-server-2008sql-server-2008-r2

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.

Best Answer

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;
Related Question