Break Up Date Parameters by month and year.

[mchatz13]

I have to create a Crystal Report off of a SQL SP. The report needs to have dynamic columns that will show monthly usage of an item. If the user passes a date parameter (12/01/03 - 03/13/04) I would like the SP to show usage for that item in vertical column for each of the months within the date range, (the dynamic columns I can handle in Crystal). The table would look like this:

Item Name Month/Year Usage
a 12/03 289
a 1/04 350
a 2/04 330
a 3/04 100

There would be more fields in the table but if I must make a seperate table or even SP to do this that is cool. Please let me know.

 

[zhavic]

No sure, but is this what you are looking for ?

CREATE PROCEDURE my_proc
	@cField_name varchar(100),
	@tStart_date datetime,
	@tEnd_date datetime

	AS

	DECLARE @cMacro nvarchar(1000)

	SET @cMacro = 'SELECT ' + @cField_name + ' ',
					   + 'CAST( MONTH( date_field ) AS varchar(2) ) + ''/'' + RIGHT( CAST( YEAR( date_field ) AS varchar(4) ), 2 ) AS [Month_year], '
					   + 'COUNT( ' + @cField_name + ' ) AS Usage '
					+ 'FROM you_table '
					+ 'WHERE CONVERT( char(10), date_field, 112 ) BETWEEN CONVERT( char(10), @tStart_date, 112 ) AND CONVERT( char(10), @tEnd_date, 112 ) '
					+ 'GROUP BY ' + @cField_name + ' ',
							+ 'CAST( MONTH( date_field ) AS varchar(2) ) + '/' + RIGHT( CAST( YEAR( date_field ) AS varchar(4) ), 2 ) '
					+ 'ORDER BY 1, 2'

	EXECUTE sp_executesql @cMacro,
						  N'@tStart_date datetime, @tEnd_date datetime',
						  @tStart_date,
						  @tEnd_date

Zhavic




---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.