Group days by week sql server / ssrs

Question

I'm going to rephrase a question that I prevously had asked.

I want to group dates by week. I currently have two 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?

You should have edited your original question to be more clear instead of posting a new question. — jzd, Jan 05 '11 at 12:43
Can you post the structure of the two tables? Seeing how both the fiscal week table and the days table you have are set up will help answer the question. Also, can you create a clearer idea of what the end result in SSRS looks like? — K Richard, Jan 05 '11 at 18:59

score 3 · Accepted Answer · edited May 02 '16 at 07:19

I found an alternative way to fill the null. This was by using the datepart function within SQL to return the week number.

SELECT DATEPART(WEEK, specified_date)

Which returns the number of the week. I then did a further expression in the cell for the week column in SSRS, to make it co-align with orignal fiscal week number and now have.

 1    4/4/2010 
 1    5/4/2010 
 1    6/4/2010 
 ...
 2  11/4/2010

Which allows me to group by week in the SSRS.

Group days by week sql server / ssrs

1 Answers1