How to Program fiscal year & Months

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 2000 Forums

Transact-SQL (2000)

How to Program fiscal year & Months

Author

Topic

yoko
Starting Member

3 Posts

Posted - 2005-08-30 : 10:38:45

I'm new to T-SQL and the prior DBA wrote a Sproc that prints a report in(Crystal Report V10). The logic is incorrect for the current month and Fiscal Year( it begins July 1). The Headers should read: Actual August 2005 - ACtual August 2004 (Comparison with last Year) and Actual Agust 2005 & Budget August 2005 (Comparison with this year's Budget). The code is:
CREATE Procedure WeeklySalesReport
as

declare @thisMonth integer, @thisYear integer, @fiscalYear int

declare @testDate dateTime

select @testDate = getDate()
--select @testDate = '07/04/05'

select @thisMonth = datepart(month, @testDate) -1
select @thisYear = datepart(year, @testDate)

select @fiscalYear = @thisYear
if( @thisMonth >= 7 )
select @fiscalYear = @fiscalYear+1

declare @startDate datetime, @endDate dateTime

select @endDate = endDate from harvardclub.dbo.fiscalMonths
where monthInfo = @thisMonth and fiscalYear = @fiscalYear

select @startDate = startDate
from harvardclub.dbo.fiscalMonths
where monthInfo = @thisMonth and fiscalYear = @fiscalYear

I have changed it many times - with no results. Any ideas/suggestions will be greatly appreciated.

thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-30 : 13:41:56

Hi Yoko,

If I understand you question(s) there's 2 of them.

Question one is, given a fiscal year start of July 1, Provide code to calculate the fiscal month and year for any given date. Will this work for you?


--it begins July 1
declare @thisMonth integer, @thisYear integer, @fiscalYear int

declare @testDate dateTime

select @testDate = getDate()
--select @testDate = '7/04/05'

select @thisMonth = m + case when m < 7 then 6 else -6 end
	,@thisYear = y + case when m > 6 then 1 else 0 end
from	(
	select	datepart(month, @testDate) m
		,datepart(year, @testDate) y
	) a

select	@testDate ActualDate
	,@thisMonth fiscalMonth
	,@thisYear fiscalYear

Question two has something to do with report headers? You provided no code or table structures that return report header information so I'm afraid you'll have to provide more information about that.

See this link about posting questions that will get answered fast:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

yoko
Starting Member

3 Posts

Posted - 2005-08-30 : 15:11:21

Sorry for not making myself clear. here is the complete SPROC that runs every month:

>> SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
11/04/03
Notes:
1) Added Fiscal Month

2) Determine which fiscal month this is
*/

CREATE Procedure WeeklySalesReport
as

declare @thisMonth integer, @thisYear integer, @fiscalYear int

declare @testDate dateTime

select @testDate = getDate()
--select @testDate = '07/04/05'

select @thisMonth = datepart(month, @testDate) -1
select @thisYear = datepart(year, @testDate)

select @fiscalYear = @thisYear
if( @thisMonth >= 7 )
select @fiscalYear = @fiscalYear+1

declare @startDate datetime, @endDate dateTime

select @endDate = endDate from harvardclub.dbo.fiscalMonths
where monthInfo = @thisMonth and fiscalYear = @fiscalYear

select @startDate = startDate
from harvardclub.dbo.fiscalMonths
where monthInfo = @thisMonth and fiscalYear = @fiscalYear

--select @testDate, @startDate start, @endDate enddate, @fiscalyear, @thisMOnth

select
sum( totalRoomAmount) as RoomSales,
sum( RoomsAvailable ) as RoomsAvailable ,
sum( totalInUse ) as RoomsOccupied,
sum( totalPeople ) as NumberOfPersons,
sum( GuestCount ) as NumberOfGuestRooms,
datePart( dw, reportdate ) as daynum ,
FiscalMonth, convert(varchar(4), fiscalYear ) as fiscalYear

from dailystats
where reportDate between @startDate and @endDate
group by datepart( dw, reportDate ), fiscalmonth, fiscalyear
order by datepart( dw, reportDate )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO <<

The SPROC is used in Crystal Reports V 10 & looks like:
COMPARISON WITH LAST YEAR
--------------------------

ACTUAL ACTUAL
AUGUST AUGUST
2005 2005

rOOM SALES $300,000 $200,000

ROOMS AVAIL 2,000 2,085

ROOMS OCCUP 1,000 1,100

ETC

comparison with this years'budget
--------------------------------

ACTUAL ACTUAL
AUGUST AUGUST
2005 2005

rOOM SALES $300,000 $200,000

ROOMS AVAIL 2,000 2,085

ROOMS OCCUP 1,000 1,100

ETC..

I'm trying to get the SPROC to calculate the Current Month & year (each month) without re-writing. i.e. August, 2005; September 2005 etc.
thanks for your help.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-30 : 16:25:21

>>Sorry for not making myself clear
Still not too clear

Is this what you're looking for?:


declare @testDate dateTime
select @testDate = getDate()

select datename(month, @testDate) + ', ' + datename(year, @testDate)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources