Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Holiday Calendar Generator Expand / Collapse
Author
Message
Posted Thursday, February 24, 2005 11:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 1:05 PM
Points: 175, Visits: 168

Hello all.

I would like to create a calendar table within SQL Server, include holidays and populate it for a few years.  Does anyone have a set of statements they have used before for something like this?

 

Post #163841
Posted Thursday, February 24, 2005 12:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 18, 2006 12:01 PM
Points: 41, Visits: 1

I made a monthly calender...c if it helps.u can probably build further on this.omit the parts which r necessary in ur case..


CREATE PROCEDURE MonthlyCalendar
@year int,
@month int,
@program_id int
as
begin
declare @fdm int
declare @fdmstr varchar(10)
declare @datestr varchar(10)
declare @date1 datetime
declare @M1 datetime
declare @M2 datetime
declare @M3 datetime
declare @M4 datetime
declare @M5 datetime
declare @T1 datetime
declare @T2 datetime
declare @T3 datetime
declare @T4 datetime
declare @T5 datetime
declare @W1 datetime
declare @W2 datetime
declare @W3 datetime
declare @W4 datetime
declare @W5 datetime
declare @TH1 datetime
declare @TH2 datetime
declare @TH3 datetime
declare @TH4 datetime
declare @TH5 datetime
declare @F1 datetime
declare @F2 datetime
declare @F3 datetime
declare @F4 datetime
declare @F5 datetime
declare @S1 datetime
declare @S2 datetime
declare @S3 datetime
declare @S4 datetime
declare @S5 datetime
declare @Su1 datetime
declare @Su2 datetime
declare @Su3 datetime
declare @Su4 datetime
declare @Su5 datetime
declare @AM1 int
declare @AM2 int
declare @AM3 int
declare @AM4 int
declare @AM5 int
declare @AT1 int
declare @AT2 int
declare @AT3 int
declare @AT4 int
declare @AT5 int
declare @AW1 int
declare @AW2 int
declare @AW3 int
declare @AW4 int
declare @AW5 int
declare @ATH1 int
declare @ATH2 int
declare @ATH3 int
declare @ATH4 int
declare @ATH5 int
declare @AF1 int
declare @AF2 int
declare @AF3 int
declare @AF4 int
declare @AF5 int
set @datestr=cast(@month as varchar(2))+'/01/'+cast(@year as varchar(4))
set @date1=convert(datetime,@datestr,101)
set @fdm=datepart(dw,@date1)
set @fdmstr=datename(dw,@date1)
--print @date1
--print @fdmstr
if @fdmstr='Monday'
begin
 set @M1 =@date1
 set @T1 =dateadd(dd,1,@date1)
 set @W1 =dateadd(dd,2,@date1)
 set @TH1 =dateadd(dd,3,@date1)
 set @F1 =dateadd(dd,4,@date1)
 set @S1 =dateadd(dd,5,@date1)
 set @Su1 =dateadd(dd,6,@date1)
end
if @fdmstr='Tuesday'
begin
  set @M1 =NULL
 set  @T1 =@date1
  set @W1 =dateadd(dd,1,@date1)
  set @TH1 =dateadd(dd,2,@date1)
  set @F1 =dateadd(dd,3,@date1)
  set @S1 =dateadd(dd,4,@date1)
  set @Su1 =dateadd(dd,5,@date1)
end
if @fdmstr='Wednesday'
begin
 set  @M1 =NULL
  set @T1 =NULL
  set @W1 =@date1
  set @TH1 =dateadd(dd,1,@date1)
  set @F1 =dateadd(dd,2,@date1)
  set @S1 =dateadd(dd,3,@date1)
  set @Su1 =dateadd(dd,4,@date1)
end
if @fdmstr='Thursday'
begin
  set @M1 =NULL
  set @T1 =NULL
  set @W1 =NULL
  set @TH1 =@date1
  set @F1 =dateadd(dd,1,@date1)
  set @S1 =dateadd(dd,2,@date1)
  set @Su1 =dateadd(dd,3,@date1)
end
if @fdmstr='Friday'
begin
  set @M1 =NULL
  set @T1 =NULL
  set @W1 =NULL
  set @TH1 =NULL
  set @F1 =@date1
  set @S1 =dateadd(dd,1,@date1)
  set @Su1 =dateadd(dd,2,@date1)
end
if @fdmstr='Saturday'
begin
  set @M1 =NULL
  set @T1 =NULL
  set @W1 =NULL
  set @TH1 =NULL
  set @F1 =NULL
  set @S1 =@date1
  set @Su1 =dateadd(dd,1,@date1)
end
if @fdmstr='Sunday'
begin
  set @M1 =NULL
  set @T1 =NULL
  set @W1 =NULL
  set @TH1 =NULL
  set @F1 =NULL
  set @S1 =NULL
  set @Su1 =@date1
end
SET @M2=DATEADD(DD,1,@SU1)
SET @M3=DATEADD(DD,7,@M2)
SET @M4=DATEADD(DD,7,@M3)
SET @M5=DATEADD(DD,7,@M4)
IF Month(@M5) <> @month
Set @M5=Null
SET @T2=DATEADD(DD,1,@M2)
SET @T3=DATEADD(DD,7,@T2)
SET @T4=DATEADD(DD,7,@T3)
SET @T5=DATEADD(DD,7,@T4)
IF Month(@T5) <> @month
Set @T5=Null
SET @W2=DATEADD(DD,1,@T2)
SET @W3=DATEADD(DD,7,@W2)
SET @W4=DATEADD(DD,7,@W3)
SET @W5=DATEADD(DD,7,@W4)
IF Month(@W5) <> @month
Set @W5=Null
SET @TH2=DATEADD(DD,1,@W2)
SET @TH3=DATEADD(DD,7,@TH2)
SET @TH4=DATEADD(DD,7,@TH3)
SET @TH5=DATEADD(DD,7,@TH4)
IF Month(@TH5) <> @month
Set @TH5=Null
SET @F2=DATEADD(DD,1,@TH2)
SET @F3=DATEADD(DD,7,@F2)
SET @F4=DATEADD(DD,7,@F3)
SET @F5=DATEADD(DD,7,@F4)
IF Month(@F5) <> @month
Set @F5=Null
select @AM1=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @m1)=0 )
select @AM2=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @m2)=0 )
select @AM3=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @m3)=0 )
select @AM4=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @m4)=0 )
select @AM5=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @m5)=0 )
select @AT1=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @t1)=0 )
select @AT2=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @t2)=0 )
select @AT3=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @t3)=0 )
select @AT4=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @t4)=0 )
select @AT5=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @t5)=0 )
select @AW1=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @w1)=0 )
select @AW2=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @w2)=0 )
select @AW3=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @w3)=0 )
select @AW4=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @w4)=0 )
select @AW5=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @w5)=0 )
select @ATH1=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @th1)=0 )
select @ATH2=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @th2)=0 )
select @ATH3=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @th3)=0 )
select @ATH4=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @th4)=0 )
select @ATH5=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @th5)=0 )
select @AF1=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @f1)=0 )
select @AF2=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @f2)=0 )
select @AF3=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @f3)=0 )
select @AF4=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @f4)=0 )
select @AF5=count(distinct(student_id))  FROM attendance A where attendence_status=1 and A.session_id in(select session_id from session where program_id=@program_id and datediff(dd,cast(convert(char(10),session_start_time ,101) as datetime) , @f5)=0 )
SELECT convert(char(7),@M1,6) AS 'M1',convert(char(7),@M2,6) AS 'M2',convert(char(7),@M3,6) AS 'M3',convert(char(7),@M4,6) AS 'M4',convert(char(7),@M5,6) AS 'M5',
convert(char(7),@T1,6) AS 'T1',convert(char(7),@T2,6) AS 'T2',convert(char(7),@T3,6) AS 'T3',convert(char(7),@T4,6) AS 'T4',convert(char(7),@T5,6) AS 'T5',
convert(char(7),@W1,6) AS 'W1',convert(char(7),@W2,6) AS 'W2',convert(char(7),@W3,6) AS 'W3',convert(char(7),@W4,6) AS 'W4',convert(char(7),@W5,6) AS 'W5',
convert(char(7),@TH1,6) AS 'TH1',convert(char(7),@TH2,6) AS 'TH2',convert(char(7),@TH3,6) AS 'TH3',convert(char(7),@TH4,6) AS 'TH4',convert(char(7),@TH5,6) AS 'TH5',
convert(char(7),@F1,6) AS 'F1',convert(char(7),@F2,6) AS 'F2',convert(char(7),@F3,6) AS 'F3',convert(char(7),@F4,6) AS 'F4',convert(char(7),@F5,6) AS 'F5',
@AM1 AS 'AM1',@AM2 AS 'AM2',@AM3 AS 'AM3',@AM4 AS 'AM4',@AM5 AS 'AM5',
@AT1 AS 'AT1',@AT2 AS 'AT2',@AT3 AS 'AT3',@AT4 AS 'AT4',@AT5 AS 'AT5',
@AW1 AS 'AW1',@AW2 AS 'AW2',@AW3 AS 'AW3',@AW4 AS 'AW4',@AW5 AS 'AW5',
@ATH1 AS 'ATH1',@ATH2 AS 'ATH2',@ATH3 AS 'ATH3',@ATH4 AS 'ATH4',@ATH5 AS 'ATH5',
@AF1 AS 'AF1',@AF2 AS 'AF2',@AF3 AS 'AF3',@AF4 AS 'AF4',@AF5 AS 'AF5'
END

GO

-Sudha





thanx...,

SSM
Post #163851
Posted Friday, February 25, 2005 7:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 26, 2008 6:13 AM
Points: 72, Visits: 4

See if the following function does the trick for you

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FloatingDate]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FloatingDate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Holiday_List]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[Holiday_List]
GO

---------------------------------------------------------------------------------
-- Function to return the X occurence of a particular weekday with a month/year
---------------------------------------------------------------------------------
CREATE FUNCTION dbo.FloatingDate(@Occur INT,@WeekDay INT,@Month INT,@Year INT)
RETURNS
    SMALLDATETIME
AS
BEGIN
    DECLARE @Result        SMALLDATETIME
    DECLARE @StartDate    SMALLDATETIME
    DECLARE @DayOfWeek    INT

    -- Get Starting date, which is first day of the month
    SET @StartDate = CONVERT(SmallDateTime,CAST(@Month AS VARCHAR(2))+'/1/'+CAST(@Year AS VARCHAR(4)))
    SET @DayOfWeek = DatePart(dw,@StartDate)                            -- Falls on what day?
    IF @DayOfWeek < @weekDay
        SET @StartDate = DateAdd(d,@weekDay-@DayOfWeek,@StartDate)        -- Adjust to requested day of week
    IF @DayOfWeek > @weekDay
        SET @StartDate = DateAdd(d,@DayOfWeek-@weekDay,@StartDate)        -- Adjust to requested day of week
    SET @Result    = DateAdd(wk,@Occur-1,@StartDate)                    -- Get X occurrence of day

    RETURN @Result
END

GO
---------------------------------------------------------------------------------
-- Returns a virtual table containing all holidays for a given year
---------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[Holiday_List] (@nYear INT)
RETURNS @Holidays TABLE
   (Holiday_name    VARCHAR(32),
    Holiday_date    SMALLDATETIME
)
AS
BEGIN
    -- Calculate Easter Sunday
    DECLARE @g         INT
    DECLARE @c         INT
    DECLARE @h         INT
    DECLARE @i         INT
    DECLARE @j         INT
    DECLARE @l         INT
    DECLARE @Month    INT
    DECLARE @Day    INT
    DECLARE @Easter SMALLDATETIME
    DECLARE @WorkDT SMALLDATETIME

    -- Bizarre Algorithm to determine Easter Sunday
    SET @g         = @nYear % 19
    SET @c         = @nYear / 100
    SET @h         = ((@c - (@c / 4) - ((8 * @c + 13) / 25) + (19 * @g) + 15) % 30)
    SET @i         = @h - ((@h / 28) * (1 - (@h /28) * (29 / (@h + 1)) * ((21 - @g) / 11)))
    SET @j         = ((@nYear + (@nYear / 4) + @i + 2 - @c + (@c / 4)) % 7)
    SET @l        = @i - @j
    SET @Month     = 3 + ((@l + 40) / 44)
    SET @Day     = @l + 28 - (31 * (@Month / 4))
    SET @Easter = CAST(@Month AS VARCHAR(2)) + '/' + CAST(@Day As VARCHAR(2)) + '/' + CAST(@nYear AS VARCHAR(4))

    ------------------------------------------------------------------------------------------------
    -- Add Easter Sunday to holiday list, and get holidays based around Easter
        INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Easter',@Easter)
    -- Good Friday
        INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Good Friday',DateAdd(d,-2,@Easter))
    -- Palm Sunday
        INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Palm Sunday',DateAdd(ww,-1,@Easter))
    -------------------------------------------------------------------------------------------------

    -- Fixed date holidays are loaded next
        INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('New Year''s Day',CONVERT(SmallDateTime,'1/1/'+CAST(@nYear AS VARCHAR(4))))
        INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Groundhog Day',CONVERT(SmallDateTime,'2/2/'+CAST(@nYear AS VARCHAR(4))))
        INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Lincoln''s Birthday',CONVERT(SmallDateTime,'2/12/'+CAST(@nYear AS VARCHAR(4))))
        INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Valentines Day',CONVERT(SmallDateTime,'2/14/'+CAST(@nYear AS VARCHAR(4))))
        INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('St. Patrick''s Day',CONVERT(SmallDateTime,'3/17/'+CAST(@nYear AS VARCHAR(4))))
        INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('April Fools Day',CONVERT(SmallDateTime,'4/1/'+CAST(@nYear AS VARCHAR(4))))
        INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Flag Day',CONVERT(SmallDateTime,'6/14/'+CAST(@nYear AS VARCHAR(4))))
        IF @nYear >=1776 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Independence Day',CONVERT(SmallDateTime,'7/4/'+CAST(@nYear AS VARCHAR(4))))
        IF @nYear >=1958 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Boss''s Day',CONVERT(SmallDateTime,'10/16/'+CAST(@nYear AS VARCHAR(4))))
        INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Halloween',CONVERT(SmallDateTime,'10/31/'+CAST(@nYear AS VARCHAR(4))))
        INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Christmas',CONVERT(SmallDateTime,'12/25/'+CAST(@nYear AS VARCHAR(4))))
        IF @nYear >=1966        INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Kwanzaa',CONVERT(SmallDateTime,'12/26/'+CAST(@nYear AS VARCHAR(4))))

    -- Holidays that full on the same day of the week (based on the year they were officially established)
        IF @nYear>=1983 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Martin Luther King Day',dbo.FloatingDate(3,2,1,@nYear))            -- 3rd Monday in January
        IF @nYear>=1993 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Take your Daughter to Work Day',dbo.FloatingDate(4,5,4,@nYear))    -- 4th Thursday in April
        IF @nYear>=1908 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Mothers Day',dbo.FloatingDate(2,1,5,@nYear))                        -- 2nd Sunday in May
        IF @nYear>=1950 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Armed Forces Day',dbo.FloatingDate(3,7,5,@nYear))                -- 3rd Saturday in May
        IF @nYear>=1910 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Fathers Day',dbo.FloatingDate(2,1,6,@nYear))                        -- 2nd Sundy in June
        IF @nYear>=1894 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Labor Day',dbo.FloatingDate(1,2,9,@nYear))                        -- 1st Monday in September
        IF @nYear>=1941 INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Thanksgiving',dbo.FloatingDate(4,5,11,@nYear))                    -- 4th Thursday in November

    ---------------------------------------------------------------------------------------
    -- Federal holidays that only come every 4 years
    ---------------------------------------------------------------------------------------
    IF (@nYear >= 1937) AND ((@nYear-1937) % 4)=0
    BEGIN
        SET @WorkDT = CONVERT(SmallDateTime,'01/20/'+CAST(@nYear AS VARCHAR(4)))    -- Get Inauguration day
        IF DatePart(dw,@WorkDT)=1 SET @WorkDt = DateAdd(d,1,@WorkDt)            -- Move to Monday if it falls on Sunday
        INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Inauguration Day',@WorkDt)
    END

    IF (@nYear < 1937) AND ((@nYear-1937) % 4)=0
    BEGIN
        -- Get Inauguration day for years prior to 1937
    SET @WorkDT = CONVERT(SmallDateTime,'03/04/'+CAST(@nYear AS VARCHAR(4)))
        INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Inauguration Day',@WorkDt)
    END
    ---------------------------------------------------------------------------------------
    -- Holidays that change based upon year
    ---------------------------------------------------------------------------------------
    IF @nYear >= 1971
        INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Presidents Day',dbo.FloatingDate(3,2,2,@nYear))    -- 3rd Monday in February
    IF @nYear < 1971
        INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Washington''s Birthday',CONVERT(SmallDateTime,'2/22/'+CAST(@nYear AS VARCHAR(4))))

    IF (@nYear >=1954)
        INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Veteran''s Day',CONVERT(SmallDateTime,'11/11/'+CAST(@nYear AS VARCHAR(4))))
    IF (@nYear >=1921 and @nYear < 1954)
        INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Armistice',CONVERT(SmallDateTime,'11/11/'+CAST(@nYear AS VARCHAR(4))))

    IF @nYear>=1971
        INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Columbus Day',dbo.FloatingDate(2,2,10,@nYear))    -- 2nd Monday in October
    IF @nYear>=1937 and @nYear <1971
        INSERT INTO @Holidays (Holiday_name,Holiday_date) VALUES ('Columbus Day',CONVERT(SmallDateTime,'10/12/'+CAST(@nYear AS VARCHAR(4))))


    RETURN
END

GO

SELECT * FROM dbo.Holiday_List(2005)
ORDER BY 2




Post #164053
Posted Saturday, May 21, 2005 7:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 10:32 AM
Points: 20, Visits: 19
The floating date function does not work correctly for 2005 (I didn't check other years)
if you modify the FloatingDate function as follows it will work:
Thanks to Karl Schmitt -Deerfield Bakery http://www.tek-tips.com/faqs.cfm?fid=5075
who also has a calendar generator

ALTER FUNCTION dbo.FloatingDate(@Occur INT,@WeekDay INT,@Month INT,@Year INT)
RETURNS
SMALLDATETIME
AS
BEGIN
DECLARE @Result SMALLDATETIME
DECLARE @StartDate SMALLDATETIME


-- Get Starting date, which is first day of the month
SET @StartDate = CONVERT(SmallDateTime,CAST(@Month AS VARCHAR(2))+'/1/'+CAST(@Year AS VARCHAR(4)))

SET @Result = cast(str(@Month)+'/'+ str((7+ @Weekday-datepart(dw,@StartDate))%7+1) +'/'+
str(@Year) AS datetime)+(@Occur-1)*7

RETURN @Result
END

GO






May you live to be 100 and me 100 but minus a day so I never know that nice people like you have passed away
Post #184346
Posted Monday, May 23, 2005 8:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 4, 2009 7:44 AM
Points: 263, Visits: 44
In case it's of use, I used this script to build the data dimension in a data warehouse. Bank Holidays are based on UK holidays though. The UDFs dbo.WorkingDay and the functions it uses for Easter & holiday dates are elsewhere on the site (I've posted them before) so you can find them by using Search or looking at my posting history.

