Calculate Fiscal Year

SQL Server 2005/CF8

Page 1 of 1

13 Replies - 11052 Views - Last Post: 19 March 2009 - 12:02 PM Rate Topic: -----

#1 armyCoder   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 56
  • Joined: 02-February 09

Calculate Fiscal Year

Posted 17 March 2009 - 11:14 AM

Ok folks, got a new brainbuster for ya. Couldn't find any results on Google specific to CF.

The system I'm working on tracks mm/dd/yyyy for every entry into the system.
Our fiscal year is 1 OCT - 30 SEP.

I need to display results from each fiscal year.

And I'm lost.

DateAdd/DateDiff maybe?

Thanks in advance.

This post has been edited by armyCoder: 17 March 2009 - 11:15 AM

Is This A Good Question/Topic? 0
  • +

Replies To: Calculate Fiscal Year

#2 xheartonfire43x   User is offline

  • D.I.C Regular

Reputation: 46
  • View blog
  • Posts: 454
  • Joined: 22-December 08

Re: Calculate Fiscal Year

Posted 17 March 2009 - 11:49 AM

What is the code you have now? Or at least what does your DB look like?
Was This Post Helpful? 0
  • +
  • -

#3 armyCoder   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 56
  • Joined: 02-February 09

Re: Calculate Fiscal Year

Posted 17 March 2009 - 01:12 PM

View Postxheartonfire43x, on 17 Mar, 2009 - 11:49 AM, said:

What is the code you have now? Or at least what does your DB look like?


Well I think may have figured it out. Sorry for not including more detailed code on my first post.

See, when a new row is entered into my DB, the entryDate field is populated with the date and time like this: mm/dd/yyyy - hh:mm

So I wanted to be able to show which fiscal year that row was in, the start of the new fiscal year being October 1.

So I thought, why not pull the values from the date and just do some simple if statements...

<cfset mm=#left(rf.entrydate, 2)#>
<cfset dd=#mid(rf.entrydate, 4, 2)#>
<cfset yyyy=#mid(rf.entrydate, 7, 4)#>
<cfset nextYear=yyyy + 1>
<cfoutput><cfif mm gte 10 and dd gte 01>FY #nextYear#
<cfelse>FY #yyyy#</cfif></cfoutput>



So if my database shows a record as 03/17/2009 - 11:18, this calculator will output FY 2009. If it's 11/18/2009 - 12:09, it will output FY 2010

There's probably a prettier and more correct way to do this, and if you can think of one, please do share :)

...but this works and it took me about 5 minutes to code once I discovered that the #Mid function exists.
Was This Post Helpful? 0
  • +
  • -

#4 xheartonfire43x   User is offline

  • D.I.C Regular

Reputation: 46
  • View blog
  • Posts: 454
  • Joined: 22-December 08

Re: Calculate Fiscal Year

Posted 17 March 2009 - 01:31 PM

The only problem with the mid function is that in order for it to always work the date always needs to be formated the exact same, or else it will pull the wrong characters.
Was This Post Helpful? 0
  • +
  • -

#5 sansclue   User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: Calculate Fiscal Year

Posted 17 March 2009 - 01:49 PM

View PostarmyCoder, on 17 Mar, 2009 - 12:12 PM, said:

...but this works and it took me about 5 minutes to code once I discovered that the #Mid function exists.


If "entrydate" is a date/time value, use date (not string) functions. That is what they are there for, and they are better suited to handle dates properly.

I would set it up as a function for easy reuse.

<cfset mm = datePart("m", theDate)>
<cfif mm gte 10 and mm lte 12>
	 <cfreturn datePart("yyyy", theDate) + 1>
<cfelse>
	<cfreturn datePart("yyyy", theDate)>
</cfif>







Hmm... since you are using SQL Server 2005 - why not a create sql udf ?

SELECT dbo.udf_getFiscalYear( theColumn ) AS FiscalYear, OtherColumns
FROM yourTable
Was This Post Helpful? 0
  • +
  • -

#6 armyCoder   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 56
  • Joined: 02-February 09

Re: Calculate Fiscal Year

Posted 18 March 2009 - 05:44 AM

Quote

The only problem with the mid function is that in order for it to always work the date always needs to be formated the exact same, or else it will pull the wrong characters.


Valid point, but this field is automatically populated with a specific format of date and time values, so it will never change. However, after reading sansclue's reply I've realized that if I would use datetime I could save myself a lot of headaches and code.


View Postsansclue, on 17 Mar, 2009 - 01:49 PM, said:

If "entrydate" is a date/time value, use date (not string) functions. That is what they are there for, and they are better suited to handle dates properly.

I would set it up as a function for easy reuse.

<cfset mm = datePart("m", theDate)>
<cfif mm gte 10 and mm lte 12>
	 <cfreturn datePart("yyyy", theDate) + 1>
<cfelse>
	<cfreturn datePart("yyyy", theDate)>
</cfif>



I had to go row by row, luckily I only had 10 or so in this table. In order to make the conversion to datetime I had to manually remove the "-" that I had CF setup to enter. It's been pretty much OTJ training so I'm learning best practices usually after I start doing things a certain way. I now see that including a "-" in that field and only using string values was limiting me substantially.




View Postsansclue, on 17 Mar, 2009 - 01:49 PM, said:

Hmm... since you are using SQL Server 2005 - why not a create sql udf ?

SELECT dbo.udf_getFiscalYear( theColumn ) AS FiscalYear, OtherColumns
FROM yourTable


I assume this is some kind of stored procedure with IF statements? I haven't ever done one, I'll do some searching and try to piece something together. Thanks for the great advice.
Was This Post Helpful? 0
  • +
  • -

#7 sansclue   User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: Calculate Fiscal Year

Posted 18 March 2009 - 07:01 AM

View PostarmyCoder, on 18 Mar, 2009 - 04:44 AM, said:

I assume this is some kind of stored procedure with IF statements? I haven't ever done one, I'll do some searching and try to piece something together. Thanks for the great advice.


It is a user defined function, similar in concept to cffunction. You can create either scalar functions (ie return single value) or table functions ( ie return a virtual table ). A great addition to later versions of sql server IMO.
Was This Post Helpful? 0
  • +
  • -

#8 kzimmerm   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 70
  • Joined: 08-February 09

Re: Calculate Fiscal Year

Posted 18 March 2009 - 04:15 PM

View Postsansclue, on 18 Mar, 2009 - 06:01 AM, said:

View PostarmyCoder, on 18 Mar, 2009 - 04:44 AM, said:

I assume this is some kind of stored procedure with IF statements? I haven't ever done one, I'll do some searching and try to piece something together. Thanks for the great advice.


It is a user defined function, similar in concept to cffunction. You can create either scalar functions (ie return single value) or table functions ( ie return a virtual table ). A great addition to later versions of sql server IMO.



I would agree here. The UDF is the way to go. I did something quite similar to this to change the date/time to show correctly based upon what timezone someone is in. The concept is quite the same. Years ago I created a fiscal calendar identifying the date range of each month, since they overlapped the Gregorian calendar. If you use this concept then you can easily figure out which fiscal month/quarter/year you are in. Your UDF should be designed to return the fiscal month,quarter,year by passing the current date and an additional parameter identifying what you want returned.

Sounds simple enough?

Good luck
Kurt
Was This Post Helpful? 0
  • +
  • -

#9 armyCoder   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 56
  • Joined: 02-February 09

Re: Calculate Fiscal Year

Posted 19 March 2009 - 10:44 AM

View Postkzimmerm, on 18 Mar, 2009 - 04:15 PM, said:

Sounds simple enough?

I wish.


Here's what my function looks like.

