CODEWITHSUNDEEP
ssrs-2008reporting-services
Jeff
Jeff

Reputation: 1

How to group records by different date range in SQL?

Using SSRS 2008, I need to group by 25 days the sales achieved and need to reflect the same in the line chart. So that the X axis contains the date difference eg. 1st-25th Jan 13, 26th Jan-19th Feb 13 and so on till scalar @endate... and y axis represents the correspondence sales achieved. I have grouped by weeks and months and aware of that but when its 25 days difference, i'm bit puzzled. Any hint on this would be much appreciated. Thanks.

Upvotes: 0

Views: 4772

Answers (1)

Ian Preston
Ian Preston

Reputation: 39586

For doing this at the report level, you can create a calculated column to group the various dates. I used the following expression:

=DateAdd(DateInterval.Day
  , 25 * Floor
    (
      (DateDiff(DateInterval.Day, CDate("01-Jan-2013"), Fields!date.Value)
      / 25)
    )
  , CDate("01-Jan-2013"))

Basically, it's calculating the number of days for each date since the start of the year, dividing this by 25 and rounding down, and using this new figure to calculate the start date in the period.

I've hard-coded in the start of year date, but you can easily make this parameter or expression based.

This gives the following results for some sample data:

enter image description here

You can see their Grouping Date values are as expected. Based on this, you can easily group them together in the Chart, too.

Edit after comment

Maybe you could double-check your expression? I think it's easier to set up a calculated field in the Dataset instead of trying to do it directly in the chart - then it's easy to put the results in a table to see if everything looks OK. I've expanded the example above, based on the same expression. I've added two more calculated fields, one for the group end date:

=DateAdd(DateInterval.Day, 24, Fields!groupDate.Value)

And one to create a nicely formatted group label:

=Format(Fields!groupDate.Value, "dd-MMM")
  & " - "
  & Format(Fields!groupDateEnd.Value, "dd-MMM")

Based on these I've added a Chart with the following category/series:

enter image description here

End result, grouped as required:

enter image description here

Or, alternatively you could just do it at the database level, assuming that's your Data Source; just change the grouping expression into equivalent T-SQL:

with groupDates as
(
  select *
    , groupDate = dateadd(dd, 25 * (datediff(dd, '01-jan-2013', date) / 25), '01-jan-2013')
  from departments
)
select department
  , groupDate
  , groupDateEnd = dateadd(dd, 24, groupDate)
  , groupValue = sum(value)
from groupDates
group by department
  , groupDate
order by department

SQL Fiddle with demo.

Upvotes: 2

Related Questions