Calculate Fiscal |Year

2
votes

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

2
sql
It seems like adding a "Fiscal year" column is just duplicate information of what is in "Time of call", so if possible, I would avoid creating the "Fiscal year" column. Instead, it probably be better to compute the fiscal year from "Time of call" every time you need it. If this to much work for your servers, use some sort of cache that is separate from your database. This way your database has the minimum data you need, and derived data like "Fiscal year" lives somewhere else. – conradkleinespel
Also, in your expected output, you meant to write 2013-14 instead of 2013-12, no? – conradkleinespel
Which DBMS are you using? – a_horse_with_no_name
date & time functions differ a lot, what is your dbms please? MySQL? Sybase? DB2? Postgres? Oracle? MS SQL Server? Terradata? .... – Paul Maxwell

2 Answers

1
votes

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
0
votes

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...