USE [GEPRS]
GO
/****** Object:  UserDefinedFunction [dbo].[get_fiscal_year]	Script Date: 03/19/2009 10:01:01 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[get_fiscal_year]
  ( @fDate datetime )
RETURNS varchar(100)
AS
BEGIN
DECLARE @ZEROMM VARCHAR(10)
DECLARE @ZERODD VARCHAR(10)
DECLARE @ZEROHH VARCHAR(10)
DECLARE @ZEROMI VARCHAR(10)
DECLARE @ZEROSS VARCHAR(10)
DECLARE @TEST VARCHAR(10)
SET @ZEROMM = ''
SET @ZERODD = ''
SET @ZEROHH  = ''
SET @ZEROMI  = ''
SET @ZEROSS  = ''
SET @TEST  = ''
IF LEN(CAST(DATEPART(MM, @fDate ) AS VARCHAR(10))) = 1 
SET @ZEROMM = '0'
IF LEN(CAST(DATEPART(DD, @fDate ) AS VARCHAR(10))) = 1
SET @ZERODD = '0'
IF LEN(CAST(DATEPART(HH, @fDate ) AS VARCHAR(10))) = 1 
SET @ZEROHH = '0'
IF LEN(CAST( DATEPART(MI, @fDate ) AS VARCHAR(10)))  = 1
SET @ZEROMI= '0'
IF LEN(CAST( DATEPART(SS, @fDate ) AS VARCHAR(10)))  = 1
SET @ZEROSS = '0'
IF (DATEPART(MM, @fDate ) ) > 9
SET @fDATE =  DATEADD(YYYY, 1,  @fDATE) --Change to FY
  RETURN Cast(DATEPART(YYYY, @fDate ) AS VARCHAR(10))  + 
@ZEROMM + CAST(DATEPART(MM, @fDate ) AS VARCHAR(10)) +
@ZERODD + CAST( DATEPART(DD, @fDate ) AS VARCHAR(10)) +  
@ZEROHH + CAST(DATEPART(HH, @fDate ) AS VARCHAR(10)) +  
@ZEROMI + CAST( DATEPART(MI, @fDate ) AS VARCHAR(10))  + 
@ZEROSS + CAST( DATEPART(SS, @fDate ) AS VARCHAR(10)) 
END



It's under my scalar functions but something isn't working right. Here's what I did to test this function

create table test
(entrydate datetime,
fiscalyear as (dbo.get_fiscal_year(entrydate)))

insert into test (entrydate) 
values ('9/25/2009')

select * from test



But the output in the fiscalyear column is "200909250000"

Not sure where I'm going wrong here...many thanks
Was This Post Helpful? 0
  • +
  • -

#10 sansclue   User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: Calculate Fiscal Year

Posted 19 March 2009 - 11:21 AM

What do you want the function to return? ie Year only 2009, 2010, etc...?
Was This Post Helpful? 0
  • +
  • -

#11 armyCoder   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 56
  • Joined: 02-February 09

Re: Calculate Fiscal Year

Posted 19 March 2009 - 11:34 AM

View Postsansclue, on 19 Mar, 2009 - 11:21 AM, said:

What do you want the function to return? ie Year only 2009, 2010, etc...?


Yes, I revised the function (which was passed to me by a co worker) and removed all of the other stuff it was returning. But it still doesn't correctly show the fiscal year. For instance, if the entrydate is 09/25/2009 it's showing 2009...

USE [GEPRS]
GO
/****** Object:  UserDefinedFunction [dbo].[get_fiscal_year]	Script Date: 03/19/2009 13:24:42 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[get_fiscal_year]
  ( @fDate datetime )
RETURNS varchar(100)
AS
BEGIN
DECLARE @ZEROMM VARCHAR(10)
SET @ZEROMM = ''
IF LEN(CAST(DATEPART(MM, @fDate ) AS VARCHAR(10))) = 1 
SET @ZEROMM = '0'
IF (DATEPART(MM, @fDate ) ) > 9
SET @fDATE =  DATEADD(YYYY, 1,  @fDATE) --Change to FY
  RETURN Cast(DATEPART(YYYY, @fDate ) AS VARCHAR(10)) 
END


Was This Post Helpful? 0
  • +
  • -

#12 armyCoder   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 56
  • Joined: 02-February 09

Re: Calculate Fiscal Year

Posted 19 March 2009 - 11:40 AM

Wait I'm an idiot. Is it the weekend yet?

that would be GREATER than 9, so of course 9/25 would return 2009.

Thanks :)
Was This Post Helpful? 0
  • +
  • -

#13 sansclue   User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: Calculate Fiscal Year

Posted 19 March 2009 - 11:44 AM

View PostarmyCoder, on 19 Mar, 2009 - 10:34 AM, said:

IF (DATEPART(MM, @fDate ) ) > 9
SET @fDATE = DATEADD(YYYY, 1, @fDATE) --Change to FY


Yes, because according to the code month 9 is still the same fiscal year. Only months 10, 11 and 12 are shifted to the next year.
Was This Post Helpful? 0
  • +
  • -

#14 sansclue   User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: Calculate Fiscal Year

Posted 19 March 2009 - 12:02 PM

Ah, okay. I didn't see your later message until after I posted.

View PostarmyCoder, on 19 Mar, 2009 - 10:40 AM, said:

Is it the weekend yet?


No. But please do let us know when it gets here. I really need it ;-)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1