CODEWITHSUNDEEP
sqloracle-database
mrochester
mrochester

Reputation: 13

Convert financial year and financial quarter into year month

I've got 2 columns in a table, Financial_Year and Financial_Quarter. An example of the data is as follows:

Financial_Year  Financial_Quarter
2018/2019         2

I want to produce a third column called 'Year_month' which is the last month of the quarter within each financial year, so in the above example I want the year_month column to read 201809.

Has anyone got any hints how to do this in Oracle SQL?

Upvotes: 0

Views: 96

Answers (2)

Joakim Danielson
Joakim Danielson

Reputation: 51971

I guess there are many ways to skin this cat, here is one

SELECT CASE WHEN financial_quarter = '4' THEN SUBSTR(financial_year, 6, 4) || '03' 
       ELSE SUBSTR(financial_year, 1, 4) || LPAD((financial_quarter + 1) * 3, 2, '0')
       END
FROM some_table

Of course '4' needs to be replaced with 4 if financial_quarter is numeric

Upvotes: 0

user5683823
user5683823

Reputation:

Assuming the FINANCIAL_YEAR column is a string (two numbers separated by slash) and the financial year 2018/2019 begins on 1 April 2018 and ends on 31 March 2019, you could do something like this:

with
  test_data (financial_year, financial_quarter) as (
    select '2018/2019', 1 from dual union all
    select '2018/2019', 2 from dual union all
    select '2018/2019', 3 from dual union all
    select '2018/2019', 4 from dual
  )
select financial_year, financial_quarter,
       to_char(add_months(to_date(substr(financial_year, 1, 4) || '03', 'yyyymm'),
                          3 * financial_quarter), 'yyyymm') as year_month
from   test_data
;

FINANCIAL_YEAR  FINANCIAL_QUARTER  YEAR_MONTH
--------------  -----------------  ----------
2018/2019                       1  201806
2018/2019                       2  201809
2018/2019                       3  201812
2018/2019                       4  201903

Also, just for fun, here is a different solution that doesn't use any date computations - it's all string based.

select financial_year, financial_quarter,
       substr(financial_year, decode(financial_quarter, 4, 6, 1), 4) ||
       decode(financial_quarter, 1, '06', 2, '09', 3, '12', 4, '03') as year_month
from   test_data
;

Upvotes: 1

Related Questions