SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fiscal Year


Fiscal Year

Author
Message
jbon007
jbon007
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 80
Hi all,

i have this function which is not fully working. what it does is look at the date and work out our financial year. (financial year is from 1 april to 31 march)

for example for financial year 2010-04-01 to 2010-03-01

I would like to show


2010/11 2010-04-01
2010/11 2010-05-01
.
.
.
.
2010/11 2011-01-01
2010/11 2011-02-01
2010/11 2011-03-01

the function i use is this-



ALTER function [dbo].[fFinYear] (@FM char(6))
returns char(7)
as
begin
return
left(@FM,4)+
case
when substring(@FM,3,2)='99' then '/00'
when substring(@FM,3,2)+1<10 then '/0'+rtrim(convert(varchar(2),substring(@FM,3,2)+1))
else '/'+rtrim(convert(varchar(2),substring(@FM,3,2)+1))
end
end




what this does is

2010/11 2010-04-01
2010/11 2010-05-01
.
.
.
.



2011/12 2011-01-01
2011/12 2011-02-01
2011/12 2011-03-01




I would like it to stay 2010/11 to end of march and once it reaches april then increase to 2011/12. I would be ever greatful if someone can help me on this?
LutzM
LutzM
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23603 Visits: 13559
Probably the easiest way to deal with fiscal years not following the calendar year would be using a calendar table where you'd add the FY information per day.
You could also use such a table to store working day as well as holiday information.
Usually quite useful.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94453 Visits: 38956
jbon007 (1/27/2011)
Hi all,

i have this function which is not fully working. what it does is look at the date and work out our financial year. (financial year is from 1 april to 31 march)

for example for financial year 2010-04-01 to 2010-03-01

I would like to show


2010/11 2010-04-01
2010/11 2010-05-01
.
.
.
.
2010/11 2011-01-01
2010/11 2011-02-01
2010/11 2011-03-01

the function i use is this-



ALTER function [dbo].[fFinYear] (@FM char(6))
returns char(7)
as
begin
return
left(@FM,4)+
case
when substring(@FM,3,2)='99' then '/00'
when substring(@FM,3,2)+1<10 then '/0'+rtrim(convert(varchar(2),substring(@FM,3,2)+1))
else '/'+rtrim(convert(varchar(2),substring(@FM,3,2)+1))
end
end




what this does is

2010/11 2010-04-01
2010/11 2010-05-01
.
.
.
.



2011/12 2011-01-01
2011/12 2011-02-01
2011/12 2011-03-01




I would like it to stay 2010/11 to end of march and once it reaches april then increase to 2011/12. I would be ever greatful if someone can help me on this?





Parapharsing here, the new fiscal year (2011/12, now called 2012 for my purposes) start on 2011-04-01 an ends on 2012-03-31, correct?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94453 Visits: 38956
Does this help you get started?


declare @TestDate date;
set @TestDate = '20110401';
select @TestDate, DATEADD(mm,9,@TestDate), CAST(year(DATEADD(mm,9,@TestDate)) - 1 as VARCHAR) + '/' + RIGHT(CAST(year(DATEADD(mm,9,@TestDate)) as VARCHAR),2);
set @TestDate = '20120331';
select @TestDate, DATEADD(mm,9,@TestDate), CAST(year(DATEADD(mm,9,@TestDate)) - 1 as VARCHAR) + '/' + RIGHT(CAST(year(DATEADD(mm,9,@TestDate)) as VARCHAR),2);



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
jbon007
jbon007
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 80
hi lynn,

thats right for fiscal year 01/04/2010 to 31/03/2011 would be 2010/11
jbon007
jbon007
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 80
Finally got it working. Many thanks for all of you to providing help.

i've posted the code if anyone else comes to the same problem.



USE [ColossusUser]
GO
/****** Object: UserDefinedFunction [dbo].[fFinYear] Script Date: 01/28/2011 22:05:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[fFinYear] (@v_date datetime)
RETURNS varchar(7) AS

BEGIN
DECLARE @v_year as VARCHAR(7)

IF MONTH(@v_date) > 3
BEGIN
SET @v_year = CAST(year(@v_date) as varchar) + '/' + Right(CAST((year(@v_date)) +1 as varchar),2)
END

IF MONTH(@v_date) between 0 and 3
BEGIN
SET @v_year = CAST(year(@v_date) -1 as varchar) + '/' + Right(CAST((year(@v_date) -1) +1 as varchar),2)
END



RETURN @v_year

END



Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94453 Visits: 38956
Two more options. Option 2 allows you to use the function in a CROSS APPLY in a query instead of using the Scalar UDF in the select part of a query. You will find that the cross apply is faster.


DROP FUNCTION dbo.fFinYear;
GO
CREATE FUNCTION [dbo].[fFinYear] (@v_date datetime)
RETURNS varchar(7) AS
BEGIN
return CAST(year(DATEADD(mm,9,@v_date)) - 1 as VARCHAR) + '/' + RIGHT(CAST(year(DATEADD(mm,9,@v_date)) as VARCHAR),2);
END;
GO
select dbo.fFinYear('20110128');
GO

DROP FUNCTION dbo.fFinYear;
GO
CREATE FUNCTION [dbo].[fFinYear] (@v_date datetime)
RETURNS table
AS
return select CAST(year(DATEADD(mm,9,@v_date)) - 1 as VARCHAR) + '/' + RIGHT(CAST(year(DATEADD(mm,9,@v_date)) as VARCHAR),2) as FiscalYear;
go
select * from dbo.fFinYear('20110128');
go
drop function dbo.fFinYear;
GO



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search