CASE Expression For Year Over Year Sales Comparison?

Question

I'm trying to do a year over year comparison with sales variance. I have two SQL statements below that pull the correct data I want. I think I should be using a CASE expression here? If so,the examples I have been looking at use CASE WHEN YEAR (DATEFIELD) = DATE THEN X ELSE X END)? What confuses me is that our FY starts in December, so the first month of Data is from a different year. Any guidance here is truly appreciated.

Current Fiscal Year Data

SELECT B.COMPANY, SUM(C.PRICE_BEFORE_TAX) AS CURRENTFY
FROM ARINVOICE A
 LEFT OUTER JOIN ARCUSTO B ON A.ARCUSTO_ID = B.ID
 LEFT OUTER JOIN V_ARINVOICE_DETAIL C ON A.ID = C.ARINVOICE_ID
WHERE TRUNC(A.INVOICE_DATE) >= TO_DATE('12/01/2018' , 'MM/DD/YYYY') 
AND TRUNC(A.INVOICE_DATE) <= TO_DATE('11/30/2019' , 'MM/DD/YYYY')
GROUP BY B.COMPANY
ORDER BY CURRENTFY DESC)

Last Fiscal Year Data

(SELECT B.COMPANY, SUM(C.PRICE_BEFORE_TAX) AS LASTFY
 FROM ARINVOICE A
 LEFT OUTER JOIN ARCUSTO B ON A.ARCUSTO_ID = B.ID
 LEFT OUTER JOIN V_ARINVOICE_DETAIL C ON A.ID = C.ARINVOICE_ID
 WHERE TRUNC(A.INVOICE_DATE) >= TO_DATE('12/01/2017' , 'MM/DD/YYYY') 
 AND TRUNC(A.INVOICE_DATE) <= (SYSDATE-365)
 GROUP BY B.COMPANY
 ORDER BY LASTFY DESC))

Desired Results

SELECT B.COMPANY, CURRENTFY, LASTFY, (((CURRENTFY-LASTFY)/CURRENTFY)*100) AS SALESVARIANCE

EDIT---------------------

I am using the following thanks to Gordon

SELECT c.COMPANY,
   SUM(CASE WHEN id.INVOICE_DATE >= DATE '2018-12-01' AND id.INVOICE_DATE <= DATE '2019-12-01'
        THEN id.PRICE_BEFORE_TAX
   END) AS CURRENTFY,
   SUM(CASE WHEN id.INVOICE_DATE >= DATE '2017-12-01' AND id.INVOICE_DATE <= (SYSDATE-365)
        THEN id.PRICE_BEFORE_TAX
   END) AS LASTFY,
ROUND(((SUM(CASE WHEN id.INVOICE_DATE >= DATE '2018-12-01' AND id.INVOICE_DATE <= DATE '2019-12-01'
        THEN id.PRICE_BEFORE_TAX
   END) -
   SUM(CASE WHEN id.INVOICE_DATE >= DATE '2017-12-01' AND id.INVOICE_DATE <= (SYSDATE-365)
        THEN id.PRICE_BEFORE_TAX
   END))/(SUM(CASE WHEN id.INVOICE_DATE >= DATE '2018-12-01' AND id.INVOICE_DATE <= DATE '2019-12-01'
        THEN id.PRICE_BEFORE_TAX
   END)))*100 , 2) AS SALESVARIANCE
FROM ARINVOICE i LEFT JOIN
 ARCUSTO c
 ON i.ARCUSTO_ID = c.ID LEFT JOIN
 V_ARINVOICE_DETAIL id
 ON i.ID = id.ARINVOICE_ID
GROUP BY c.COMPANY
ORDER BY CURRENTFY DESC

Answer 1

Simply use conditional aggregation:

SELECT c.COMPANY,
       SUM(CASE WHEN id.INVOICE_DATE >= DATE '2018-12-01' AND id.INVOICE_DATE < DATE '2019-12-01'
                THEN id.PRICE_BEFORE_TAX
           END) AS CURRENTFY,
       SUM(CASE WHEN id.INVOICE_DATE >= DATE '2017-12-01' AND id.INVOICE_DATE < DATE '2018-12-01'
                THEN id.PRICE_BEFORE_TAX
           END) AS LASTFY
FROM ARINVOICE i LEFT JOIN
     ARCUSTO c
     ON i.ARCUSTO_ID = c.ID LEFT JOIN
     V_ARINVOICE_DETAIL id
     ON i.ID = id.ARINVOICE_ID
GROUP BY c.COMPANY
ORDER BY CURRENTFY DESC;

Notes:

• This changes the table aliases to be meaningful, rather than arbitrary letters.
• This uses the DATE keyword for date constants.
• Date comparisons are used directly on the values, so extracting the date portion of the invoice is not needed.
• I removed the time comparisons in the WHERE so it is easier to add more fiscal years. Obviously, you can still filter on the last two years of values.