SQL Server Performance Forum – Threads Archive
Financial Year calculation
Hi guys, This is probably a fairly simple query but I am really struggling with it!! I am trying to calculate a financial year to date flag for a time dimension. The time dimension holds dates from 1 Jan 1998 up to 31 Dec 2020. The financial year starts on the 17 April and runs for 52 weeks. In terms of calculating the YTD when I am still in the current year, I can make that work. However, my issue is with dealing with dates that are not in the same year as the start date of the financial year. For example: April 1 2006 is financial year 2005. I tried to take the approach below but it flags everything from the start of last years financial year up to today as the current financial year to date – not just everything from 17 April this year. casewhen d_time_caldate < cast(cast(year(getdate()) as varchar(4)) +’-‘ + ‘4’ + ‘-‘ + ’17’ as datetime) and d_time_caldate between cast(cast(year(getdate())-1 as varchar(4)) +’-‘ + ‘4’ + ‘-‘ + ’17’ as datetime) and getdate() then ‘Y’
when d_time_caldate between cast(cast(year(getdate()) as varchar(4)) +’-‘ + ‘4’ + ‘-‘ + ’17’ as datetime) and getdate() then ‘Y’ else ‘N’ end I know the issue is with the first section of the case statement – I just can’t work out how to make the code do this correctly!! Any comments/assistance would be greatly appreciated….. Thanks all!!
Petew
check this: declare @caldate1 datetime,@caldate2 datetime
select @caldate1=’2006-04-16′,@caldate2=’2006-04-21′
select case when @caldate1 between cast(convert(varchar(4),year(getdate()))+’-04’+’-17′ as datetime) and getdate()
then ‘y’ else ‘n’ end,
case when @caldate2 between cast(convert(varchar(4),year(getdate()))+’-04’+’-17′ as datetime) and getdate()
then ‘y’ else ‘n’ end
If I understand this correctly, this should give you some ideas:
DECLARE @date_in DATETIME
DECLARE @date_not_in DATETIME
SELECT @date_in = ‘20070401’, @date_not_in = ‘20070418’
SELECT
CASE
WHEN @date_in BETWEEN ‘20060417’ AND DATEADD(WEEK, 52, ‘20060417’)
THEN ‘Date is in Financial Year 2006’
ELSE ‘Date is not in Financial Year 2006’
END
, CASE
WHEN @date_not_in BETWEEN ‘20060417’ AND DATEADD(WEEK, 52, ‘20060417’)
THEN ‘Date is in Financial Year 2006’
ELSE ‘Date is not in Financial Year 2006’
END
———————————- ———————————-
Date is in Financial Year 2006 Date is not in Financial Year 2006 (1 row(s) affected) In production you would concatenate the string in the BETWEEN and CAST them to DATETIME to parameterize the query.
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
…and depending on how to handle the 17 of April, you would use BETWEEN (inclusive 17th) or >= AND < (exclusive). —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
Guys, Thanks for this (especially the inspiration from Frank!)….. Final solution as follows….The post from Frank reminded me that life becomes much easier when you know what the start date of your year is…..
——————-
declare @currfinyear int
declare @currfinyearstart datetime –Work out the current financial Year
select @currfinyear = (select case when getdate() < cast(cast(year(getdate()) as varchar(4)) +’-‘ + ‘4’ + ‘-‘ + ’17’ as datetime) then
year(getdate()) -1 else year(getdate()) end) —Calculate my current financial year start date
select @currfinyearstart = ( select cast(cast(@currfinyear as varchar(4)) +’-‘ + ‘4’ + ‘-‘ + ’17’ as datetime)) select @currfinyear,@currfinyearstart select d_time_caldate,
case
when d_time_caldate between @currfinyearstart and getdate() then
‘Y’ else ‘N’ end
from d_time ——————–
This happily gives me flags to identify the current financial year to date. Of course…….it only works properly if the financial year starts on 17 April every year. I am going to build up a table the calculates the start and end dates for the financial years correctly (ie: Find out the start date of the first financial year and then increment years in 52 week chunks to get more accurate start and end dates) Cheers all
Petew
All, In case you’re interested, I tidied things up and played around with the code. Rather than create a new table just to hold start and end dates for financial years, I have used the below to calculate on the fly based on the date entry in the d_time_caldate field: [email protected] is the start date for all of the calculations. This could be set to any start –date required.
select @currfinsource = ’04-18-2005′ select @currfinyearstart = (dateadd(week,52 * (year(@currfinsource) * -1 + year(getdate())),@currfinsource)) select @currfinyearstart
if getdate() < @currfinyearstart
begin
select @currfinyearstart = dateadd(week,-52,@currfinyearstart)
end select @currfinyear = year(@currfinyearstart) select
case
when d_time_caldate between @currfinyearstart and getdate() then
‘Y’ else ‘N’ end,
–Which financial year does this date belong to
year(
case when
d_time_caldate < dateadd(week,52 * (year(@currfinsource) * -1 + year(d_time_caldate)),@currfinsource)
then dateadd(week,-52,dateadd(week,52 * (year(@currfinsource) * -1 + year(d_time_caldate)),@currfinsource))
else
dateadd(week,52 * (year(@currfinsource) * -1 + year(d_time_caldate)),@currfinsource)
end
) Petew
What don’t you update your time dimension with fiscal columns and use the queries above to update your dimension? Our time dimension does not only contain calendar dates but also fiscal dates. May the Almighty God bless us all!
www.empoweredinformation.com
Hm, are we talking about about OLAP or OLTP? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)
]]>