Search code examples
sqlgroup-bysumcasebetween

Outputting the results of 2 SQL date ranges into one output

I've been trying to sort this for ages and am now probably overthinking it so hoping you can help me.

I have a sales transaction table. In it, is a dated list of every transaction which I want to view by account code and put side by side, but to compare the account 2019 spend against their 2020 spend using date ranges

So far, I can only get either one or the other, never both.

I've looked at nested selects, union and more than I can recall but I'm not getting anywhere.

I've gotten closer, I think with this code:

SELECT inv_account, 
    (SELECT SUM(basevalue)) from salestrans WHERE inv_invoicedate BETWEEN '2019-04-01' AND '2020-03-31'), 
    (SELECT SUM(basevalue)from salestrans WHERE inv_invoicedate BETWEEN '2020-04-01' AND '2021-03-31') 
FROM salestrans
GROUP BY inv_account

This is giving me the total for every account, not individual accounts. Now I need to filter it down by individual account which I assume goes somewhere in the SELECTs in brackets but aren't sure how to do this.

Solution

  • Use conditional aggregation:

    SELECT inv_account, 
       SUM(CASE WHEN inv_invoicedate BETWEEN '2019-04-01' AND '2020-03-31' THEN basevalue ELSE 0 END) total1, 
       SUM(CASE WHEN inv_invoicedate BETWEEN '2020-04-01' AND '2021-03-31' THEN basevalue ELSE 0 END) total2
FROM salestrans
GROUP BY inv_account