Group by Week Issue

[fmrock]

I am trying to create a query that groups data by week. However the week should be Saturday to Friday.

Any ideas how I should convert my date to Friday and then I can group/sum my data based on that date.

Thanks .

 

[SQLBill]

Use the BOL (Microsoft Books OnLine) and look up 'SET DATEFIRST'. You can use that to set the first day of the week to Saturday. I suggest that your code set it to Saturday at the beginning and then at the end set it back to what it currently is. You can 'capture' the current first day of the week with @@DATEFIRST.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[fmrock]

Is there anyway of doing it without changing the SET DATEFIRST?

In Oracle I can do something like this SELECT next_day(trunc(sysdate)-7,'Thursday') dtWeek FROM DUAL;

 

[gmmastros]

You could try grouping by:

DateDiff(Day, 0, DateAdd(Day, -5, YourDateColumnHere)) / 7

This will not depend on your DateFirst setting. It does depend on the fact that Jan 1, 1900 is a Monday, but even the best "History Re-writers" can't touch this!



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fmrock]

How do you use this DateDiff(Day, 0, DateAdd(Day, -5, YourDateColumnHere)) / 7 to display a date?

 

[gmmastros]

[!]DateAdd(Day, 7 * [/!]DateDiff(Day, 0, DateAdd(Day, -5, YourDateColumnHere)) / 7[!], 0)[/!]

If I am not mistaken, this should show the Friday date. If you want to show the Saturday date, then change the last 0 to 1.

DateAdd(Day, 7 * DateDiff(Day, 0, DateAdd(Day, -5, YourDateColumnHere)) / 7, [!]1[/!])

Not tested.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom