I'm wondering if somebody can help me improve my T-SQL query so that i can retrieve the SUMs of the money columns from an additional table... :)

First of all, thank you very much in advance for any attempt at helping me with this. My current query (below) works the way i want it to save the requirement mentioned in the title: I need to retrieve the SUM's of the money columns in my 'expenses' table AND each of these values must correspond to a period_ending value that is greater than @FiscalYearEnd, as is achieved for the other SUM va开发者_StackOverflow社区lues in this line:

JOIN statement so ON so.patron_number = producer.patron_number AND so.period_ending > @FiscalYearEnd

Here is the entire query:

SELECT
patron_name, 
federal_number, 
hst_number, 
average_bf_test, 
statement_number, 
period_ending, 
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 (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 > @FiscalYearEnd
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

Here is sample data from 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

Any, more info needed, just let me know. Once again, thank you very much for any help you can provide.

Without any way to test. I think the following query will get you going along the right direction while also correcting possible issues (such as performance, duplication of effort, unnecessary grouping or joins). Take a look at the following and let me know how it works out.

SELECT  patron_name
    ,federal_number
    ,hst_number
    ,average_bf_test
    ,statement_number
    ,period_ending
    ,payments.quota_payments_sum AS quota_payment
    ,payments.total_deductions_sum AS total_deductions
    ,payments.net_cheque_or_direct_deposit_sum AS net_cheque_or_direct_deposit
    ,payments.interim_payment_sum AS interim_payment
    ,payments.final_payment_sum AS final_payment 
    ,expenses.milk_promotion_and_adv_sum AS milk_promotion_and_adv_expense
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 > @FiscalYearEnd
    CROSS APPLY
    (
        SELECT  SUM(quota_payment) AS quota_payment_sum,
                SUM(total_deductions) AS total_deductions_sum,
                SUM(net_cheque_or_direct_deposit) AS net_cheque_or_direct_deposit_sum,
                SUM(interim_payment) AS interim_payment_sum,
                SUM(final_payment) AS final_payment_sum
        FROM    payment p
        WHERE   p.statement_number = so.statement_number
    ) payments
    CROSS APPLY
    (
        SELECT  SUM(milk_promotion_and_adv) AS milk_promotion_and_adv_sum,
                -- etc
        FROM    expenses e
        WHERE   e.statement_number = so.statement_number
    ) expenses