| Author |
 Topic  |
|
|
Anand.A
Posting Yak Master
India
109 Posts |
 Posted - 04/23/2012 : 05:36:09
|
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
anand |
Edited by - Anand.A on 04/23/2012 05:37:54
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 04/23/2012 : 08:27:40
|
You may need to tweak this a bit as your actual Start dates are not clear from your example.
UPDATE DIM_DAY
SET
[FISCAL_DAY_OF_YEAR] =
DATEDIFF
(day,DATEADD(YEAR, DATEDIFF(MONTH, '19000401', TIME_DATE) / 12, '19000401')
,TIME_DATE+1
)
,[FISCAL_WEEK_NUMBER] = DATEDIFF
(week,DATEADD(YEAR, DATEDIFF(MONTH, '19000401', TIME_DATE) / 12, '19000401')
,TIME_DATE+7
)
Jim
Everyday I learn something that somebody else already knew |
 |
|
| |
Topic |
|
|
|
T sql for dim_Day
