Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1028
  • Last Modified:

How to populate the Week Number inside the Date Dimension?

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
DBADS
Asked:
DBADS
  • 4
  • 3
  • 2
2 Solutions
 
Christopher GordonSenior Developer AnalystCommented:
SELECT DATEPART(week, @thedate)
0
 
_DJCommented:
SELECT DATEPART(WEEK, GETDATE())
OR
SELECT DATEPART(wk, GETDATE())
OR
SELECT DATEPART(ww, GETDATE())
0
 
DBADSAuthor Commented:
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
Christopher GordonSenior Developer AnalystCommented:
What about something like this...

SELECT case DATEPART(WEEK, GETDATE())      
      when 1 then 53
      else DATEPART(WEEK, GETDATE()) + 1
end
0
 
Christopher GordonSenior Developer AnalystCommented:
Actually you'd want this...

SELECT case DATEPART(WEEK, GETDATE())      
      when 1 then 53
      else DATEPART(WEEK, GETDATE()) - 1
end
0
 
DBADSAuthor Commented:
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
 
DBADSAuthor Commented:
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
 
Christopher GordonSenior Developer AnalystCommented:
0
 
_DJCommented:
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now