February 25, 2003 at 2:32 am
Morning all
I have a Calendar table which I use instead of datepart calculations as I need to know the type of day, not just the dayname etc.
Therefore I populate a permanent table and split each day into it's components - determining the weekday & monthend is fine but I can't figure out how to define the Business monthend....ARGH!
The problem I have is that if a monthend is on a holiday I need to obtain the previous business day which, if this is a Monday, means I need to go back 2 days and set the isBMonthEnd flag to 1.
Probably an easy solution is out there but I can't seem to find it....:(
Any help would be much appreciated
February 25, 2003 at 5:08 am
Possible solution under the following preconditions :
- DayOfWeek is 1 for Monday
- Holiday is 1 for a holiday, 0 for not (weekend is also holiday)
- @Month is the month you search for (ommitted year for clarity)
SELECT
CASE holiday
WHEN 0 THEN Date
ELSE CASE DayOfWeek WHEN 1 THEN
DATEADD('d', Date, -3)
WHEN 7 THEN DATEADD('d', Date, -2)
ELSE DATEADD('d', Date, -1)
END
END
FROM Calendar
WHERE Month = @Month
AND DayOfMonth = (SELECT MAX(DayOfMonth)
FROM Calendar
WHERE Month = @Month)
Edited by - NPeeters on 02/25/2003 05:10:55 AM
Edited by - NPeeters on 02/25/2003 05:12:31 AM
February 25, 2003 at 5:51 am
Nice solution! However my schema is rather different - to allow for different date states:
/****** Object: Table [dbo].[Calendar] Script Date: 25/02/2003 12:33:33 ******/
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_IsWeekDay] DEFAULT (0) FOR [IsWeekDay],
CONSTRAINT [DF_Calendar_IsBWeekDay1] DEFAULT (0) FOR [IsBWeekDay],
CONSTRAINT [DF_Calendar_IsArchive] DEFAULT (0) FOR [IsArchive]
GO
I am using the Holiday column to tie through to a Holiday table
February 25, 2003 at 8:12 am
I believe my solution can be adjusted for your schema.
Replace Date with FullDate, Holiday with IsHoliday, DayOfWeek by DayNumberOfWeek, DayOfMonth by DayNumberOfMonth, Month by ShortMonthName and you'll be up and running.
However, an easier solution to get a list of all Business Month ends is below, provided you have IsHoliday, IsBWeekDay and IsMonthEnd set correctly.
SELECT *
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)
Edited by - NPeeters on 02/25/2003 08:15:43 AM
February 25, 2003 at 8:40 am
Thanks very much that worked just fine. Now when I build the calendar all I need to ensure is that the business week is correct which is easy enough.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply