using fiscal year to show results

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)

using fiscal year to show results

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2009-09-15 : 07:27:31

Hi there,

The query I'm working on has to show results within a financial/fiscal year that starts on the last Sunday of March and finishes on the last Saturday of March rather than the Gregarian calandar.

I'm stumped with this one. At present I have a date table which has the following columns:
Year
PeriodNo (Monthly periods)
WeekNo( 1 - 52; 52nd week being the end of March)
WeekStartDate (Always Saturday)
WeekEndDate (Always Sunday)

The idea I had was to use the year column and from Sun - 29/03/2009 - Sat - 27/03/2010, the year column would say 2009, this being the finacial year

Then I could do something with my query. for example a where clause, only showing results that match the year column where it's 2009. However, I can't use where YearCol = Year(getdate()) because when I reach 2010 this will show only results that match 2010 and I want to view results, for example, on Feb 2010 from the end of March 2009.

I'm sure this is a common occurance. I've never really had to face it until now so if anyone could help me on the best way to handle fiscal years I'd be very grateful.

Thanks

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-15 : 07:57:35

In your data table, do you not have a date field?

If you do, then why not use DateCol >= @YearStartDate and DateCol <= @YearEndDate ?

cidr
Posting Yak Master

207 Posts

Posted - 2009-09-15 : 08:29:19

Hi Rick,thanks for getting back to me.

I'm somewhat still new to working with dates. Is it possible you could elaborate in the parameters?

Thanks

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-09-15 : 09:30:05

Declare @YearStartDate as datetime, @YearEndDate as datetime

set @YearStartDate = (select WeekStartDate from DateTable where Year = 2009 and WeekNo = 1)
set @YearEndDate = (select WeekEndDate from DateTable where Year = 2009 and WeekNo = 52)

That is assuming that the Year in your datetable is still set to 2009 for week 52.
Also, you may need to change the WeekStartDate and WeekEndDate around to get the dates you want.

If your Year isn't set correctly, you can still get the correct dates using the dateadd and datediff functions, you could try:


declare @Date datetime
set @Date = '01 April 2009'
select dateadd(dd,(datediff(dd,-53684,@Date)/7)*7,-53684) as startofyear,
dateadd(dd,(datediff(dd,-53684,dateadd(yy,1,@Date))/7)*7,-53684) as endofyear

cidr
Posting Yak Master

207 Posts

Posted - 2009-09-15 : 10:22:08

Thanks again Rick

The top idea could be used, however, because the fiscal year is within two actual years i.e 2009 and 2010, I'm not really sure how to set this up so it's dynamic so that I don't have to change the YearCol in the year clause to 2010 etc.

If I used yearCol = year(getdate()) for example, I'd have the problem of hitting 2010 and this turning to yearCol = 2010 when it's 2009 still.

Do you know of a good way to do this?

The second suggestion I was puzzled with, again, if it was 2010, this code might present a problem for me and I'm not so familiar with it to go ahead with this one

Anymore help would be appreciated. I'm working with dates more these dates and hopefully will get up to speed quickly because learning date problems can be painful for me :)

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources