Fiscal Calender Issue... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Fiscal Calender Issue...

Author

Topic

mk
Starting Member

2 Posts

Posted - 2007-10-20 : 09:46:47

Hi,

If some could please help me out with my current problem that would be greatly appreciated.

I am having problem with a stored precedure to create Calander table. I have created this code a while ago and now i am just playing with it.

What i am trying to do is replace 'CASE' statement with IF/ELSE.... As i have changed the code for "FiscalQuarter" it does not work... I got a hunch that it's something small i am missing out..

Can someone please help?

Below is my code,

/*Code for Calendar creation*/
/*
Crated By : Mehbub Khan
This stored procedure creates a financial calendar, like Fiscal year, FiscalMonth, FiscalQuarter etc.
Restriction : It only tracks date from 01/07/1997 till 30/06/2011
*/

CREATE PROC sp_Fiscal_Calendar
AS

CREATE TABLE FiscalCalendar
(
[Date] smalldatetime,
[Day] AS day([Date]),
[DayOfWeek] AS datepart(dw, [Date]),

[FiscalQuarterName] AS
CAST(IF( datepart(q, [Date]) = 0)
'FY' + RIGHT(CAST((datepart(yy,[Date])) as varchar(4)), 2) +'-Q' + CAST((datepart(q, [Date])+2)as varchar(7))
ELSE
'FY' + RIGHT(CAST((datepart(yy,[Date])+ 1) AS VARCHAR(4)), 2) +'-Q' + CAST((datepart(q, [Date])-1)as varchar(7))
as varchar(7)),

[FiscalMonthName] AS DATENAME(MONTH, [Date]) +', ' + DATENAME(YEAR, [Date]),
[FiscalYearName] AS
CAST(CASE WHEN datepart(mm, [Date]) <= 6 THEN
'FY' + RIGHT(CAST((datepart(yy,[Date])) as varchar(4)), 2)
ELSE 'FY' + RIGHT(CAST((datepart(yy,[Date])+1 )AS VARCHAR(4)), 2)
END as varchar(4)),
[FiscalHalfName] AS
CAST(CASE WHEN datepart(mm, [Date]) <= 6 THEN
'FY' + RIGHT(CAST((datepart(yy,[Date])) as varchar(4)), 2) + '-'+'H2'
ELSE 'FY' + RIGHT(CAST((datepart(yy,[Date])+1 )AS VARCHAR(4)), 2) + '-'+'H1'
END as varchar(7)),
[FiscalYearID] AS
CAST(CASE WHEN datepart(mm, [Date]) <= 6 THEN
(datepart(yy,[Date]))
ELSE(datepart(yy,[Date])+1 )
END as NUMERIC),
[FiscalHalfID] AS
CAST(CASE WHEN datepart(mm, [Date]) <= 6 THEN
(CAST((datepart(yy,[Date]))AS NUMERIC ) * 100) + 2
ELSE (CAST((datepart(yy,[Date])+1 )AS NUMERIC)*100) + 1
END as NUMERIC),
[FiscalMonthID] AS
CAST(CASE WHEN datepart(mm, [Date]) <= 6 THEN
(CAST((datepart(yy,[Date]))AS NUMERIC ) * 100) + CAST((datepart(mm,[Date]))AS NUMERIC )
ELSE (CAST((datepart(yy,[Date])+1 )AS NUMERIC)*100) + CAST((datepart(mm,[Date]))AS NUMERIC )
END as NUMERIC),
[FiscalQuarterID] AS
CAST(CASE WHEN datepart(mm, [Date]) <= 6 THEN
(CAST((datepart(yy,[Date])) as NUMERIC) * 100) + CAST((datepart(q, [Date])+ 2)as NUMERIC)
ELSE (CAST((datepart(yy,[Date])+ 1) as NUMERIC) * 100) + CAST((datepart(q, [Date]) - 2)as NUMERIC)
END as NUMERIC),
[CalendarMonthName] AS DATENAME(MONTH, [Date])
)

DECLARE @StartDate datetime
DECLARE @CurrentDay int
DECLARE @endDate datetime

set @StartDate = CONVERT(DATETIME, '1996-07-01 00:00:00')
set @CurrentDay = 0
set @endDate = CONVERT(DATETIME, '2011-06-30 00:00:00')

WHILE @StartDate <= @endDate BEGIN
insert FiscalCalendar ([Date]) values (dateadd(dd, @CurrentDay, @StartDate))
set @StartDate = (dateadd(dd, 1, @StartDate))
END
GO

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-10-21 : 23:21:43

Result seem to be wrong but the syntax is as follows:

[FiscalQuarterName] AS
'FY'
+ RIGHT(CAST((datepart(yy,[Date])
+ CASE datepart(q, [Date]) WHEN 0 THEN 0 ELSE 1 END) as varchar(4)), 2)
+ '-Q'
+ CAST((datepart(q, [Date])-1)as varchar(7)),

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources