Solved

How to populate the Week Number inside the Date Dimension?

Posted on 2010-11-29
9
918 Views
Last Modified: 2012-08-13
Hello,

Does anyone have a script or a guide to load the week number (1-53) column in a date dimension
The week starts on Sunday and ends on Saturday.


Thank you
0
Comment
Question by:DBADS
  • 4
  • 3
  • 2
9 Comments
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 34234272
SELECT DATEPART(week, @thedate)
0
 
LVL 1

Expert Comment

by:_DJ
ID: 34236668
SELECT DATEPART(WEEK, GETDATE())
OR
SELECT DATEPART(wk, GETDATE())
OR
SELECT DATEPART(ww, GETDATE())
0
 

Author Comment

by:DBADS
ID: 34238757
Thanks guys for the reply
The problem is the results of the above queries is different than this http://2010-calendar.org/ 
The query is producing W+1 compared to the calendar in the link

Example: For day 2010-01-01:
The query is producing Week 1, while the link denotes Week 53 of the previous year

Please advise. This is urgent
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 34238814
What about something like this...

SELECT case DATEPART(WEEK, GETDATE())      
      when 1 then 53
      else DATEPART(WEEK, GETDATE()) + 1
end
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 34238819
Actually you'd want this...

SELECT case DATEPART(WEEK, GETDATE())      
      when 1 then 53
      else DATEPART(WEEK, GETDATE()) - 1
end
0
 

Author Comment

by:DBADS
ID: 34238992
Unfortunately, it didn't work
Applying your suggested query worked unless the year starts with a Sunday (example 2012)
In such case the first week of the year will be Week 53 instead of 1

I think the query to populate the dimension has to have the calendar week logic in order to cover all cases...
0
 

Author Comment

by:DBADS
ID: 34239079
Is there a way to query this logic:
"ISO 8601 defines the Week as always starting with Monday. The first week is the week which contains the first Thursday of the calendar year. This implies that it is the week which is mostly within the Calendar year and the week containing January 4th"
The only difference is that the starting day should be Sunday; instead of Monday.
0
 
LVL 14

Assisted Solution

by:Christopher Gordon
Christopher Gordon earned 175 total points
ID: 34239099
0
 
LVL 1

Accepted Solution

by:
_DJ earned 325 total points
ID: 34247239
DECLARE @DateTime DATETIME
SELECT @DateTime = GETDATE()

SELECT CASE DATEPART(WEEK, DATEADD(yy, DATEDIFF(yy, 0, @DateTime), 3))
            WHEN 1 THEN DATEPART(WEEK, @DateTime)
            ELSE CASE WHEN DATEPART(WEEK, @DateTime) = 1 THEN 53 ELSE DATEPART(WEEK, @DateTime) - 1 END END
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How can I get blanks instead of zero's to show up in my results? 2 30
Can Unique column have more than one Null? 8 48
all records from previous month 6 45
TSQL previous 5 26
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now