Link to home
Start Free TrialLog in
Avatar of Jeff S
Jeff SFlag for United States of America

asked on

SQL 2005 - Disallowed implicit conversion from data type varchar to data type money,

I am getting the following Error. Suggestions?

Server: Msg 260, Level 16, State 1, Line 69
Disallowed implicit conversion from data type varchar to data type money, table 'tempdb.dbo.#Tmp________________________________________________________________________________________________________________00000000F4BB', column 'Amount'. Use the CONVERT function to run this query.

SET NOCOUNT ON 

declare @groupby1 varchar(60),
             @groupby2 varchar(60)

set @groupby1 = convert(varchar(60),'None')
set @groupby2 = convert(varchar(60),'None')

SELECT
        	pm.DateDeleted,
        	pm.DeletedBy,
        	pm.PayerName,
        	pm.PayerType,
        	pm.DateOfEntry,
        	pm.Amount,
        	CASE 
			WHEN pm.paymenttype = '1' THEN 'Cash'
			WHEN pm.paymenttype = '2' THEN 'Check'
			WHEN pm.paymenttype = '3' THEN 'Credit Card'
			WHEN pm.paymenttype = '4' THEN 'EFT'
			WHEN pm.paymenttype = '5' THEN 'Money Order'
			WHEN pm.paymenttype = '6' THEN 'Conveyance'
		ELSE ''
		END AS PaymentMethod,
        	pp.PatientId,
		pp.PatientProfileID,
        	pp.Last,
        	pp.First,
		dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS [Patient Name],
        	pv.Visit,
        	pv.TicketNumber,
		case @groupby1
		when 'Patient' then dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) 
		when 'Payer Name' then pm.PayerName
		when 'Payment Method' then 	CASE 
					WHEN pm.paymenttype = '1' THEN 'Cash'
					WHEN pm.paymenttype = '2' THEN 'Check'
					WHEN pm.paymenttype = '3' THEN 'Credit Card'
					WHEN pm.paymenttype = '4' THEN 'EFT'
					WHEN pm.paymenttype = '5' THEN 'Money Order'
					WHEN pm.paymenttype = '6' THEN 'Conveyance'
					ELSE ''
					END 
		when 'User' then pm.DeletedBy
		else 'None'
    		end as Group1,
		case @groupby2
		when 'Patient' then dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) 
		when 'Payer Name' then pm.PayerName
		when 'Payment Method' then 	CASE 
					WHEN pm.paymenttype = '1' THEN 'Cash'
					WHEN pm.paymenttype = '2' THEN 'Check'
					WHEN pm.paymenttype = '3' THEN 'Credit Card'
					WHEN pm.paymenttype = '4' THEN 'EFT'
					WHEN pm.paymenttype = '5' THEN 'Money Order'
					WHEN pm.paymenttype = '6' THEN 'Conveyance'
					ELSE ''
					END 
		when 'User' then pm.DeletedBy
		else 'None'
    	end as Group2
INTO #Tmp

FROM	cusPMDeleteLog pm
        LEFT JOIN cusVTDeleteLog vt ON pm.PaymentMethodId = vt.PaymentMethodId
        LEFT JOIN patientvisit pv ON pv.PatientVisitId = vt.PatientVisitId
        LEFT JOIN patientprofile pp ON pp.PatientProfileId = pv.PatientProfileId

SELECT
        *
FROM
	#tmp
WHERE  
 	[Amount]<> '0.00'
 	AND -- Filter on Deleted Date
	(
	DateDeleted >= ISNULL('01/01/2009','01/01/1900') AND [DateDeleted] < DATEADD(d,1, ISNULL('12/31/2009','01/01/3000'))
	)
	AND  --Filter on Patient
	(
	(NULL IS NOT NULL AND [PatientProfileID] IN (NULL)) OR
	(NULL IS NULL)
	)
	
DROP TABLE #tmp

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial