Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Fiscal Year Expand / Collapse
Author
Message
Posted Thursday, January 27, 2011 3:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 4, 2012 11:52 AM
Points: 26, 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?



Post #1054984
Posted Thursday, January 27, 2011 4:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, March 4, 2015 3:33 PM
Points: 6,897, Visits: 13,550
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
Post #1055014
Posted Thursday, January 27, 2011 5:57 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 21,749, Visits: 34,413
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?



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)
Post #1055028
Posted Thursday, January 27, 2011 6:03 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 21,749, Visits: 34,413
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);




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)
Post #1055029
Posted Friday, January 28, 2011 1:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 4, 2012 11:52 AM
Points: 26, Visits: 80
hi lynn,

thats right for fiscal year 01/04/2010 to 31/03/2011 would be 2010/11
Post #1055137
Posted Friday, January 28, 2011 3:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 4, 2012 11:52 AM
Points: 26, 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


Post #1055653
Posted Friday, January 28, 2011 5:09 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 21,749, Visits: 34,413
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




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)
Post #1055687
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse