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... :)

Question

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 values 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.

Answer 1

Hi there brookmarker,

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