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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Shift Work Week to W-Tues

Author  Topic 

paulkem
Starting Member

28 Posts

Posted - 2013-09-24 : 12:25:48
Hello. I need to be able to group data based on not only the date, but also the "week of". However, the "week" is defined as Wed - Tues.

Basically, I think what I need is for the code to convert a date to the preceding Wednesday (not the Wed of last week). For example, 9/23/2013 would convert to 9/18/2013, but 9/27/2013 would convert to 9/25/2013.

I can find some code to find a date in the previous week, but nothing like this (so far).

I do have a Date table at my disposal that has Date, WeekStartSunday, DayOfWeek, etc.


Thanks,

PK

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-24 : 12:56:54
Group by this:
DATEADD(dd,-DATEDIFF(dd,2,YourDateColumn)%7,YourDateColumn)


If you have time portion remember to remove that like this:
DATEADD(dd,-DATEDIFF(dd,2,YourDateColumn)%7,CAST(YourDateColumn AS DATE))
Go to Top of Page

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-09-24 : 12:59:36
Look at: http://technet.microsoft.com/en-us/library/ms181598(v=sql.105).aspx

djj
Go to Top of Page

paulkem
Starting Member

28 Posts

Posted - 2013-09-24 : 13:51:26
James K: That appears to do the trick! Such simple code, yet I will have to ponder exactly how it works.

djj55: Isn't that a server wide setting though?

Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-24 : 14:03:06
Create a Date/Calendar table. Set up the columns that you need with the proper Work Week column. Join to said table and group by the work week.
Go to Top of Page

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-09-24 : 14:54:55
paulkem, yes. I did not know if you wanted the whole thing changed.

djj
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-24 : 15:16:30
quote:
Originally posted by James K

Group by this:
DATEADD(dd,-DATEDIFF(dd,2,YourDateColumn)%7,YourDateColumn)


If you have time portion remember to remove that like this:
DATEADD(dd,-DATEDIFF(dd,2,YourDateColumn)%7,CAST(YourDateColumn AS DATE))




DATEDIFF(dd,2,YourDateColumn) calculates the number of days between the value in YourDateColumn and the date represented by the number 2 - which is Jan 3, 1900 - which happened to be a Wednesday.

So DATEDIFF(dd,2,YourDateColumn)%7 will be zero if YourDateColumn is a Wednesday, 1 if it is Thursday, 2 if it is Friday and so on.

When you subtract 0 days from Wed, or 1 day from Thursday, or 2 days from Friday and so on, you get to Wednesday. Hence the "-DATEDIFF(dd,2,YourDateColumn)%7".
Go to Top of Page
   

- Advertisement -