StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If I understood correctly, the following should work:</p> <pre><code>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 </code></pre> <p>However, I think there's more you can do to make this query run a little better - for example, the multiple sub-selects on <code>statement</code> seem likely to be an easy target for optimization / refactoring.</p>

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