T sql for dim_Day - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

T sql for dim_Day

Author

Topic

Anand.A
Posting Yak Master

109 Posts

Posted - 2012-04-23 : 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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-04-23 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources