locked
Grouping by First_of_Month RRS feed

  • Question

  • Hi,

     

    I've created a matrix style table in report builder.  The one problem I'm running into is the columns are broken out by days of the month and I just want the data to aggregate to first_of_month.  So, the example is that there are a lot of counts for the month of july but instead of having the columns say july1, july2, july3 etc...I want the data to consolidate under 7-1-2011, and 8-1-2011 in the table columns, and so on until my date range ends.  

    Anyone try this before?

    Thursday, May 26, 2011 1:40 PM

Answers

  • Hi,

     

    Suppose you have a date column in your select statement: MyTable.MyDate, then modify it by adding

    CAST(STR(Month(MyTable.MyDate)) + '/1/' + STR(YEAR(MyTable.MyDate)) as datetime) as DateForGrouping

    Use DateForGrouping in your report column group.

     


    Remember to mark as an answer if this post has helped you.
    • Marked as answer by Eileen Zhao Wednesday, June 1, 2011 3:08 AM
    Thursday, May 26, 2011 4:57 PM

All replies

  • Hi,

     

    Suppose you have a date column in your select statement: MyTable.MyDate, then modify it by adding

    CAST(STR(Month(MyTable.MyDate)) + '/1/' + STR(YEAR(MyTable.MyDate)) as datetime) as DateForGrouping

    Use DateForGrouping in your report column group.

     


    Remember to mark as an answer if this post has helped you.
    • Marked as answer by Eileen Zhao Wednesday, June 1, 2011 3:08 AM
    Thursday, May 26, 2011 4:57 PM
  • It is possible to do via T-SQL, please provide sample data and desired result


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, May 26, 2011 6:09 PM