DayOfWeek Query

Question

Hi All,

I got request to generate report based on day of week.

eg: Mon Tue Wed Thu Fri Sat Sun

SELECT
DateKey,
CalendarDate,
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 :)

Answer 1

Hi

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.

Cheers

---

MCP, MCTS, MCITP

Answer 2

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

Answer 3

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

http://www.sqlservercentral.com/scripts/Data+Warehousing/65762/

Answer 4

Thank you for the info. Will take a look on it