Database Reference
In-Depth Information
, [MonthName]
, [Quarter]
, [QuarterName]
, [Year]
, [YearName]
)
VALUES (
--
[Date]
@DateInProcess
--
[DateName]
, Convert(varchar(50), @DateInProcess, 110)+', '
+ DateName( weekday, @DateInProcess )
--
[Month]
, Month( @DateInProcess )
--
[MonthName]
, Cast( Year(@DateInProcess) as nVarchar(4) )+' - '
+ DateName( month, @DateInProcess )
--
[Quarter]
, DateName( quarter, @DateInProcess )
--
[QuarterName]
, Cast( Year(@DateInProcess) as nVarchar(4) )+' - '
+ 'Q'+DateName( quarter, @DateInProcess )
--
[Year]
, Year(@DateInProcess)
--
[YearName]
, Cast( Year(@DateInProcess) as nVarchar(4) )
)
-- Add a day and loop again
SET @DateInProcess=DateAdd(d, 1, @DateInProcess)
END
-- Check the table SELECT Top 10 * FROM DimDates
In the next exercise, you create this date table using the code in Listing 5-10. Then, you create the foreign key
constraints from the DimTitles and FactSales tables.
eXercISe 5-3. create a Date DIMeNSION
In this exercise, you create a date dimension table in the Publication Industries data warehouse. You
can choose to use either the SQL code presented here, the table designer or the diagramming tools to
accomplish your goal. (In Chapter 7 you fill the table with data using the code in Listing 5-11.)
Tip:
The code files for this exercise, as well as all of the exercises throughout this topic, are available in the
downloadable book content.
create the DimDates table
You first task is to create a table to hold date dimension data.
1.
If it is not open already, open SQL Server Management Studio; see Exercise 5-1 for
more details.
