Databases
StackOverflow2013
Postsdbo
POMy T-SQL query needs additional SUM's from an additional table
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMy T-SQL query needs additional SUM's from an additional table
    text
    copied!<p>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.</p> <pre><code>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 </code></pre> <p>The following is my 'expenses' table:</p> <pre><code>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) </code></pre> <p>Here is the data in my 'expenses' table:</p> <pre><code>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 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload