Table for OLAP date
--------------------
How It Works:
GetLastFridayOfMonthForYearRange.sql
This query populates a table called FinancialMonths with the date of every last Friday in a 12 month period based on financial year range (startdate, end date) entered.
Last Friday is found as follows: the query check when you start a new month (1/?/????) (@currentMonth <> month(@StartDate) it then takes 7 days off the current date (@startDate) and loops backwards until it finds the first Friday ( last Friday of prev month) and inserts into new table
DECLARE @StartDate as datetime
DECLARE @endDate as datetime
DECLARE @currentMonth as integer
SET @StartDate = '2010-07-01'
SET @EndDate = '2011-06-30'
DECLARE @lastFridayOfPrevMonthDate as datetime
DECLARE @getLastFridayDateCounter as datetime
SET @currentMonth = month(@StartDate)
seT @lastFridayOfPrevMonthDate = @StartDate
WHILE @startDate < @endDate BEGIN --GET LAST FRIDAY CODE IF @currentMonth <> month(@StartDate)
BEGIN
SET @getLastFridayDateCounter = dateadd(dd,-1,@StartDate)
WHILE @getLastFridayDateCounter < @StartDate BEGIN IF UPPER(DATENAME(WEEKDAY,@getLastFridayDateCounter)) = 'FRIDAY' BEGIN SET @lastFridayOfPrevMonthDate = @getLastFridayDateCounter --END LOOP SET @getLastFridayDateCounter = @StartDate + 1 END ELSE BEGIN SET @getLastFridayDateCounter = @getLastFridayDateCounter - 1 END SET @currentMonth = month(@StartDate) END --insert into FinancialMonth Select @lastFridayOfPrevMonthDate END SET @startDate = @startDate +1 END --last month end date is not a last friday insert on its own --insert into FinancialMonth Select @endDate ------------------------------------------------------------------------------------ Olap Solution.sql A Record needs to be inserted on a per day basis in Calendar table. The query loops per day from the startdate of given financial year to the end date. For each day is checks if this date is a last Friday (from table above previously populated) and upates set of variables relating.Similiarly every day and weekly variables are updated. DECLARE @StartYearDate as datetime DECLARE @EndYearDate as datetime DECLARE @quarterStartDate as datetime DECLARE @quarterCount as integer DECLARE @day_of_year as integer DECLARE @currentMonth as integer DECLARE @lastFridayOfPrevMonthDate as datetime DECLARE @getLastFridayDateCounter as datetime DECLARE @monthQuarterCount AS INTEGER Declare @monthStartDate as datetime Declare @weekDate as datetime declare @weekNo as integer Declare @dayOfQuarter as integer Declare @dayOfMonth as integer Declare @weekOfYear as integer Declare @monthOfYear as integer Declare @monthOfQuarter as integer Declare @quarterOfYear as integer Declare @financialYear as varchar(20) -------------------------------------------------------------------------------- --update these 3 variable prior to calendar insert on a per fin year basis ----- -------------------------------------------------------------------------------- SEt @financialYear = '2010-2011' SET @StartYearDate = '2010-07-01' SET @EndYearDate = '2011-06-30' --delete from tblbsbcalendar_Marguerite -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SET @quarterCount = 1 SET @day_of_year = 0 SET @currentMonth = month(@StartYearDate) SET @monthQuarterCount = 1 seT @lastFridayOfPrevMonthDate = @StartYearDate SET @quarterStartDate = @StartYearDate SET @weekDate= @StartYearDate SET @weekNo = 1 set @dayOfQuarter = 0 set @dayOfMonth = 0 set @weekOfYear = 1 set @monthOfYear = 1 set @monthOfQuarter = 1 set @quarterOfYear = 1 WHILE @StartYearDate <= @endYearDate BEGIN --ALWAYS UPDATE THESE COUNTERS SET @day_of_year = @day_of_year + 1 SEt @dayOfQuarter = @dayOfQuarter + 1 set @dayOfMonth = @dayOfMonth + 1 --every financial month update variables IF Exists(Select LastFriday from FinancialMonth where LastFriday = @StartYearDate-1) BEGIN SELECT @lastFridayOfPrevMonthDate = LastFriday +1 from FinancialMonth where LastFriday = @StartYearDate-1 SET @dayOfMonth = 1 Set @monthOfYear = @monthOfYear + 1 SEt @monthOfQuarter =@monthOfQuarter + 1 IF @currentMonth = 12 SET @currentMonth = 1 ELSE SET @currentMonth = @currentMonth + 1 -- UPDATE QUARTER COUNT IF NECESSARY WHEN MONTH CHANGES SET @monthQuarterCount = @monthQuarterCount + 1 IF @monthQuarterCount > 3
BEGIN
Set @quarterOfYear = @quarterOfYear + 1
SET @monthQuarterCount = 1
SEt @dayOfQuarter = 1
SEt @monthOfQuarter = 1
SET @quarterStartDate = @lastFridayOfPrevMonthDate + 1
SET @quarterCount = @quarterCount + 1
END
END
--every week updates
IF UPPER(DATENAME(WEEKDAY,@StartYearDate)) = 'SATURDAY'
BEGIN
set @weekDate= @StartYearDate
SET @weekNo = @weekNo + 1
Set @weekOfYear = @weekOfYear + 1
END
-- INSERT CODE---------------------------------------------------------------------------------------------------------------------------
insert into tblcalendar_table
SELECT @StartYearDate as pk_date,
DATENAME(weekday, @StartYearDate) +', ' + DATENAME(MM, @StartYearDate) + ' ' + DATENAME(DD, @StartYearDate) + ' ' + DATENAME(YY, @StartYearDate) as Date_Name,
cast(cast(year(@StartYearDate) as varchar) + '-01' + '-01' as datetime) as [Year],
'Calendar ' + @financialYear as Year_Name,
@quarterStartDate as Quarter,
'Quartor ' + cast(@quarterCount as varchar) + ', ' + @financialYear as Quarter_Name,
@lastFridayOfPrevMonthDate as [Month],
case @currentMonth
When 1 then 'January'
When 2 then 'February'
When 3 then 'March'
When 4 then 'April'
When 5 then 'May'
When 6 then 'June'
When 7 then 'July'
When 8 then 'August'
When 9 then 'September'
When 10 then 'October'
When 11 then 'November'
When 12 then 'December'
END + ' ' + @financialYear,
@weekDate as week,
'Week ' + cast(@weekNo as varchar) + ', ' + @financialYear as week_name,
@day_of_year as day_of_year,
'Day ' + cast(@day_of_year as varchar) as day_of_year_name,
@dayOfQuarter as Day_of_Quarter,
'Day ' + cast(@dayOfQuarter as varchar) as day_of_quarter_name,
@dayOfMonth as day_of_month,
'Day ' + cast(@dayOfMonth as varchar) as day_of_month_name,
DATEPART(weekday,@StartYearDate) as day_of_week,
'Day' + cast(DATEPART(weekday,@StartYearDate) as varchar) as day_of_week_name,
@weekOfYear as day_of_year,
'Week ' + cast(@weekOfYear as varchar) as day_of_year_name,
@monthOfYear,
'Month ' + cast(@monthOfYear as varchar),
@monthOfQuarter,
'Month ' + Cast(@monthOfQuarter as varchar),
@quarterOfYear,
'Quarter ' + cast(@quarterOfYear as varchar)
SET @StartYearDate = @StartYearDate +1
END
