Convert WeekNumber to Calendar Week

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Convert WeekNumber to Calendar Week

Author

Topic

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-12-03 : 10:53:43

I have a table:
SyStudent

Two Columns:
DateAdded
SyStudentID

SELECT DATENAME(weekday, DateAdded) AS AddDate, DATEPART(wk, DateAdded) AS WeekNumber,
DATEPART(year, DateAdded)AS YearNumber,
COUNT(SyStudentID) AS NewStudents
FROM dbo.SyStudent
GROUP BY
DATENAME(weekday, DateAdded), DATEPART(wk, DateAdded), DATEPART(year, DateAdded)
ORDER BY
YearNumber,
WeekNumber,
AddDate

The result is a list of new students added per day.

AddDate=Friday
WeekNumber=3
YearNumber=2007
NewStudents=15

Etc.

What I need to do is convert the WeekNumber into an actual corresponding Calendar time frame.

Are there any suggestions on how to accomplish this please.

Thank you.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-03 : 11:05:01

quote:

What I need to do is convert the WeekNumber into an actual corresponding Calendar time frame.

Can you explain in detail about your requirement.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-03 : 11:12:23

Your problem is to find the startdate of week 1 in the year.
you could do this by
declare @d datetime
select @d = '20100101'
while datepart(wk,@d) <> 2
select @d = @d + 1
select @d = @d - 14

then
select dateadd(wk,weekno,@d) should give the satrtdate for the week - add 7 for the enddate.

For multiple years I think you will have to build a table of the week start dates for each year.
Probably best to maintain this in a date table.
Datawarhouses have a dim_date which for each date holds the week start and end dates so is easy to join to - you might want to build one.
If you deal with dates it is usually very useful

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-12-03 : 11:33:12

My requirement is to have a view which shows the number of students added per day of the week to the table SyStudent.

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-12-03 : 11:34:59

nigelrivett,

Would I need to create a calendar table? I saw something in my research yesterday on creating a separate calender table.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-03 : 11:46:53

They are often useful.
If you are dealing with week numbers then you probably have quarters, months, week starts and ends, holidays, fiscal periods, ....
You really don't want to have to calculate them every time
For your immediate requirement I would start with something like

date
dayname
weeknumber
weekstartdate
weekenddate
monthnumber
monthname
monthstartdate
monthenddate
quarternumber
quarterstartdate
quarterenddate
IsWeekend
IsBankHoliday
year

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources