Help with Stored Procedure

[primagic]

I have the following stored procedure which exports some results to excel.

USE [BusinessSupportReporting]
GO
/****** Object:  StoredProcedure [dbo].[spMonthlyProvidentCompliance]    Script Date: 04/02/2014 10:25:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spMonthlyProvidentCompliance]
@Email AS BIT

AS

SET NOCOUNT ON;

DECLARE @temp				AS VARCHAR(1000);
DECLARE @FileDate			AS VARCHAR(20);
DECLARE @FileName			AS VARCHAR(200);
DECLARE @bcpCMD				AS VARCHAR(3000);
DECLARE @MessageHTML		AS VARCHAR(MAX);
DECLARE @StartDate			AS DATE;
DECLARE @EndDate			AS DATE;

SET @StartDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0);
SET @EndDate = DATEADD(MONTH, DATEDIFF(MONTH, - 1, GETDATE()) - 1,- 1);





IF OBJECT_ID('dbo.tempProvidentCompliance', 'U') IS NOT NULL
DROP TABLE dbo.tempProvidentCompliance

CREATE TABLE dbo.tempProvidentCompliance (
[Date]					VARCHAR(24) NULL,
Dials		            VARCHAR(24) NULL,
Connects		        VARCHAR(24) NULL,
Abandons      			VARCHAR(24) NULL,
[Abandon Rate]			varchar(24) NULL
)

INSERT INTO dbo.tempProvidentCompliance([Date],Dials,Connects,Abandons, [Abandon Rate])
SELECT 
convert(char(12), JH.jobdate) AS DialDate,
CONVERT(VARCHAR(20),COUNT(DAJS.ID)) AS Dials, 
CONVERT(VARCHAR(20),SUM(DAJS.connectFlag)) AS Connects,
CONVERT(VARCHAR(20),SUM(CASE WHEN DAJS.Code IN (48,96) THEN 1 ELSE 0 END)) As Abandons,
CONVERT(float,SUM(CASE WHEN DAJS.Code IN (48,96) THEN 1 ELSE 0 END)) / CONVERT(float,SUM(DAJS.connectFlag))* 100 as [AbandonRate]

FROM OPENQUERY([BCW-APPS],'SELECT ID, jobNum, jobdate, bcwref, clientcode, connectFlag, Code, phoneNum, type FROM Strategy..Dial_Attempts_JS') AS DAJS
JOIN (SELECT * FROM OPENQUERY([BCW-APPS],'SELECT jobNo, jobDate, jobName FROM Strategy..Job_History')) AS JH ON JH.jobNo = DAJS.jobNum
JOIN BusinessSupportReporting.dbo.OfcomJobHierarchy OH ON OH.JobName collate SQL_Latin1_General_CP1_CI_AS=JH.jobName collate SQL_Latin1_General_CP1_CI_AS
WHERE (OH.Hierarchy = 'BCW Contingent') AND
(DAJS.[type] = 'O') AND
(JH.jobDate >= @StartDate AND
JH.jobDate <= @EndDate)
GROUP BY JH.jobDate


		-------------------------------------------------------------------
		------------------SORT OUT HEADERS FOR EXCEL-----------------------
		-------------------------------------------------------------------

		SELECT @temp=(CASE WHEN @temp is null THEN '' ELSE @temp+',' END)+''''+column_name+'''' 
		FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tempProvidentCompliance'

		IF OBJECT_ID('dbo.tempProvidentHeaders', 'U') IS NOT NULL
		DROP TABLE dbo.tempProvidentHeaders

		CREATE TABLE dbo.tempProvidentHeaders (
		[Date]					VARCHAR(24) NULL,
		Dials	                VARCHAR(24) NULL,
		Connects		        VARCHAR(24) NULL,
		Abandons	            VARCHAR(24) NULL,
		[Abandon Rate]      	VARCHAR(24) NULL
		
		)

		INSERT INTO tempProvidentHeaders EXEC('select ' + @temp)
	

		-------------------------------------------------------------------
		-----------------------------EXPORT--------------------------------
		-------------------------------------------------------------------

		SET @FileDate = SUBSTRING(REPLACE(CONVERT(VARCHAR(20),@StartDate,103),'/',''),3,6)
		--SET @FileName = '\\bcw-secureftp\Eircomletters\Outgoing\Provident_' + @FileDate + '.xls'
		SET @FileName = 'D:\Files\Provident_Compliance_Monthly_' + @FileDate + '.xls'
		SET @bcpCMD = 'bcp "SELECT * FROM BusinessSupportReporting.dbo.tempProvidentHeaders UNION All SELECT * FROM BusinessSupportReporting.dbo.tempProvidentCompliance" queryout "' + @FileName + '" -T -c'
		EXEC xp_cmdshell @bcpCMD
	
	
		-------------------------------------------------------------------
		-----------------------------TIDY UP-------------------------------
		-------------------------------------------------------------------

		IF OBJECT_ID('dbo.tempProvidentCompliance', 'U') IS NOT NULL
		DROP TABLE dbo.tempProvidentCompliance

		IF OBJECT_ID('dbo.tempProvidentHeaders', 'U') IS NOT NULL
		DROP TABLE dbo.tempProvidentHeaders
		
	
		-------------------------------------------------------------------
		--------------------------SEND EMAILS------------------------------
		-------------------------------------------------------------------
		
IF @Email = 1
BEGIN			
		SET @MessageHTML = 'Hi<BR><BR>Please find attached monthly compliance report. Please upload to Provident sftp site<BR><BR>' 
						+ '<strong>Period:</strong>&nbsp; ' + convert(varchar(10), @StartDate,103) + '&nbsp;-&nbsp;' + convert(varchar(10), @EndDate,103) + '<BR><BR>'
						+ 'Regards<BR>'
						+ 'Business Support Team' 
										
		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = 'ReportsBS1Email',
			@recipients = '????',
			@subject = 'Monthly Provident Compliance Report',
			@body = @MessageHTML,
			@body_format = 'HTML',
			@file_attachments = @FileName
			
		exec  xp_cmdshell "Del D:\Files\Provident*.xls"

			
END

I need to be able to export the abandon rate as a percentage with 2 decimal places. So like 2.11% not like 2.11565

Any ideas how to do this? Can it be done

 

[drlex]

You'll need to change the code that is casting the rate as a character field

...
CONVERT(float,SUM(CASE WHEN DAJS.Code IN (48,96) THEN 1 ELSE 0 END)) / CONVERT(float,SUM(DAJS.connectFlag))* 100 as [AbandonRate]

I tend to use decimal prior to varchar conversion:

select cast(cast(0.9455 as decimal (5,2)) as varchar(20))

output: 0.95

soi là, soi carré