My T-SQL query needs additional SUM's from an additional table...

Question

The following is my current query. It works the way i want it to. However, it needs some improvements so that i can 1) retrieve the SUM's of the money columns in my 'expenses' table and 2) add a WHERE clause (or HAVING?) so that all of the SUM's are year-to-date values, e.g. any amounts prior to Jan. 1, 2010 will not be totaled by the SUM functions.

SELECT 
patron_name, 
federal_number, 
hst_number, 
average_bf_test, 
statement_number, 
period_ending, 
SUM(quota_payment) AS ytd_quota_payment, 
SUM(total_deductions) AS ytd_total_deductions, 
SUM(net_cheque_or_direct_deposit) AS ytd_net_cheque_or_direct_deposit, 
SUM(interim_payment) AS ytd_interim_payment, 
SUM(final_payment) AS ytd_final_payment 
FROM (SELECT  patron_name, 
producer.federal_number, 
hst_number, 
average_bf_test,
(
SELECT  MAX(s.statement_number)
FROM    statement s
) AS statement_number,
(
SELECT MAX(s.period_ending) 
FROM   statement s
) AS period_ending,
sums.*
FROM    producer
JOIN    producer_details
ON      producer_details.federal_number = producer.federal_number
JOIN    statement so
ON      so.patron_number = producer.patron_number
CROSS APPLY
(
SELECT  SUM(quota_payment) AS quota_payment,
        SUM(total_deductions) AS total_deductions,
        SUM(net_cheque_or_direct_deposit) AS net_cheque_or_direct_deposit,
        SUM(interim_payment) AS interim_payment,
        SUM(final_payment) AS final_payment
FROM    payment p
WHERE   p.statement_number = so.statement_number
) sums
WHERE   producer.patron_number = @PatronNo) a 

GROUP BY 
patron_name, 
federal_number, 
hst_number, 
average_bf_test, 
statement_number, 
period_ending

The following is my 'expenses' table:

cgm_validation (money)
milk_promotion_and_adv (money)
trucking (money)
loan_capital (money)
dfns_producer_dues (money)
sundry_deduction (money)
credited_to_account (money)
hst (money)
statement_number (int)

Here is the data in my 'expenses' table:

cgm_validation  milk_promotion_and_adv  trucking  loan_capital  dfns_producer_dues 
0.0             387.41                  950.25    487.34        134.92             
0.0             525.67                  812.47    501.12        162.93                                             
sundry_deduction  credited_to_account   hst       statement_number
4987.56           0.0                   182.34    386
5620.34           0.0                   234.28    387

Answer 1

If I understood correctly, the following should work:

SELECT 
a.patron_name, 
a.federal_number, 
a.hst_number, 
a.average_bf_test, 
a.statement_number, 
a.period_ending, 
SUM(a.quota_payment) AS ytd_quota_payment, 
SUM(a.total_deductions) AS ytd_total_deductions, 
SUM(a.net_cheque_or_direct_deposit) AS ytd_net_cheque_or_direct_deposit, 
SUM(a.interim_payment) AS ytd_interim_payment, 
SUM(a.final_payment) AS ytd_final_payment 
FROM (
        SELECT  patron_name, 
        producer.federal_number, 
        hst_number, 
        average_bf_test,
        (
            SELECT  MAX(s.statement_number)
            FROM    statement s
        ) AS statement_number,
        (
            SELECT MAX(s.period_ending) 
            FROM   statement s
        ) AS period_ending,
        sums.*
        FROM    producer
        JOIN    producer_details ON producer_details.federal_number = producer.federal_number
        JOIN    statement so ON (so.patron_number = producer.patron_number and so.period_ending > '01/01/2010')
        CROSS APPLY
        (
            SELECT  SUM(quota_payment) AS quota_payment,
                    SUM(total_deductions) AS total_deductions,
                    SUM(net_cheque_or_direct_deposit) AS net_cheque_or_direct_deposit,
                    SUM(interim_payment) AS interim_payment,
                    SUM(final_payment) AS final_payment
            FROM    payment p
            WHERE   p.statement_number = so.statement_number
        ) sums
        WHERE   producer.patron_number = @PatronNo
) a join expenses e on (a.statement_number = e.statement_number)
GROUP BY 
a.patron_name, 
a.federal_number, 
a.hst_number, 
a.average_bf_test, 
a.statement_number, 
a.period_ending

However, I think there's more you can do to make this query run a little better - for example, the multiple sub-selects on statement seem likely to be an easy target for optimization / refactoring.