May 22, 2007 at 8:25 am
Hello,
I have a table of aggregated revenue data with a DATETIME column called 'ReportDate' that identifies the date when the revenue was reported. A new record is created each day of the month.
I need to express this data in such a way that the DATETIME values become colums. So each day, there would be a new column added to the results. The full range of date columns would be limited to the prior month. The number of columns would be equal to the number of days in the prior month.
I know how to use date functions to identify the first day and last day of the previous month, but what can I do to generate daily columns in this manner? With monthly reports, its easy enough to use CASE in this situation to create twelve columns to represent each month of the year, and show the revenue for each month.
If you have any ideas on this, please share.
Thank you for your help!
CSDunn
May 22, 2007 at 9:00 am
May 22, 2007 at 2:35 pm
Thanks!
CSDunn
May 22, 2007 at 2:55 pm
In addtion to what mrpolecat said, if you only need this in reports, most reporting tools (including Reporting Services) can do this with your data as it is currently. It's often called a cross-tab report, but in RS, the object is called a Matrix. The reason I'd go this route, if it's just a report that you're interested in, is that it's far easier to do it in the reporting tool than using dynamic sql to create a physical table, and it takes up no physical storage. It also allows you to easily have multiple levels, so that your days could fall under a month name, which would fall under a year, etc, and even drill down as needed.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply