Modifying a stored procedure

[RepRider]

CRXI and SQL 2000

I have the following stored procedure which works fine except for a small detail I need. I did not write this nor have I worked with SPs much. Any help is appreciated!

This currently creates 12 months of data that I display in a crosstab. However the column heading only shows up as a number representing the month (and not year). For example, 1 - January, 2 = February, etc. In the crosstab I would like the header to show 01/08 or 02/08 and be ordered by oldest to latest. Right now I have to manually adjust each time by going into "specified order" each month its refreshed and for some reason it takes an ungodly amount of "wait time" to complete this. I pull a running 12 months and have to delete the oldest date and add the newest via specified order.

Can anyone help modify the stored procedure to show both the month and year of the data that is called?

Here is the code:

CREATE procedure [dbo].[usp_AdultTeamReportBase_wMonths] (
@prmStartDate datetime,
@prmEndDate datetime,
@prmNoActivities varchar(200)
)
as

declare @prmMonths tinyint
declare @prmSQLString nvarchar(2000)

/*
declare @prmNoActivities varchar(200)
declare @prmStartDate datetime
declare @prmEndDate datetime

set @prmStartDate = '2005-10-01'
set @prmEndDate = '2006-05-31'
set @prmNoActivities = '99999,H0004,H0006'
*/

set @prmSQLString = ''

/* Only totals direct services */
set @prmMonths = datediff(m, @prmStartDate, @prmEndDate+1);


set @prmSQLString = @prmSQLString + 'select'
set @prmSQLString = @prmSQLString + ' f.SupervisorName'
set @prmSQLString = @prmSQLString + ', f.EmployeeName'
set @prmSQLString = @prmSQLString + ', f.ActivityType'
set @prmSQLString = @prmSQLString + ', f.ServiceCode'
set @prmSQLString = @prmSQLString + ', f.ServiceDescription'
set @prmSQLString = @prmSQLString + ', f.CostCenter'
set @prmSQLString = @prmSQLString + ', datepart(mm,f.StartDate) as SvcMonth'
set @prmSQLString = @prmSQLString + ', count(distinct f.ClientCode) as DistinctConsumers'
set @prmSQLString = @prmSQLString + ', count(*) as TotalContacts'
set @prmSQLString = @prmSQLString + ', sum(cast(f.EffectiveMinutes as float))/60 as Hours'
set @prmSQLString = @prmSQLString + ', cast(sum(cast(f.EffectiveMinutes as float))/60 /'
set @prmSQLString = @prmSQLString + ' ( '
set @prmSQLString = @prmSQLString + ' select sum(cast(f1.EffectiveMinutes as float))/60 '
set @prmSQLString = @prmSQLString + ' from fnProductivityReportBase('
set @prmSQLString = @prmSQLString + dbo.xEnclose(@prmStartDate,'''','''') + ','
set @prmSQLString = @prmSQLString + dbo.xEnclose(@prmEndDate,'''','''') + ') as f1 '
set @prmSQLString = @prmSQLString + ' where f.EmployeeNumber = f1.EmployeeNumber '
set @prmSQLString = @prmSQLString + ' and not f1.Activity in ('+ dbo.fnDQList(@prmNoActivities) +') '

set @prmSQLString = @prmSQLString + ' )as dec(6,3)) as HourPct'
set @prmSQLString = @prmSQLString + ', sum(f.Units) as TotalUnits'
set @prmSQLString = @prmSQLString + ', sum(cast(f.EffectiveMinutes as float))/60/('+str(@prmMonths)+'*1.30) as ProductivityPct'
set @prmSQLString = @prmSQLString + ', f.Activity'
set @prmSQLString = @prmSQLString + ', f.ActivityDesc'
set @prmSQLString = @prmSQLString + ', f.ProgramDescription'
set @prmSQLString = @prmSQLString + ' from fnProductivityReportBase('
set @prmSQLString = @prmSQLString + dbo.xEnclose(@prmStartDate,'''','''') + ','
set @prmSQLString = @prmSQLString + dbo.xEnclose(@prmEndDate,'''','''') + ') as f '

set @prmSQLString = @prmSQLString + 'where not f.Activity in ('+ dbo.fnDQList(@prmNoActivities) +')'

set @prmSQLString = @prmSQLString + 'group by f.SupervisorName, f.EmployeeNumber, f.EmployeeName, '
set @prmSQLString = @prmSQLString + 'f.ActivityType, f.ServiceCode, f.ServiceDescription, f.CostCenter, '
set @prmSQLString = @prmSQLString + 'f.Activity, f.ActivityDesc, f.ProgramDescription, datepart(mm,f.StartDate) '
set @prmSQLString = @prmSQLString + 'order by f.SupervisorName, f.EmployeeName, f.ActivityType desc, f.ServiceCode;'

exec sp_executesql @prmSQLString
GO


Thank you in advance!!!

 

[Madawc]

Try the SQL forum

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10