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.
| 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)) |
 |
|
|
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).aspxdjj |
|
|
|
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? |
|
|
|
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. |
|
|
|
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 |
|
|
|
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". |
|
|
|
|
|
|
|
|
Shift Work Week to W-Tues