SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Shift Work Week to W-Tues
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

paulkem
Starting Member

25 Posts

Posted - 09/24/2013 :  12:25:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 09/24/2013 :  12:56:54  Show Profile  Reply with Quote
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

USA
283 Posts

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

djj
Go to Top of Page

paulkem
Starting Member

25 Posts

Posted - 09/24/2013 :  13:51:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4354 Posts

Posted - 09/24/2013 :  14:03:06  Show Profile  Reply with Quote
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

USA
283 Posts

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

djj
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 09/24/2013 :  15:16:30  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000