Calendar Table - Week number of month
- by Saif Khan
I have a calendar table with data from year 2000 to 2012 (2012 wasn't intentional!). I just realize that I don't have the week number of month (e.g In January 1,2,3,4 February 1,2,3,4)
How do I go about calculating the week numbers in a month to fill this table?
Here is the table schema
CREATE TABLE [TCalendar] (
[TimeKey] [int] NOT NULL ,
[FullDateAlternateKey] [datetime] NOT NULL ,
[HolidayKey] [tinyint] NULL ,
[IsWeekDay] [tinyint] NULL ,
[DayNumberOfWeek] [tinyint] NULL ,
[EnglishDayNameOfWeek] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SpanishDayNameOfWeek] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FrenchDayNameOfWeek] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DayNumberOfMonth] [tinyint] NULL ,
[DayNumberOfYear] [smallint] NULL ,
[WeekNumberOfYear] [tinyint] NULL ,
[EnglishMonthName] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SpanishMonthName] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FrenchMonthName] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MonthNumberOfYear] [tinyint] NULL ,
[CalendarQuarter] [tinyint] NULL ,
[CalendarYear] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CalendarSemester] [tinyint] NULL ,
[FiscalQuarter] [tinyint] NULL ,
[FiscalYear] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FiscalSemester] [tinyint] NULL ,
[IsLastDayInMonth] [tinyint] NULL ,
CONSTRAINT [PK_TCalendar] PRIMARY KEY CLUSTERED
(
[TimeKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
