T SQL dim_day

hi i wrote a T sql for generating dim_Day

DECLARE @StartDate datetime, @EndDate datetime
-- Set StartDate and EndDate as per your requirement
SELECT @StartDate = '2008-01-01', @EndDate = '2015-12-31'

WHILE (@StartDate <= @EndDate )
BEGIN
INSERT INTO dbo.DIM_DAY
SELECT
CAST(CONVERT(varchar(8), @StartDate ,112) AS int) DATESK
, @StartDate AS TIME_DATE
,DATENAME(DD, @StartDate) DAY_NUMBER
, DATEPART(MM , @StartDate) MONTH_NUMBER
, DATEPART(YY , @StartDate) YEAR_NUMBER
,REPLACE(convert(varchar(7), @StartDate, 126),'-','') YEAR_MONTH

SET @StartDate = @StartDate +1
END
GO


now by using this procedure i want to update 2 columns in dim_day table
[FISCAL_DAY_OF_YEAR]
[FISCAL_WEEK_NUMBER]

[FISCAL_DAY_OF_YEAR] Means its start from 1st of march 2010 - is 1 and its end in 31-03-2011 is 365..
[FISCAL_WEEK_NUMBER] means its start from 1st of march 2010- is 1 and its end in 31-03-2011 is 52 or 53..

how to write update in this procedure

This should help.  More here: http://www.ipcdesigns.com/dim_date/dim_day_fiscal_sql.txt

and here: http://www.ipcdesigns.com/dim_date/

-- populate fiscal day number of year

update common.dim_date

set fiscal_day =

-- Assumes first year in dim_day begins January 1...and US Federal year begins October 1

-- are we in first year and a month less than the beginning of US Federal fiscal year?

-- yes: calculate fiscal day of year number from beginning of last day of previous year

-- no: calculate offset from 30-Sep of previous year

case

when year([day]) = (

select min(year([day]))

from common.dim_date

where [day] is not null

)

and month([day]) < @Wk_FiscalMonthBegin

then datediff( d

, cast(

cast(

(

select min(year([day]))-1

from common.dim_date

where [day] is not null

) as varchar(4)

-- Again, for the purists out there, if you must have symmetry...

-- uncomment the following line and comment out the 2nd line down

-- ) + '-09-30' as datetime

) + '-12-31' as datetime

)

, [day]) - @Wk_FiscalDayDiff

else datediff(d, cast(

cast(

case

when month([day]) < @Wk_FiscalMonthBegin

then year([day]) - 1

else year([day])

end as varchar(4)

) + '-' + --'09-30'

CAST(@Wk_FiscalMonthBegin - 1 AS VARCHAR(2))

+ '-' + --'31'

CAST(DAY(--'2008-05-31'

dbo.LastDayOfMonth(cast(

case

when month([day]) < @Wk_FiscalMonthBegin

then year([day]) - 1

else year([day])

end as varchar(4)

) + '-' +

cast((@Wk_FiscalMonthBegin - 1) as varchar(2))

+ '-01'

)

)

AS VARCHAR(2))

as datetime

)

, [day] )

end

from common.dim_date

where [day] is not null

and date_key > 0;

--and fiscal_day_num_of_year is null

-- populate fiscal week of year number

update common.dim_date

set fiscal_week = ((fiscal_day - 1) / 7) + 1

where fiscal_day is not null

and date_key > 0;