August 6, 2012 at 9:43 pm
Comments posted to this topic are about the item Display data by fiscal year instead of calendar year
August 7, 2012 at 12:52 am
Nice article.
As I have to work with harvest years, going from August to July the next year, it is of great interest to me as well.
I am just wondering: Is it much better to do a lot of if statements (a case statement is often a multiple-if statement unless it is very big, in which case it might be converted into a lookup table - not sure what SQL server does) than doing some quick math?
You could have calculated the fiscal month as (MONTH(somedate) + 3) % 12 + 1, which, I think, is less cluttered and much more readable than the other variant.
You could likewise calculate the year as YEAR(somedate) + (MONTH(somedate) + 3) / 12 with an equal increase in readability. Whether or not people understands what happens is another matter, but it should be quite straightforward.
/Keld Laursen
August 7, 2012 at 1:01 am
If you want some flexibility to change fiscal year date ranges the case statements are a bit tedious. I happened to need some fiscal year calculations just the other day so came up with this table-valued function:
CREATE FUNCTION [dbo].[tvfGetFiscalYear]
(
@YearStart VARCHAR(10)
,@YearEnd VARCHAR(10)
,@InputMonth INT
,@InputYear INT
)
RETURNS @FYear TABLE
(
FMonth INT
,FYear INT
,IMonth INT
,IYear INT
)
AS
BEGIN
DECLARE
@FiscalYear INT
,@FiscalMonth INT
,@FiscalYearStart DATE
,@FiscalYearEnd DATE
,@FStartMonth DATE
,@InputDate DATE
SET @InputDate = CAST(CAST(@InputMonth AS VARCHAR(2))+'/01/'+CAST(@InputYear AS VARCHAR(4)) AS DATE)
SET @FiscalYearEnd = CAST(CAST(DATEPART(yyyy,@InputDate) AS CHAR(4))+'/'+@YearEnd AS DATE)
SET @FiscalYearStart = CAST(CAST(DATEPART(yyyy,DATEADD(yy,-1,@InputDate)) AS CHAR(4))+'/'+@YearStart AS DATE)
IF DATEDIFF(day,@FiscalYearEnd,@InputDate) > 0
SET @FiscalYear = YEAR(DATEADD(yy,1,@InputDate))
ELSE IF DATEDIFF(day,@InputDate,@FiscalYearStart) > 0
SET @FiscalYear = YEAR(DATEADD(yy,-1,@InputDate))
ELSE
SET @FiscalYear = YEAR(@InputDate)
SET @FStartMonth = CAST(@YearStart+'/'+CAST(@FiscalYear-1 AS CHAR(4)) AS DATE)
SET @FiscalMonth = CAST(DATEDIFF(MONTH,@FStartMonth,@InputDate) AS INT)+1
INSERT INTO @FYear
SELECT
@FiscalMonth
,@FiscalYear
,@InputMonth
,@InputYear
RETURN
END
Then, use the function like this and get the same results:
DECLARE
@YearStart VARCHAR(10)
,@YearEnd VARCHAR(10)
SET @YearStart = '10/1'
SET @YearEnd = '9/30'
SELECT
COUNT(ur.UserId) AS [User Count]
,DATENAME(MONTH,ur.Registrationdate) AS mnth
,YEAR(ur.Registrationdate) AS Yr
,'FY '+CAST((SELECT FYear FROM dbo.tvfGetFiscalYear(@YearStart,@YearEnd,MONTH(ur.Registrationdate),YEAR(ur.Registrationdate))) AS VARCHAR(7)) AS FY
,(SELECT FMonth FROM dbo.tvfGetFiscalYear(@YearStart,@YearEnd,MONTH(ur.Registrationdate),YEAR(ur.Registrationdate))) AS FYMonth
FROM
dbo.UserRegistration AS ur
WHERE
ur.UserId > 0
GROUP BY
DATENAME(MONTH,ur.Registrationdate)
,YEAR(ur.Registrationdate)
,MONTH(ur.Registrationdate)
ORDER BY
FYMonth
,yr
Probably an even better method would be to use the function's date logic in a stored procedure to calculate the fiscal year/month for all of the rows and then join the results to the UserRegistration table instead of using a function in the select statement. For a large dataset that would likely be more efficient. But I'll leave that for someone else. 😉
August 7, 2012 at 6:48 am
That seems a little complicated for a Fiscal Year function.
The Fiscal Year Logic can be pared down to
select YEAR(DATEADD(m,3,RegistrationDate)) as Fyear
,MONTH(DATEADD(m,3,RegistrationDate)) as Fmonth
-- OR A Table Function
CREATE FUNCTION dbo.FiscalYear
(@SomeDate DateTime)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN SELECT Fyear = YEAR(DATEADD(m,3,@SomeDate))
,Fmonth = MONTH(DATEADD(m,3,@SomeDate))
;
GO
SELECT
fy.Fyear
,fy.Fmonth
from YourTable
CROSS APPLY dbo.FiscalYear(RegistrationDate) fy
Or if you prefer to do it in SSRS create a calculated field with vb.net
=Year(DateAdd(DateInterval.Month,3,Fields!RegistrationDate))
=Month(DateAdd(DateInterval.Month,3,Fields!RegistrationDate))
As stated earlier, you can also build a calendar table in memory and do a join, provided that the Registration Date is just the date value and doesn't have time values. I've been burned on calendar tables with fiscal data I find it easier and usually faster to do the DATEADD function
"There is nothing so useless as doing efficiently that which should not be done at all." - Peter Drucker
August 7, 2012 at 7:16 am
charles.byrne (8/7/2012)
That seems a little complicated for a Fiscal Year function.The Fiscal Year Logic can be pared down to
select YEAR(DATEADD(m,3,RegistrationDate)) as Fyear
,MONTH(DATEADD(m,3,RegistrationDate)) as Fmonth
<snip>
<snip>
I've been burned on calendar tables with fiscal data I find it easier and usually faster to do the DATEADD function
Good advice, and better readability on the result than what I posted.
August 7, 2012 at 7:31 am
CELKO (8/7/2012)
Why not use a Calendar table with whatever fiscal or reporting years you use? The GAAP had 250+ the last time I looked. SQL is not a computional language and function calls only prevent optimization.
That was the solution I was expecting to read about. In the classic tradeoff between computation time and storage space, a few thousand rows worth of calendar table to save the overhead of all these function calls and so-called "more readable" maths would be obvious.
If anyone does write an article solving this problem with a calendar table, please comment in this thread with a link so those of us already discussing it do not miss the alternate implementation. Thanks!
August 7, 2012 at 7:34 am
CELKO (8/7/2012)
Why not use a Calendar table with whatever fiscal or reporting years you use? The GAAP had 250+ the last time I looked. SQL is not a computional language and function calls only prevent optimization.
I have to agree with Mr Celko.
My first thought to the solution to this was a Calendar Table with a column for Fiscal Year and a column for Calendar year. Then you don't have to do anything complicated.
August 7, 2012 at 7:41 am
I used SSAS to create an extensive calendar table, which includes fiscal year. Then just link by date and you can get any combination of calendar data.
August 7, 2012 at 7:50 am
This also doesn't address lunar fiscal calendars, which are based on a pattern of weeks for each month, such as 4-4-5, not just a shift in the month number. A calendar table is a much more useful solution.
August 7, 2012 at 9:20 am
select YEAR(DATEADD(m,3,RegistrationDate)) as Fyear
,MONTH(DATEADD(m,3,RegistrationDate)) as Fmonth
This seems to be cool technic. Thanks for posting.
August 7, 2012 at 1:51 pm
I dobt that this is the best way to do this but it is one way - you would not generate the period table dynamically as each period should be 445 forever so you could generate 10 or 20 years in a real table with correct indexes.
-- Declare a table variable (note - table variables have no indexes and are heaps)
-- This holds the different periods (4,4,5) calculated from a known start date
declare @PeriodTable table
(
Periodvarchar(50),
StartDatedatetime,
EndDatedatetime
)
-- Our data table - only has a date field but this is your normal data table
declare @Dates table
(
Datedatetime
)
-- Some variables to help create the data
declare @WhichPeriod smallint = 3;
declare @NextDatedate
declare @Datedate = '2011-1-1'
-- First add some dates to the date table
while (@Date < '2012-1-1')
begin
insert into @Dates(Date)
values (@Date)
set @Date = Dateadd(day, 1, @Date)
end
-- Now add the periods to the period table with known starting date for the first period
set @Date = '2011-1-1'
while (@Date < '2012-1-1')
begin
set @WhichPeriod=@WhichPeriod + 1;
if (@WhichPeriod >= 3)
set @WhichPeriod = 0
set @NextDate = case when @WhichPeriod = 2 then dateadd(day, 5 * 7, @Date) else dateadd(day, 4*7, @date) end
insert into @PeriodTable(Period, StartDate, EndDate)
values(
case
when @WhichPeriod = 0 then 'Period 1 (4 Weeks)'
when @WhichPeriod = 1 then 'Period 2 (4 Weeks)'
else 'Period 3 (5 Weeks)'
end,
@Date,
dateAdd(day,-1, @NextDate)
)
set @Date = @NextDate
end
-- Query showing the results of the date and the period
select * from @Dates d inner join @PeriodTable p
on d.Date between p.StartDate and p.EndDate
The results are like this
DatePeriodStartDateEndDate
2011-01-01 00:00:00.000Period 1 (4 Weeks)2011-01-01 00:00:00.0002011-01-28 00:00:00.000
2011-01-02 00:00:00.000Period 1 (4 Weeks)2011-01-01 00:00:00.0002011-01-28 00:00:00.000
2011-01-03 00:00:00.000Period 1 (4 Weeks)2011-01-01 00:00:00.0002011-01-28 00:00:00.000
...
2011-01-27 00:00:00.000Period 1 (4 Weeks)2011-01-01 00:00:00.0002011-01-28 00:00:00.000
2011-01-28 00:00:00.000Period 1 (4 Weeks)2011-01-01 00:00:00.0002011-01-28 00:00:00.000
2011-01-29 00:00:00.000Period 2 (4 Weeks)2011-01-29 00:00:00.0002011-02-25 00:00:00.000
2011-01-30 00:00:00.000Period 2 (4 Weeks)2011-01-29 00:00:00.0002011-02-25 00:00:00.000
...
2011-02-24 00:00:00.000Period 2 (4 Weeks)2011-01-29 00:00:00.0002011-02-25 00:00:00.000
2011-02-25 00:00:00.000Period 2 (4 Weeks)2011-01-29 00:00:00.0002011-02-25 00:00:00.000
2011-02-26 00:00:00.000Period 3 (5 Weeks)2011-02-26 00:00:00.0002011-04-01 00:00:00.000
2011-02-27 00:00:00.000Period 3 (5 Weeks)2011-02-26 00:00:00.0002011-04-01 00:00:00.000
...
2011-12-30 00:00:00.000Period 3 (5 Weeks)2011-11-26 00:00:00.0002011-12-30 00:00:00.000
2011-12-31 00:00:00.000Period 1 (4 Weeks)2011-12-31 00:00:00.0002012-01-27 00:00:00.000
Chris
August 7, 2012 at 3:57 pm
mtassin (8/7/2012)
CELKO (8/7/2012)
Why not use a Calendar table with whatever fiscal or reporting years you use? The GAAP had 250+ the last time I looked. SQL is not a computional language and function calls only prevent optimization.I have to agree with Mr Celko.
My first thought to the solution to this was a Calendar Table with a column for Fiscal Year and a column for Calendar year. Then you don't have to do anything complicated.
It really depends on what you mean by complicated.
If your Calendar Table just has a Date value and no time value then you better be certain that the field you're joining on doesn't have time values. If it does then your join by Date may not return values so you'd have to do a conversion or add another field on the table with just the date. You also have to account for the fact that the date may be outside the range
of your calendar table.
The best option is to just have the Fiscal Fields on the table. For most of our stuff we calculate the values during the insert or when the transaction
is approved/final and it remains static.
For those who would prefer to use a Calendar Table that only has date values but your source has a date-time values (string and/or date conversion(s) then inner/left join):
--Just the Date No Time Value
DATEADD(d,DATEDIFF(d,0,RegistrationDate),0)
--OR
CONVERT(DATETIME,(CONVERT(VARCHAR, @MyDate,110)))
--OR
CONVERT(VARCHAR,RegistrationDate,110) --MM-DD-YYYY --
--OR
CONVERT(VARCHAR,RegistrationDate,112) --YYYYMMDD
In this scenario, DATEADD(m,RegistrationDate,3) or the table function
I mentioned earlier seems alot simpler. I know, I know its a Scalar Function, but this logic can be done outside of SQL like a SSRS Calculated field or a static field on the data table if performance is an issue.
"There is nothing so useless as doing efficiently that which should not be done at all." - Peter Drucker
August 9, 2012 at 6:44 am
Just to echo previous comments I've used Calendar tables in which is held all the necessary fiscal/lunar/holiday information required for the business.
Even 100 years worth of data held by the day is not that large a table.
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
August 9, 2012 at 4:23 pm
Robin Sasson (8/9/2012)
Just to echo previous comments I've used Calendar tables in which is held all the necessary fiscal/lunar/holiday information required for the business.Even 100 years worth of data held by the day is not that large a table.
We do have calendar tables for legacy systems, but never more than a few years in the future because of changes in business rules, holidays, business days, etc. A business partner calendar date may change days of business from Mon-Friday to Tues-Saturday. A company may move to a different locale where different holidays are observed etc.
"There is nothing so useless as doing efficiently that which should not be done at all." - Peter Drucker
August 9, 2012 at 5:05 pm
This is just a caveat to readers of this thread...
As I stated earlier, Don't build a calendar table on Dates unless you really need to. Again, it depends on the data and deciding on maintenance and complexity of the calendars you need.
The Fiscal Year in this article is a simple (well defined) time offset by 3 months that can be done in SQL or SSRS. It is not a lunar calendar, a 4-4-5, a holiday calendar, etc. I would just calculate the value (in SQL or outside of SQL) and put it on the original table or when it went into the data warehouse. I wouldn't build/maintain a Calendar table for fiscal years for 100 years. At my place of work we do have Calendar Tables for legacy systems and for constantly changing business rules or for historical and/or effective dating, currency rate history etc.
If you need to calculate the distance between two points via a Trig function then calculate on the fly as you use them (in or out of sql). If performance is an issue then by all means put it in a table.
Would you build an Absolute value table so you didn't have to use the ABS function to reduce processing time? If so then what would be your max number in that table?
Would you build a DateTable for 100 years just to know the day of the week or format the date to different varchar formats? You can I guess and it would be suitable in a table with a 100K or millions of rows, but not
a few thousand.
Again, I'm not trying to detract from calendar tables. They may be needed especially in performance critical scenarios and DW environments and where there may be different rules for different situations and substantially large sets of data.
These kind of things are (sometimes) good candidates for Calendar Tables:
* Data from multiple countries that observe different holidays
* A business partner that has different workdays
(Do they work Tuesday to Saturday compared to your business).
* A business partner who does 4-4-5 and starts the 2nd Sat in January.
* Does the next business day start at Midnight or does it start at 3PM?
* What was the agreed hourly rate from X company on Y date for Z
services?
This algorithm in the article, Fiscal Year, was well defined and doesn't require such. If performance is really an issue then do the calculation outside of SQL or by all means build a Calendar table if you must, in memory or permanently and do your join.
"There is nothing so useless as doing efficiently that which should not be done at all." - Peter Drucker
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply