SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Calculate values for 4-4-5 fiscal calendar
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jughead1111
Starting Member

14 Posts

Posted - 10/04/2007 :  17:01:02  Show Profile  Reply with Quote
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 - 10/05/2007 :  14:57:19  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 10/05/2007 :  15:45:57  Show Profile  Reply with Quote
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 - 10/05/2007 :  16:05:57  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 10/05/2007 :  17:46:50  Show Profile  Reply with Quote
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 - 10/09/2007 :  08:19:31  Show Profile  Reply with Quote
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

Sweden
29910 Posts

Posted - 10/09/2007 :  09:04:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 10/09/2007 :  09:26:05  Show Profile  Reply with Quote
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 Posts

Posted - 09/29/2012 :  10:36:43  Show Profile  Reply with Quote
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

USA
9 Posts

Posted - 11/30/2012 :  16:52:34  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 12/01/2012 :  00:09:24  Show Profile  Reply with Quote
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

USA
8 Posts

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






Edited by - Howard43Willard on 12/02/2012 20:22:27
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000