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
WHILE ( @StartYear <= @currentYear + 3)
BEGIN
INSERT @ListOfYears SELECT @StartYear
SET @StartYear = @StartYear + 1
END
SELECT * FROM @ListOfYears ORDER BY Years ASC