IF EXISTS (SELECT [name] FROM sysobjects WHERE [name] = 'DX_Date' AND [type] = 'U')
DROP TABLE dbo.DX_Date
GO

CREATE TABLE dbo.DX_Date
( DateKey int IDENTITY(1,1) NOT NULL,
SQLDate smalldatetime NOT NULL,
DayOfWeek tinyint NOT NULL,
DayOfWeekName varchar(9) NOT NULL,
DayOfMonth tinyint NOT NULL,
DayOfYear smallint NOT NULL,
LastDayOfWeek_B bit NOT NULL,
LastDayOfMonth_B bit NOT NULL,
WeekEndingDate char(8) NOT NULL,
WeekNumber tinyint NOT NULL,
MonthName varchar(9) NOT NULL,
MonthNumber tinyint NOT NULL,
YearMonth char(6) NOT NULL,
Quarter tinyint NOT NULL,
YearQuarter char(6) NOT NULL,
Year smallint NOT NULL,
FinDayOfYear smallint NOT NULL,
FinWeekNumber tinyint NOT NULL,
FinPeriod tinyint NOT NULL,
FinYearPeriod char(6) NOT NULL,
FinQuarter tinyint NOT NULL,
FinYearQuarter char(6) NOT NULL,
FinYear smallint NOT NULL,
BankHoliday_B bit NOT NULL,
Weekday_B bit NOT NULL,
CONSTRAINT DX_Date_PK PRIMARY KEY (DateKey)
)
GO

-- Populate table

CREATE TABLE #numbers
( n int IDENTITY(1,1) PRIMARY KEY CLUSTERED )

WHILE ISNULL(SCOPE_IDENTITY(),0) <= 100000
BEGIN
INSERT #numbers DEFAULT VALUES
END
GO

IF @@DATEFIRST != 7
PRINT 'WARNING: @@DATEFIRST != 7'

DECLARE @dtStartDate datetime, @dtEndDate datetime, @iDays int

SET @dtStartDate = CONVERT(datetime, '19991231')
SET @dtEndDate = CONVERT(datetime, '20101231')

SET @iDays = DATEDIFF(dd, @dtStartDate, @dtEndDate)

INSERT DX_Date (SQLDate, DayOfWeek, DayOfWeekName, DayOfMonth, DayOfYear, LastDayOfWeek_B, LastDayOfMonth_B, WeekEndingDate,
WeekNumber, MonthName, MonthNumber, YearMonth, Quarter, YearQuarter, Year, FinDayOfYear, FinWeekNumber, FinPeriod,
FinYearPeriod, FinQuarter, FinYearQuarter, FinYear, BankHoliday_B, Weekday_B)

SELECT DATEADD(dd, n, @dtStartDate),
DATEPART(dw, DATEADD(dd, n, @dtStartDate)),
DATENAME(dw, DATEADD(dd, n, @dtStartDate)),
DATEPART(dd, DATEADD(dd, n, @dtStartDate)),
DATEPART(dy, DATEADD(dd, n, @dtStartDate)),
CASE WHEN DATEPART(dw, DATEADD(dd, n, @dtStartDate)) = 1 THEN 1 ELSE 0 END,
CASE WHEN DATEPART(mm, DATEADD(dd, n, @dtStartDate)) = DATEPART(mm, DATEADD(dd, n + 1, @dtStartDate)) THEN 0 ELSE 1 END,
CONVERT(varchar, DATEADD(dd, n + ((8 - DATEPART(dw, DATEADD(dd, n, @dtStartDate))) % 7), @dtStartDate), 112),
DATEPART(wk, DATEADD(dd, n, @dtStartDate)),
DATENAME(mm, DATEADD(dd, n, @dtStartDate)),
DATEPART(mm, DATEADD(dd, n, @dtStartDate)),
CONVERT(varchar, DATEPART(yy, DATEADD(dd, n, @dtStartDate))) +
CASE WHEN DATEPART(mm, DATEADD(dd, n, @dtStartDate)) < 10
THEN '0' + CONVERT(varchar, DATEPART(mm, DATEADD(dd, n, @dtStartDate)))
ELSE CONVERT(varchar, DATEPART(mm, DATEADD(dd, n, @dtStartDate))) END,
DATEPART(qq, DATEADD(dd, n, @dtStartDate)),
CONVERT(varchar, DATEPART(yy, DATEADD(dd, n, @dtStartDate))) + '0' + CONVERT(varchar, DATEPART(qq, DATEADD(dd, n, @dtStartDate))),
DATEPART(yy, DATEADD(dd, n, @dtStartDate)),
DATEDIFF(dd, CONVERT(datetime,
CONVERT(varchar, DATEPART(yy, DATEADD(dd, n, @dtStartDate)) -
CASE WHEN DATEPART(mm, DATEADD(dd, n, @dtStartDate)) <= 4 THEN 1 ELSE 0 END) + '0501'),
DATEADD(dd, n, @dtStartDate)) + 1,
DATEDIFF(ww, CONVERT(datetime,
CONVERT(varchar, DATEPART(yy, DATEADD(dd, n, @dtStartDate)) -
CASE WHEN DATEPART(mm, DATEADD(dd, n, @dtStartDate)) <= 4 THEN 1 ELSE 0 END) + '0501'),
DATEADD(dd, n, @dtStartDate)) + 1,
(DATEPART(mm, DATEADD(dd, n, @dtStartDate)) + 8) % 12,
'XXXXXX',
0,
'XXXXXX',
DATEPART(yy, DATEADD(dd, n, @dtStartDate)) -
CASE WHEN DATEPART(mm, DATEADD(dd, n, @dtStartDate)) <= 4 THEN 1 ELSE 0 END,
CASE WHEN eBIS_INTERACTIVE.dbo.WorkingDay(DATEADD(dd, n, @dtStartDate)) = 2 THEN 0 ELSE 1 END,
CASE WHEN eBIS_INTERACTIVE.dbo.WorkingDay(DATEADD(dd, n, @dtStartDate)) = 1 THEN 0 ELSE 1 END
FROM #numbers
WHERE n <= @iDays
GO

UPDATE DX_Date SET FinPeriod = 12 WHERE FinPeriod = 0
GO

UPDATE DX_Date
SET FinYearPeriod = CONVERT(varchar, FinYear) + CASE WHEN FinPeriod < 10 THEN '0' ELSE '' END + CONVERT(varchar, FinPeriod),
FinQuarter = CASE WHEN FinPeriod IN (1,2,3) THEN 1
WHEN FinPeriod IN (4,5,6) THEN 2
WHEN FinPeriod IN (7,8,9) THEN 3
WHEN FinPeriod IN (10,11,12) THEN 4 END
GO

UPDATE DX_Date
SET FinYearQuarter = CONVERT(varchar, FinYear) + '0' + CONVERT(varchar, FinQuarter)
GO

UPDATE DX_Date
SET FinWeekNumber = 1
WHERE FinWeekNumber = 53
GO

-- Create index

CREATE NONCLUSTERED INDEX DX_Date_IX1
ON DX_Date (SQLDate)
WITH FILLFACTOR = 100
GO

DROP TABLE #numbers
GO
Post #184519
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse