4/4/2010 4/4/2010 NULL 5/4/2010 NULL 6/4/2010 ... 11/4/2010 11/4/2010 NULL 12/4/2010In 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?
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.
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
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
@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.
@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.
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.
