The purpose of the sp is to create a calendar table that contains info about dates that users can sql in conjuction with other user tables with time / date data already converted in various formats. Note that the cursor table is not updated.
This is the full sp...
create procedure [localTableOwner].[Time_20]
@job int = 0
as
set nocount on
declare @current_calendar char(50)
declare @curr_cal_desc char(50)
declare @count int
declare @dateend datetime
declare @dateseed datetime
declare @error_var int
declare @error_message char(50)
declare @is_work_day char(1)
declare @number_of_years char(3)
declare @parm_value char(80)
declare @previous_calendar char(10)
declare @Row_Count int
declare @start_date char(23)
truncate table [localTableOwner].[Time_Calendar]
set identity_insert [localTableOwner].[Time_Calendar] on
declare calendar_cursor insensitive cursor for
select calendar, descr
from otherDatabase.otherUser.cclcald with (nolock)
insert into [localTableOwner].[Time_Calendar] (Time_key) values (1)
set identity_insert [localTableOwner].[Time_Calendar] off
exec [localTableOwner].[get_config_info] 'Time','Start Date', @parm_value output
set @start_date = CONVERT (datetime, @parm_value ,103)
exec [localTableOwner].[get_config_info] 'Time','Number of Years', @parm_value output
set @number_of_years = substring(@parm_value,1,3)
set datefirst 1 -- 1 = Mon (7 = Sun).
open calendar_cursor
fetch next
from calendar_cursor
into @current_calendar,
@curr_cal_desc
while @@FETCH_STATUS = 0
begin
-- Reset the @dateseed and @dateend variables prior to processing all the dates for the current
-- calendar
set @dateseed = cast(@start_date as datetime)
set @dateend = dateadd(yy,cast(@number_of_years as int),@dateseed)
while @dateseed < @dateend and @current_calendar is not null
begin
insert into [localTableOwner].[Time_Calendar] -- Insert Fields.
(
[Date],
[Calendar_Year],
[Calendar_Month],
[Calendar_Day],
[Day],
[Dy],
[Day_of_Week],
[Month],
[Mth],
[Quarter],
[Quarter_Name],
[Qtr],
[IS_Weekend],
[Calendar],
[Calendar_Description]
)
values -- With Values.
(
@dateseed,
datepart(yyyy,@dateseed),
datepart(mm,@dateseed),
datepart(dd,@dateseed),
datename(dw,@dateseed),
case datename(dw,@dateseed) -- Day Name.
when 'Monday' then 'Mon'
when 'Tuesday' then 'Tue'
when 'Wednesday' then 'Wed'
when 'Thursday' then 'Thu'
when 'Friday' then 'Fri'
when 'Saturday' then 'Sat'
else 'Sun'
end,
datepart(dw,@dateseed),
datename(mm,@dateseed), -- Month Name.
case datepart(mm,@dateseed) -- Short Month Name.
when 1 then 'Jan'
when 2 then 'Feb'
when 3 then 'Mar'
when 4 then 'Apr'
when 5 then 'May'
when 6 then 'Jun'
when 7 then 'Jul'
when 8 then 'Aug'
when 9 then 'Sep'
when 10 then 'Oct'
when 11 then 'Nov'
else 'Dec'
end,
datepart(qq,@dateseed), -- Quarter Number.
case datename(qq,@dateseed) -- Quarter Name.
when 1 then '1st Quarter'
when 2 then '2nd Quarter'
when 3 then '3rd Quarter'
else '4th Quarter'
end,
case datename(qq,@dateseed) -- Short Quarter Name.
when 1 then '1Qtr'
when 2 then '2Qtr'
when 3 then '3Qtr'
else '4Qtr'
end,
case datename(dw,@dateseed) -- Weekend Indicator.
when 'Saturday' then 'T'
when 'Sunday' then 'T'
else 'F'
end,
@current_calendar,
@curr_cal_desc
)
set @dateseed = @dateseed + 1
end
fetch next
from calendar_cursor
into @current_calendar,
@curr_cal_desc
end
close calendar_cursor
deallocate calendar_cursor
go