Andrey Doronin Andrey Doronin - 6 months ago 16
SQL Question

Get data for fiscal year from table without date columns

I'm trying to create a query (purpose: manual DB testing) that would get the rows of the previous/current/next Fiscal Year and then the SUM(turnover)




Given

(1) the below table,

and

(2) Fiscal Year (FY) = March to February

When Previous FY -- Then 2 rows: 2016/1 to 2016/2

When Current FY -- Then 12 rows: from 2016/3 to 2017/2 (year/month)

When Future FY -- Then 1 row: 2017/3

+--------------+---------------+----------+
| Year (num) | Month (num) | Turnover |
+--------------+---------------+----------+
| 2016 | 1 | 1000 |
+--------------+---------------+----------+
| 2016 | 2 | 2000 |
+--------------+---------------+----------+
| 2016 | 3 | 3000 |
+--------------+---------------+----------+
| 2016 | 4 | 4000 |
+--------------+---------------+----------+
| 2016 | 5 | 2000 |
+--------------+---------------+----------+
| 2016 | 6 | 1000 |
+--------------+---------------+----------+
| 2016 | 7 | 2000 |
+--------------+---------------+----------+
| 2016 | 8 | 1000 |
+--------------+---------------+----------+
| 2016 | 9 | 2000 |
+--------------+---------------+----------+
| 2016 | 10 | 3000 |
+--------------+---------------+----------+
| 2016 | 11 | 4000 |
+--------------+---------------+----------+
| 2016 | 12 | 5000 |
+--------------+---------------+----------+
| 2017 | 1 | 6000 |
+--------------+---------------+----------+
| 2017 | 2 | 2000 |
+--------------+---------------+----------+
| 2017 | 3 | 1000 |
+--------------+---------------+----------+


The best solution I came up with is the below query and change the
Year
values to switch between years. It feels hacky to me because of creating an extra solumn with sysdate and checking for NOT NULL. Is there a more elegant way?

WITH CTE AS (

SELECT
CASE
WHEN Month BETWEEN 3 AND 12 AND Year = 2016
THEN sysdate
WHEN Month BETWEEN 1 AND 2 AND Year = 2017
THEN sysdate
END case_statement_date,

year, month, turnover, FROM Table

)

SELECT sum(turnover) FROM CTE
WHERE case_statement_date IS NOT NULL
;

Answer

Is this what you want?

select year + (case when month >= 3 then 0 else -1 end) as fiscal_year,
       sum(turnover)
from t
group by year + (case when month >= 3 then 0 else -1 end) ;