question

node18 avatar image
node18 asked

Group days by week sql server ssrs

I want to group dates by week. I currently have to functions: The first of which returns all fiscal weeks of the year and the second all the dates. I do a left outer join on the Fiscal weeks from the days to get this result set.
 4/4/2010    4/4/2010 
 NULL        5/4/2010 
 NULL        6/4/2010 
 ...
 11/4/2010   11/4/2010
 NULL        12/4/2010 
In SSRS, I want to group the days by weeek, so I need to have the Null values having the fiscal week that the dates are part of, any ideas of how to do this?
sql-server-2005sqlssrs
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
Have you tried the WEEK parameter in the Datepart function to provide data for the cell in the report? Using that should then give you a value you can group on... [ http://msdn.microsoft.com/en-us/library/ms174420.aspx][1] [1]: http://msdn.microsoft.com/en-us/library/ms174420.aspx
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks, I wasn't aware of all the date functions, but it provides a good mechanism to solve the problem.
0 Likes 0 ·
Henrik Staun Poulsen avatar image
Henrik Staun Poulsen answered
I prefer to have a Calendar table, that I do an inner join to. This table can have all the fields I need for first day of month, year number, day number etc. Joe Celco used to have an example somewhere. Here is another example [of a good calendar by Dan Guzman][1] [1]: http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Fatherjack; it normally starts with one report, then you go for a cup of coffee, and when you come back, it is used in many places.
1 Like 1 ·
@Henrik, a great alternative.
I generally only create a calendar table if there are lots of processes that would need it, simply to justify its existence. If its just a one off report it would get the altered TSQL option from me.
0 Likes 0 ·
Thanks for your input, I will start doing such things as my skills improve.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.