I have a week number and need it to be < 52

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

I have a week number and need it to be < 52

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2012-01-11 : 17:14:09

I have a view that returns a year and week number. in 2011 it was great because the week number was 1-52. But in 2012, the first week is returning as 53. My user wants it 1-52. I can do a simple case statement to just subtract 52 from any number larger than 53, but 2013 will come, and so on, and I need something more dynamic. Help! Here is the query:


SELECT 
cw.subID, WeekNum = DATEPART(wk,cw.rptTime),YYYY = datepart(yy,cw.rptTime),WeekScore = cw.compiancePCT
FROM Pol..rpt_DailyPercentComplete cw 
INNER JOIN (SELECT yyyy = datepart(yy,rptTime), MaxDayWeek=MAX(datepart(dw,rptTime)) from Pol..rpt_DailyPercentComplete group by datepart(wk,rptTime), datepart(yy,rptTime)) x ON x.WeekNum=DATEPART(wk,cw.rptTime) and x.yyyy = datepart(yy,cw.rptTime) and x.MaxDayWeek = datepart(dw,cw.rptTime)

Craig Greenwood

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-11 : 17:19:25

I don't understand. When I run this: select DATEPART(wk,'01/01/2012') I get back 1 and not 53.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog