CODEWITHSUNDEEP
sql
whitz11
whitz11

Reputation: 229

Calculate Fiscal |Year

I'm trying to add a fiscal year column. Our fiscal year looks like this Q1 apr-jun, Q2 jul-sept, Q3 oct-dec, Q4 jan-mar

I have the column 'TimeOfCall' which displays 

2013-01-26 07:44:51.000
2014-03-12 13:21:34.000 
2015-12-25 16:25:21.000  etc

Searching the forum i have tried using the following. For the date 2015-12-25 this gives the fiscal year as 2016. I need it to be 2015.

case when month(timeofcall) >4
then year(timeofcall) +1
else year(timeofcall) 
end as fiscal

Ideally I'd like the output to look like 

TimeoFCall                      Fiscal Year
2013-01-26 07:44:51.000            2012-13
2014-03-12 13:21:34.000            2013-12
2015-12-25 16:25:21.000            2015-16

but this would suffice

TimeoFCall                      Fiscal Year
2013-01-26 07:44:51.000             2012
2014-03-12 13:21:34.000             2013
2015-12-25 16:25:21.000             2015

Thank you

Upvotes: 2

Views: 1699

Answers (2)

dnoeth
dnoeth

Reputation: 60472

A simple solution is "extract the year after subtracting 3 months", e.g. Oracle syntax:

extract(year from add_months(timeofcall,-3))

Of course you should also have a calendar where things like that are precalculated...

Upvotes: 0

conradkleinespel
conradkleinespel

Reputation: 6997

Would this work? It says "if april and up, take the current year, else, take the previous year". For instance, April 2015 is fiscal year 2015, but March 2015 is fiscal year 2014.

case when month(timeofcall) >= 4
then year(timeofcall)
else year(timeofcall) - 1
end as fiscal

Upvotes: 1

Related Questions