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: Yesterday @ 11:43 AM
Points: 6,826, Visits: 13,279
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: Yesterday @ 7:22 PM
Points: 20,680, Visits: 32,279
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: Yesterday @ 7:22 PM
Points: 20,680, Visits: 32,279
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 8:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:15 PM
Points: 1,945, Visits: 3,008
Build a calendar table for this. Put in the holidays and all the other temporal data you use.

Your problem is that you are still thinking like a procedrual progammer and like database programmer


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1055358
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: Yesterday @ 7:22 PM
Points: 20,680, Visits: 32,279
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