Thursday, August 09, 2012 8:52 AM
I got request to generate report based on day of week.
eg: Mon Tue Wed Thu Fri Sat Sun
DailyAggregate = DD.CalendarDate,
DayOfWeekAggregate = ?????????
WeeklyAggregate = DATEADD(DAY, (DD.WeekNumberInYear - 1)*7, CONVERT(nvarchar(4),DD.YearNumber) + '-1-1 00:00:00'),
MonthlyAggregate = DATEADD(MONTH, DD.MonthNumber - 1, CONVERT(nvarchar(4),DD.YearNumber) + '-1-1 00:00:00'),
QuarterlyAggregate = DATEADD(MONTH, (((DD.MonthNumber-1)/3) *3), CONVERT(nvarchar(4),DD.YearNumber) + '-1-1 00:00:00'),
YearlyAggregate = CONVERT(datetime, CONVERT(nvarchar(4),DD.YearNumber) + '-1-1 00:00:00')
FROM DateDim DD
If i would like to add new DayOfWeekAggregate... what is the query for it ?
I'm not SQL expert.... help pls :)
Thursday, August 09, 2012 11:22 AM
If you are going to design a data warehouse with a Date dimension, I suggest take a look at AdventureWorksDW, DimDate. It would be have very good design ideas for you.
Anyway, to have Days of a week using T-SQL, you can use "SELECT DATENAME(dw,'2012-8-9')" to retrieve day name.
MCP, MCTS, MCITP
- Proposed As Answer by Omar Sultan Friday, August 10, 2012 11:26 AM
Friday, August 10, 2012 7:55 PM
Thank you for the answer...
I'm not design a data warehouse but trying to retrieve data from System Center Service Manager Datawarehouse.
May i have the formula to populate information under Mon Tue until Sun for a month ? which is not in the existing query.
Any possible ? TQ
Saturday, August 11, 2012 2:20 AM
You might be better off creating a a static date table than calculating the entire date range the way that you have the the moment.no point running the query everytime when u can do a simple join on a static table and index it. there are quite a few scripts availble out there to create a date table.
here is an example with the script
- Marked As Answer by Eileen ZhaoMicrosoft Contingent Staff, Moderator Monday, August 27, 2012 2:50 AM
Monday, August 13, 2012 9:39 AMThank you for the info. Will take a look on it