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
 SQL Server Development (2000)
 Calculate values for 4-4-5 fiscal calendar

Author  Topic 

jughead1111
Starting Member

14 Posts

Posted - 2007-10-04 : 17:01:02
I'm writing a procedure for calculating the fiscal periods in a 4-4-5 calendar. We use a calendar year for our fiscal year. Therefore, the 1st period (January) is always a different number of days.

I have finished most of the script but when I put in a date other than a January date it gives an endless loop.

Could someone take a look and see where my mistake is? Code is below

ALTER PROCEDURE [dbo].[spJCTFiscalCalc]
-- Add the parameters for the stored procedure here
@date datetime, -- The date being sent to the procedure
@fy_year int output,
@fy_period int output,
@wtd_start datetime output,
@wtd_end datetime output,
@mtd_start datetime output,
@mtd_end datetime output

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
/****************************************************************************/
-- Declare variables
/****************************************************************************/
-- Starting fiscal offsets
declare @fy_ofs_date datetime
declare @fy_ofs_year int
declare @fy_ofs_period int

-- Variables for determining days in Jan
declare @FirstDayOfYear datetime
declare @FirstFriday datetime
declare @LastDayInJan datetime
declare @LastDayofYear datetime
declare @FirstPeriodDays int

-- Variable for loop
declare @dloop datetime

-- Variables to hold calculated values
declare @period int
declare @year int
declare @end datetime

/****************************************************************************/
-- Initialize variables
/****************************************************************************/

--First determine how many days are in the first period since it differs each year
select @FirstDayOfYear = Convert(char(10), '1/1/' + Convert(char(4), datepart(yyyy, @date)), 120)
--Check to see if its Friday
if datepart(dw, @FirstDayOfYear) = 6
select @FirstFriday = @FirstDayOfYear
--Check to see if its Saturday
if datepart(dw, @FirstDayOfYear) = 7
select @FirstFriday = DateAdd(d, 6, @FirstDayOfYear)
else
select @FirstFriday = DateAdd(d, 0-(datepart(dw, @FirstDayOfYear)-6), @FirstDayOfYear)

select @LastDayInJan = DateAdd(wk, 3, @FirstFriday)
select @FirstPeriodDays = DateDiff(d, @FirstDayOfYear, @LastDayInJan)

-- Set the beginning offsets for FY 2005. We will count forward or
-- backward from this point to get our information.
select @fy_ofs_date = @FirstDayOfYear
select @fy_ofs_year = Convert(int, datepart(yyyy, @date))
select @fy_ofs_period = 1

-- Is this date in the past or future?
if datediff(d,@fy_ofs_date,@date) >= 0
begin
-- Start at the offset and work up until we reach our date
select @dloop = @fy_ofs_date
select @period = 1
select @year = @fy_ofs_year
-- Loop through dates
while @dloop <= @date
begin
-- Break out of loop if the next increment will
-- put us over the selected date
if @period = 1
begin
if dateadd(d, @FirstPeriodDays, @dloop) > @date
begin
break
end
end
else
begin
if @period = 3 or @period = 6 or @period = 9 or @period = 12
begin
if dateadd(d,35,@dloop) > @date
begin
break
end
end
else
begin
if dateadd(d,27,@dloop) >= @date
begin
break
end
end
end
-- Increment according to period
if @period = 1
begin
if dateadd(d, @FirstPeriodDays, @dloop) > @date
begin
break
end
end
else
begin
if @period = 3 or @period = 6 or @period = 9 or @period = 12
begin
select @dloop = dateadd(d,35,@dloop)
end
else
begin
select @dloop = dateadd(d,27,@dloop)+1
end
-- Increment period number
if @period = 12
begin
select @period = 1
select @year = @year + 1
end
else
begin
select @period = @period + 1
end
end
end
end

-- Put results in output parameters
select @fy_year = @year
select @fy_period = @period
select @mtd_start = @dloop

-- Set last day of period
if @period = 1
begin
begin
select @mtd_end = dateadd(d, @FirstPeriodDays, @mtd_start)
end
end
else
begin
if @period = 3 or @period = 6 or @period = 9 or @period = 12
begin
select @mtd_end = dateadd(d,34,@mtd_start)
end
else
begin
select @mtd_end = dateadd(d,27,@mtd_start)
end
end
-- Set week to date range (loop backwards to find start of week - Sunday)
select @dloop = @date
while datepart(dw,@dloop) <> 1
begin
select @dloop = dateadd(d,-1,@dloop)
end
select @wtd_start = @dloop
select @wtd_end = dateadd(d,6,@wtd_start)


END

jughead1111
Starting Member

14 Posts

Posted - 2007-10-05 : 14:57:19
Never mind...I figured the problem out. If anyone is interested in the code



ALTER PROCEDURE [dbo].[spJCTFiscalCalc]
-- Add the parameters for the stored procedure here
@date datetime, -- The date being sent to the procedure
@fy_year int output,
@fy_period int output,
@wtd_start datetime output,
@wtd_end datetime output,
@mtd_start datetime output,
@mtd_end datetime output

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
/****************************************************************************/
-- Declare variables
/****************************************************************************/
-- Starting fiscal offsets
declare @fy_ofs_date datetime
declare @fy_ofs_year int
declare @fy_ofs_period int

-- Variables for determining days in Jan
declare @FirstDayOfYear datetime
declare @FirstFriday datetime
declare @LastDayInJan datetime
declare @LastDayofYear datetime
declare @FirstPeriodDays int

-- Variable for loop
declare @dloop datetime

-- Variables to hold calculated values
declare @period int
declare @year int
declare @end datetime

/****************************************************************************/
-- Initialize variables
/****************************************************************************/

--First determine how many days are in the first period since it differs each year
select @FirstDayOfYear = Convert(char(10), '1/1/' + Convert(char(4), datepart(yyyy, @date)), 120)
--Check to see if its Friday
if datepart(dw, @FirstDayOfYear) = 6
select @FirstFriday = @FirstDayOfYear
--Check to see if its Saturday
if datepart(dw, @FirstDayOfYear) = 7
select @FirstFriday = DateAdd(d, 6, @FirstDayOfYear)
else
select @FirstFriday = DateAdd(d, 0-(datepart(dw, @FirstDayOfYear)-6), @FirstDayOfYear)

select @LastDayInJan = DateAdd(wk, 3, @FirstFriday)
select @FirstPeriodDays = DateDiff(d, @FirstDayOfYear, @LastDayInJan)


select @fy_ofs_date = @FirstDayOfYear
select @fy_ofs_year = Convert(int, datepart(yyyy, @date))
select @fy_ofs_period = 1

--if period = 1
-- begin
-- if dateadd(d, @FirstPeriodDays, @dloop) > @date
-- end

-- Is this date in the past or future?
if datediff(d,@fy_ofs_date,@date) >= 0
begin --1
-- Start at the offset and work up until we reach our date
select @dloop = @fy_ofs_date
select @period = 1
select @year = @fy_ofs_year

if @period = 1
begin
if dateadd(d, @FirstPeriodDays, @dloop) > @date
begin
select @fy_year = @year
select @fy_period = @period
select @mtd_start = @dloop
end
else
begin
select @dloop = dateadd(d, @FirstPeriodDays, @dloop) +1
select @period = @period + 1
end
-- Loop through dates
while @dloop <= @date
begin --2
-- Break out of loop if the next increment will
-- put us over the selected date
if @period = 12
begin
break
end
else
begin
if @period = 3 or @period = 6 or @period = 9
begin
if dateadd(d,35,@dloop) > @date
begin
break
end
end
else
begin
if dateadd(d,27,@dloop) >= @date
begin
break
end
end
-- Increment according to period
if @period = 3 or @period = 6 or @period = 9
begin
select @dloop = dateadd(d,35,@dloop)
end
else
begin
select @dloop = dateadd(d,27,@dloop)+1
end
-- Increment period number
if @period = 12
begin
select @fy_year = @year
select @fy_period = @period
select @mtd_start = @dloop
end
else
begin
select @period = @period + 1
end
end --2
end
end --1
end


-- Put results in output parameters
select @fy_year = @year
select @fy_period = @period
select @mtd_start = @dloop


-- Set last day of period
if @period = 12
begin
select @mtd_end = convert(datetime, '12/31/' + convert(char(4), datepart(yyyy, @date)), 120)
end
else
begin
if @period = 1
begin
select @mtd_end = dateadd(d, @FirstPeriodDays, @mtd_start)
end
else
Begin
if @period = 3 or @period = 6 or @period = 9
begin
select @mtd_end = dateadd(d,34,@mtd_start)
end
else
begin
select @mtd_end = dateadd(d,27,@mtd_start)
end
end
end
-- Set week to date range (loop backwards to find start of week - Sunday)
select @dloop = @date
while datepart(dw,@dloop) <> 1
begin
select @dloop = dateadd(d,-1,@dloop)
end
select @wtd_start = @dloop
select @wtd_end = dateadd(d,6,@wtd_start)

END



I would be appreaciated if anyone could add any thoughts or suggestions on how to improve this code.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-10-05 : 15:45:57
You didn't really explain what a "4-4-5 calendar" is, so it is hard to comment on what you are trying to so, or if there is a simpler way.




CODO ERGO SUM
Go to Top of Page

jughead1111
Starting Member

14 Posts

Posted - 2007-10-05 : 16:05:57
I apologize for the confusion. A 4-4-5 calendar is a fiscal calendar where month is a certain number of weeks.

Month 1 - 4 weeks
Month 2 - 4 weeks
Month 3 - 5 weeks
Month 4 - 4 weeks
Month 5 - 4 weeks
Month 6 - 5 weeks
Month 7 - 4 weeks
Month 8 - 4 weeks
Month 9 - 5 weeks
Month 10 - 4 weeks
Month 11 - 4 weeks
Month 12 - 5 weeks

You can start on any day. My company just happens to start on 1/1/yyyy and end on 12/31/yyyy

My procedure above attempts to figure out a given month start, month end, week start, week end, period (1-12), and fiscal year based on a date passed to the procedure.

I know a table could be used for the data and could be used to lookup the dates, but I thought this might be a better solution.

I hope this clears up the confusion.

I hope this helps
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-10-05 : 17:46:50
This code does it with a single select statement.

You can turn it into a stored procedure it you want.


select aaa.*,
PERIOD_OF_YEAR =
case
when WEEK_OF_YEAR < 5 then 1
when WEEK_OF_YEAR < 9 then 2
when WEEK_OF_YEAR < 14 then 3
when WEEK_OF_YEAR < 18 then 4
when WEEK_OF_YEAR < 22 then 5
when WEEK_OF_YEAR < 27 then 6
when WEEK_OF_YEAR < 31 then 7
when WEEK_OF_YEAR < 35 then 8
when WEEK_OF_YEAR < 40 then 9
when WEEK_OF_YEAR < 44 then 10
when WEEK_OF_YEAR < 48 then 11
else 12 end,
FY_PERIOD_START =
dateadd(dd,
case
when WEEK_OF_YEAR < 5 then 0
when WEEK_OF_YEAR < 9 then 4
when WEEK_OF_YEAR < 14 then 8
when WEEK_OF_YEAR < 18 then 13
when WEEK_OF_YEAR < 22 then 17
when WEEK_OF_YEAR < 27 then 21
when WEEK_OF_YEAR < 31 then 26
when WEEK_OF_YEAR < 35 then 30
when WEEK_OF_YEAR < 40 then 34
when WEEK_OF_YEAR < 44 then 39
when WEEK_OF_YEAR < 48 then 43
else 47 end*7,FY_START),
FY_PERIOD_END =
case
when WEEK_OF_YEAR >= 48 then FY_END
else
dateadd(dd,(
case
when WEEK_OF_YEAR < 5 then 4
when WEEK_OF_YEAR < 9 then 8
when WEEK_OF_YEAR < 14 then 13
when WEEK_OF_YEAR < 18 then 17
when WEEK_OF_YEAR < 22 then 21
when WEEK_OF_YEAR < 27 then 26
when WEEK_OF_YEAR < 31 then 30
when WEEK_OF_YEAR < 35 then 34
when WEEK_OF_YEAR < 40 then 39
when WEEK_OF_YEAR < 44 then 43
else 48 end*7)-1,FY_START)
end,
FY_WEEK_START = dateadd(dd,(WEEK_OF_YEAR-1)*7,FY_START),
FY_WEEK_END =
case
when dateadd(dd,((WEEK_OF_YEAR-1)*7)+6,FY_START) > FY_END
then FY_END
else dateadd(dd,((WEEK_OF_YEAR-1)*7)+6,FY_START)
end
from
(
Select aaaa.*,
WEEK_OF_YEAR = ((DAY_OF_FY-1)/7)+1
from
(
select aaaaa.*,
DAY_OF_FY = datediff(dd,FY_START,DATE)+1
from
(
select DATE = dateadd(dd,datediff(dd,0,b.date),0),
FY_START = dateadd(yy,datediff(yy,0,b.date),0),
FY_END = dateadd(yy,datediff(yy,-1,b.date),-1)
from
(
select
-- Generate test data to give one year of output
DATE =dateadd(dd,number,'20070101')
from
-- Function available on this link
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
F_TABLE_NUMBER_RANGE(0,366) ) b
) aaaaa ) aaaa ) aaa


CODO ERGO SUM
Go to Top of Page

jughead1111
Starting Member

14 Posts

Posted - 2007-10-09 : 08:19:31
I will look at the results more closely, but upon first glance it looks like I have a lot to learn.

There are always more than one way to skin the cat.

Thanks

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 09:04:27
quote:
Originally posted by jughead1111

Month 1 - 4 weeks
Month 2 - 4 weeks
Month 3 - 5 weeks
Month 4 - 4 weeks
Month 5 - 4 weeks
Month 6 - 5 weeks
Month 7 - 4 weeks
Month 8 - 4 weeks
Month 9 - 5 weeks
Month 10 - 4 weeks
Month 11 - 4 weeks
Month 12 - 5 weeks
Where do you put the 1 or 2 remaining days?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

jughead1111
Starting Member

14 Posts

Posted - 2007-10-09 : 09:26:05
Our fiscal is always 1/1/yyyy and 12/31/yyyy. The remaining days in December is always counted in the last period 12. When you use calendar year like this the number of days in the first period (January) and the last period (December) fluctuates year to year.

In 2006 period 1 had 27 days and period 12 had 37 days.
In 2007 period 1 had 26 days and period 12 will have 38 days.




Go to Top of Page

maddog
Starting Member

1 Post

Posted - 2012-09-29 : 10:36:43
quote:
Originally posted by Michael Valentine Jones

This code does it with a single select statement.

You can turn it into a stored procedure it you want.


select aaa.*,
PERIOD_OF_YEAR =
case
when WEEK_OF_YEAR < 5 then 1
when WEEK_OF_YEAR < 9 then 2
when WEEK_OF_YEAR < 14 then 3
when WEEK_OF_YEAR < 18 then 4
when WEEK_OF_YEAR < 22 then 5
when WEEK_OF_YEAR < 27 then 6
when WEEK_OF_YEAR < 31 then 7
when WEEK_OF_YEAR < 35 then 8
when WEEK_OF_YEAR < 40 then 9
when WEEK_OF_YEAR < 44 then 10
when WEEK_OF_YEAR < 48 then 11
else 12 end,
FY_PERIOD_START =
dateadd(dd,
case
when WEEK_OF_YEAR < 5 then 0
when WEEK_OF_YEAR < 9 then 4
when WEEK_OF_YEAR < 14 then 8
when WEEK_OF_YEAR < 18 then 13
when WEEK_OF_YEAR < 22 then 17
when WEEK_OF_YEAR < 27 then 21
when WEEK_OF_YEAR < 31 then 26
when WEEK_OF_YEAR < 35 then 30
when WEEK_OF_YEAR < 40 then 34
when WEEK_OF_YEAR < 44 then 39
when WEEK_OF_YEAR < 48 then 43
else 47 end*7,FY_START),
FY_PERIOD_END =
case
when WEEK_OF_YEAR >= 48 then FY_END
else
dateadd(dd,(
case
when WEEK_OF_YEAR < 5 then 4
when WEEK_OF_YEAR < 9 then 8
when WEEK_OF_YEAR < 14 then 13
when WEEK_OF_YEAR < 18 then 17
when WEEK_OF_YEAR < 22 then 21
when WEEK_OF_YEAR < 27 then 26
when WEEK_OF_YEAR < 31 then 30
when WEEK_OF_YEAR < 35 then 34
when WEEK_OF_YEAR < 40 then 39
when WEEK_OF_YEAR < 44 then 43
else 48 end*7)-1,FY_START)
end,
FY_WEEK_START = dateadd(dd,(WEEK_OF_YEAR-1)*7,FY_START),
FY_WEEK_END =
case
when dateadd(dd,((WEEK_OF_YEAR-1)*7)+6,FY_START) > FY_END
then FY_END
else dateadd(dd,((WEEK_OF_YEAR-1)*7)+6,FY_START)
end
from
(
Select aaaa.*,
WEEK_OF_YEAR = ((DAY_OF_FY-1)/7)+1
from
(
select aaaaa.*,
DAY_OF_FY = datediff(dd,FY_START,DATE)+1
from
(
select DATE = dateadd(dd,datediff(dd,0,b.date),0),
FY_START = dateadd(yy,datediff(yy,0,b.date),0),
FY_END = dateadd(yy,datediff(yy,-1,b.date),-1)
from
(
select
-- Generate test data to give one year of output
DATE =dateadd(dd,number,'20070101')
from
-- Function available on this link
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
F_TABLE_NUMBER_RANGE(0,366) ) b
) aaaaa ) aaaa ) aaa


CODO ERGO SUM



This reply is for MVJ - I know this is an old post, but am looking around for a SQL 2000 (yes I know) solution to calculate an annual 4-4-5 calendar with a fixed Jan 1 and Dec 31 fiscal year start and end date, but with a 'week' being Saturday through Friday versus Sunday through Saturday. So in the SQL example above, what would need to be adjusted to make the starting day of the week shift to Saturday? I played around a bit with the dateadd functions at the bottom but was not successful. Cheers!
Go to Top of Page

hoghunter
Starting Member

9 Posts

Posted - 2012-11-30 : 16:52:34
I know the post is old but I wanted to point out the error with it. A 4-4-5 fiscal calendar doesn't always start on 1/1. It starts one day after the previous year ended. The rule is a year mus end within the calendar year and not have more than 7 days remaining in the calendar year. So if a year started on say 12/27/1998 as ours did in 1998, it would end on 12/25/1999. Then a fiscal start of 12/26/1999 would force that year to end on 12/25/2000 which leave 7 days. So that you was a 4-4-5, 4-4-5, 4-4-5, 4-4-6 year. It ended 12/30/2000.

So your computation needs to be able to account for the fiscal year starting on a different day than Jan 1.

quote:
Originally posted by Michael Valentine Jones

This code does it with a single select statement.

You can turn it into a stored procedure it you want.


select aaa.*,
PERIOD_OF_YEAR =
case
when WEEK_OF_YEAR < 5 then 1
when WEEK_OF_YEAR < 9 then 2
when WEEK_OF_YEAR < 14 then 3
when WEEK_OF_YEAR < 18 then 4
when WEEK_OF_YEAR < 22 then 5
when WEEK_OF_YEAR < 27 then 6
when WEEK_OF_YEAR < 31 then 7
when WEEK_OF_YEAR < 35 then 8
when WEEK_OF_YEAR < 40 then 9
when WEEK_OF_YEAR < 44 then 10
when WEEK_OF_YEAR < 48 then 11
else 12 end,
FY_PERIOD_START =
dateadd(dd,
case
when WEEK_OF_YEAR < 5 then 0
when WEEK_OF_YEAR < 9 then 4
when WEEK_OF_YEAR < 14 then 8
when WEEK_OF_YEAR < 18 then 13
when WEEK_OF_YEAR < 22 then 17
when WEEK_OF_YEAR < 27 then 21
when WEEK_OF_YEAR < 31 then 26
when WEEK_OF_YEAR < 35 then 30
when WEEK_OF_YEAR < 40 then 34
when WEEK_OF_YEAR < 44 then 39
when WEEK_OF_YEAR < 48 then 43
else 47 end*7,FY_START),
FY_PERIOD_END =
case
when WEEK_OF_YEAR >= 48 then FY_END
else
dateadd(dd,(
case
when WEEK_OF_YEAR < 5 then 4
when WEEK_OF_YEAR < 9 then 8
when WEEK_OF_YEAR < 14 then 13
when WEEK_OF_YEAR < 18 then 17
when WEEK_OF_YEAR < 22 then 21
when WEEK_OF_YEAR < 27 then 26
when WEEK_OF_YEAR < 31 then 30
when WEEK_OF_YEAR < 35 then 34
when WEEK_OF_YEAR < 40 then 39
when WEEK_OF_YEAR < 44 then 43
else 48 end*7)-1,FY_START)
end,
FY_WEEK_START = dateadd(dd,(WEEK_OF_YEAR-1)*7,FY_START),
FY_WEEK_END =
case
when dateadd(dd,((WEEK_OF_YEAR-1)*7)+6,FY_START) > FY_END
then FY_END
else dateadd(dd,((WEEK_OF_YEAR-1)*7)+6,FY_START)
end
from
(
Select aaaa.*,
WEEK_OF_YEAR = ((DAY_OF_FY-1)/7)+1
from
(
select aaaaa.*,
DAY_OF_FY = datediff(dd,FY_START,DATE)+1
from
(
select DATE = dateadd(dd,datediff(dd,0,b.date),0),
FY_START = dateadd(yy,datediff(yy,0,b.date),0),
FY_END = dateadd(yy,datediff(yy,-1,b.date),-1)
from
(
select
-- Generate test data to give one year of output
DATE =dateadd(dd,number,'20070101')
from
-- Function available on this link
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
F_TABLE_NUMBER_RANGE(0,366) ) b
) aaaaa ) aaaa ) aaa


CODO ERGO SUM

Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-12-01 : 00:09:24
quote:
Originally posted by hoghunter

I know the post is old but I wanted to point out the error with it. A 4-4-5 fiscal calendar doesn't always start on 1/1. It starts one day after the previous year ended. The rule is a year mus end within the calendar year and not have more than 7 days remaining in the calendar year. So if a year started on say 12/27/1998 as ours did in 1998, it would end on 12/25/1999. Then a fiscal start of 12/26/1999 would force that year to end on 12/25/2000 which leave 7 days. So that you was a 4-4-5, 4-4-5, 4-4-5, 4-4-6 year. It ended 12/30/2000.

So your computation needs to be able to account for the fiscal year starting on a different day than Jan 1.
...



Every organization has different rules. The OP said "...We use a calendar year for our fiscal year...", so their fiscal year starts on Jan 1 and ends on Dec 31.

My post was meant for the OP, not for the way you organization does things.





CODO ERGO SUM
Go to Top of Page

Howard43Willard
Starting Member

8 Posts

Posted - 2012-12-02 : 20:20:11
I have finished most of the script but when I put in a date other than a January date it gives an endless loop.





Go to Top of Page
   

- Advertisement -