My T-SQL query needs additional SUM's from an additional table
The following is my current query. It works the way i want it to. Howev开发者_开发百科er, 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
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.
精彩评论