Dynamic Calendar Control

Data Source ---- EMG_MSCRM

--*************************************************

/*DECLARE @FromYear INT =2010, @FromMonth INT = 5

DECLARE @ToYear INT =2010, @ToMonth INT = 6*/

--*************************************************

DECLARE @StartDate DATETIME, @EndDate DATETIME

DECLARE @Date DATETime

SET @Date =
CONVERT(DATETIME,
CONVERT(VARCHAR(2),@FromMonth)
+
'/1/'
+
CONVERT(VARCHAR(10),@FromYear),101)

SELECT @StartDate =
DATEADD(MONTH,DATEDIFF(MONTH, 0, @Date),0)

SET @Date =
CONVERT(DATETIME,
CONVERT(VARCHAR(2),@ToMonth)
+
'/1/'
+
CONVERT(VARCHAR(10),@FromYear),101)

SELECT @EndDate =
DATEADD(MONTH,DATEDIFF(MONTH,
-1, @Date),0)

if(@StartDate < @EndDate)

begin

DECLARE @TempDimDate TABLE
(
[Date] DATETime

,DayNumberOfWeek TINYINT

,DayNameOfWeek VARCHAR(10)

,DayNumberOfMonth TINYINT

,DayNumberOfYear SMALLINT

,WeekNumberOfYear TINYINT

,[MonthName] VARCHAR(10)

,MonthNumberOfYear TINYINT

,CalendarQuarter TINYINT

,CalendarYear CHAR(4)

,CalendarSemester TINYINT
);

WITH CalculatedDate([Date])
AS
(

SELECT
@StartDate AS [Date]

UNION
ALL

SELECT

([Date] + 1)

FROM
CalculatedDate

WHERE

(([Date] + 1)
< @EndDate)
)
INSERT
INTO @TempDimDate

SELECT
[Date] AS [Date]

,DATEPART(DW,[Date])
AS DayNumberOfWeek

,DATENAME(DW,[Date])
AS DayNameOfWeek

,DAY(Date)
AS DayNumberOfMonth

,DATEPART(DY,[Date])
AS DayNumberOfYear

,DATEPART(WK,[Date])
AS WeekNumberOfYear

,DATENAME(MM,[Date])
AS [MonthName]

,Month([Date])
AS MonthNumberOfYear

,DATENAME(QUARTER, [Date])
AS CalendarQuarter

,YEAR([Date])
AS CalendarYear

,CASE
WHEN
DATEPART(mm, [Date])
> 6

THEN 2

ELSE 1

END
AS CalendarSemester

FROM CalculatedDate

OPTION (MAXRECURSION 0)
SELECT DimDate.Date,
DimDate.WeekNumberOfYear,
DimDate.DayNumberOfWeek,
DimDate.DayNameOfWeek,
DimDate.DayNumberOfMonth,
DimDate.CalendarYear,
DimDate.MonthNumberOfYear
FROM @TempDimDate AS DimDate
END

Data Source ---- ListOfYears

DECLARE @ListOfYears TABLE (Years INT)
DECLARE @currentYear INT
SET @currentYear =
DATEPART(YEAR,GETDATE())
DECLARE @StartYear INT
SET @StartYear = 2009