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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy