February 26, 2003 at 10:55 am
Dear all
I would value comments and constructive comments on a solution I have cribbed/cobbled and customed for date management:
CREATE TABLE [dbo].[Calendar] (
[CalendarID] [int] IDENTITY (1, 1) NOT NULL ,
[FullDate] [datetime] NOT NULL ,
[DayNameOfWeek] [varchar] (9) COLLATE Latin1_General_CI_AS NOT NULL ,
[DayNumberOfWeek] [tinyint] NOT NULL ,
[DayNumberOfMonth] [tinyint] NOT NULL ,
[DayNumberOfYear] [smallint] NOT NULL ,
[WeekNumberOfYear] [smallint] NOT NULL ,
[MonthFullName] [varchar] (9) COLLATE Latin1_General_CI_AS NOT NULL ,
[MonthShortName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[CalendarQuarter] [tinyint] NOT NULL ,
[CalendarYear] [smallint] NOT NULL ,
[IsHoliday] [bit] NOT NULL ,
[IsMonthEnd] [bit] NOT NULL ,
[IsBMonthEnd] [bit] NULL ,
[IsWeekDay] [bit] NOT NULL ,
[IsBWeekDay] [bit] NULL ,
[IsArchive] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Calendar] WITH NOCHECK ADD
CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED
(
[CalendarID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Calendar] WITH NOCHECK ADD
CONSTRAINT [DF_Calendar_IsHoliday] DEFAULT (0) FOR [IsHoliday],
CONSTRAINT [DF_Calendar_IsMonthEnd] DEFAULT (0) FOR [IsMonthEnd],
CONSTRAINT [DF_Calendar_IsBMonthEnd] DEFAULT (0) FOR [IsBMonthEnd],
CONSTRAINT [DF_Calendar_IsWorkDay] DEFAULT (0) FOR [IsWeekDay],
CONSTRAINT [DF_Calendar_IsWeekDay1] DEFAULT (0) FOR [IsBWeekDay],
CONSTRAINT [DF_Calendar_IsArchive] DEFAULT (0) FOR [IsArchive]
GO
CREATE TABLE [dbo].[HolDates] (
[HolDatesID] [int] IDENTITY (1, 1) NOT NULL ,
[HolidayID] [int] NULL ,
[CalendarID] [int] NULL ,
[IsArchive] [bit] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Holidays] (
[HolidayID] [int] IDENTITY (1, 1) NOT NULL ,
[HNameRFValID] [int] NULL ,
[IsArchive] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[HolDates] WITH NOCHECK ADD
CONSTRAINT [PK_Holidays] PRIMARY KEY CLUSTERED
(
[HolDatesID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Holidays] WITH NOCHECK ADD
CONSTRAINT [PK_Holidays_1] PRIMARY KEY CLUSTERED
(
[HolidayID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[HolDates] WITH NOCHECK ADD
CONSTRAINT [DF_HolDates_IsArchive] DEFAULT (0) FOR [IsArchive]
GO
ALTER TABLE [dbo].[Holidays] WITH NOCHECK ADD
CONSTRAINT [DF_Holidays_IsArchive] DEFAULT (0) FOR [IsArchive]
GO
ALTER TABLE [dbo].[HolDates] ADD
CONSTRAINT [FK_HolDates_Calendar] FOREIGN KEY
(
[CalendarID]
) REFERENCES [dbo].[Calendar] (
[CalendarID]
),
CONSTRAINT [FK_HolDates_Holidays] FOREIGN KEY
(
[HolidayID]
) REFERENCES [dbo].[Holidays] (
[HolidayID]
)
GO
ALTER TABLE [dbo].[Holidays] ADD
CONSTRAINT [FK_Holidays_RefValues] FOREIGN KEY
(
[HNameRFValID]
) REFERENCES [dbo].[RefValues] (
[RFValID]
)
GO
/****** Object: Table [dbo].[RefValDomains] Script Date: 26/02/2003 17:40:39 ******/
CREATE TABLE [dbo].[RefValDomains] (
[RFValDomID] [int] IDENTITY (1, 1) NOT NULL ,
[Code] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Description] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[IsArchive] [bit] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[RefValues] Script Date: 26/02/2003 17:40:40 ******/
CREATE TABLE [dbo].[RefValues] (
[RFValID] [int] IDENTITY (1, 1) NOT NULL ,
[RFValDomID] [int] NOT NULL ,
[Code] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[Description] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[IsArchive] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RefValDomains] WITH NOCHECK ADD
CONSTRAINT [PK_RefValDomains] PRIMARY KEY CLUSTERED
(
[RFValDomID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RefValues] WITH NOCHECK ADD
CONSTRAINT [PK_RefValues] PRIMARY KEY CLUSTERED
(
[RFValID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RefValDomains] WITH NOCHECK ADD
CONSTRAINT [DF01__RefValDomains__IsArchive] DEFAULT (0) FOR [IsArchive]
GO
ALTER TABLE [dbo].[RefValues] WITH NOCHECK ADD
CONSTRAINT [DF01__RefValues__IsArchive] DEFAULT (0) FOR [IsArchive]
GO
ALTER TABLE [dbo].[RefValues] ADD
CONSTRAINT [FK_RefValues_RefValDomains] FOREIGN KEY
(
[RFValDomID]
) REFERENCES [dbo].[RefValDomains] (
[RFValDomID]
)
GO
CREATE PROCEDURE sCalendarBuild
@StartDate DATETIME,
@EndDate DATETIME
AS
SET NOCOUNT ON
DECLARE @FullDate DATETIME,
@DayNumberOfMonth INTEGER,
@DayNumberOfYear INTEGER,
@DayNameOfWeek VARCHAR(9),
@WeekNumberOfYear INTEGER,
@MonthFullName VARCHAR(9),
@MonthNumberOfYear INTEGER,
@CalendarYear INTEGER,
@CalendarQuarter INTEGER,
@DayNumberOfWeek INTEGER,
@MonthShortName VARCHAR (3)
SET DATEFIRST 7
WHILE @StartDate < @EndDate
BEGIN
SELECT
@FullDate = @StartDate,
@DayNumberOfMonth = DATEPART(DD, @StartDate),
@DayNumberOfYear = DATEPART(DY, @StartDate),
@DayNameOfWeek = UPPER(DATENAME(weekday, @StartDate)),
@WeekNumberOfYear = DATEPART(WK, @StartDate),
@MonthFullName = UPPER(DATENAME(month, @StartDate)),
@MonthNumberOfYear = DATEPART(M, @StartDate),
@CalendarYear = DATEPART(YYYY, @StartDate),
@CalendarQuarter = DATEPART(QQ, @StartDate),
@DayNumberOfWeek = DATEPART(DW, @StartDate),
@MonthShortName = UPPER(DATENAME(M, @StartDate))
INSERT INTO Calendar (
FullDate
,DayNameOfWeek
,DayNumberOfWeek
,DayNumberOfMonth
,DayNumberOfYear
,WeekNumberOfYear
,MonthFullName
,MonthShortName
,CalendarYear
,CalendarQuarter
)
SELECT
@FullDate
,@DayNameOfWeek
,@DayNumberOfWeek
,@DayNumberOfMonth
,@DayNumberOfYear
,@WeekNumberOfYear
,@MonthFullName
,@MonthShortName
,@CalendarYear
,@CalendarQuarter
SELECT @StartDate = @StartDate + 1
END
UPDATE Calendar SET IsMonthEnd = 1
FROM Calendar C1
WHERE FullDate = (
SELECT TOP 1 C2.FullDate
FROM Calendar C2
WHERE
C2.MonthShortName = C1.MonthShortName
AND
C2.CalendarYear = C1.CalendarYear
ORDER BY DayNumberOfMonth DESC
)
UPDATE Calendar SET IsWeekDay = 1
FROM Calendar C1
WHERE FullDate = (
SELECT TOP 1 C2.FullDate
FROM Calendar C2
WHERE
C2.MonthShortName = C1.MonthShortName
AND
C2.DayNumberOfMonth = C1.DayNumberOfMonth
AND
DayNameOfWeek not in ('Saturday','Sunday')
AND
IsHoliday <> 1
AND
C2.CalendarYear = C1.CalendarYear
ORDER BY DayNumberOfMonth DESC
)
-- Set IsBWeekday = 1 where DayNameOfWeek <> Sunday
UPDATE Calendar SET IsBWeekDay = 1
FROM Calendar C1
WHERE FullDate = (
SELECT TOP 1 C2.FullDate
FROM Calendar C2
WHERE
C2.MonthShortName = C1.MonthShortName
AND
C2.DayNumberOfMonth = C1.DayNumberOfMonth
AND
DayNameOfWeek not in ('Sunday')
AND
IsHoliday <> 1
AND
C2.CalendarYear = C1.CalendarYear
ORDER BY DayNumberOfMonth DESC
)
UPDATE Calendar SET IsBMonthEnd = 1
FROM Calendar C1
WHERE FullDate = (
SELECT TOP 1 C2.FullDate
FROM Calendar C2
WHERE
C2.MonthShortName = C1.MonthShortName
AND
C2.CalendarYear = C1.CalendarYear
AND
C2.IsBWeekDay = 1
ORDER BY DayNumberOfMonth DESC
)
GO
Best regards,
February 27, 2003 at 2:24 am
Dear all
I am intending to use the above as opposed to calculating business days et al as I believe this is a simple solution for reporting dates etc.
This being so I would really appreciate your comments on the usefullness of the above - this will be going on to form part of a generic SQL management system which I intend to make freely available.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply