Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1211
  • Last Modified:

SQL 2005 Error - Cannot call methods on money.

Please note, the SQL is handled dynamically, therefore, some items in my WHERE clauses will look odd to you. Please disregard them. I am getting back an error I've never seen .... any thoughts?

Msg 258, Level 15, State 1, Line 428
Cannot call methods on money.
/* Daily Balance */
 
SET NOCOUNT ON
 
CREATE TABLE #Temp	
		(	
		Type smallint,
		MTDCharges money NULL,
		MTDPayments money NULL,
		MTDAdjustments money NULL, 
		Diagnosis smallint,
		PatientVisitId int,
		PatientVisitProcsId int NULL,
		BatchId int NULL,
		Batch varchar(255) NULL,
		Date datetime NULL,		
		DepositDate datetime NULL,
		Source varchar(10) NULL,
		PayerName varchar(255) NULL,
		PaymentMethod varchar(25) NULL,
		ActionType varchar(200) NULL,
		DocumentNumber varchar(30) NULL,
		Amount money NULL,
		Code varchar(10) NULL,
		Modifier1 varchar(30) NULL,
		Modifier2 varchar(30) NULL,
		Modifier3 varchar(30) NULL,
		Modifier4 varchar(30) NULL,
		PatientVisitDiags1 smallint NULL,
		PatientVisitDiags2 smallint NULL,
		PatientVisitDiags3 smallint NULL,
		PatientVisitDiags4 smallint NULL,
		PatientVisitDiags5 smallint NULL,
		PatientVisitDiags6 smallint NULL,
		PatientVisitDiags7 smallint NULL,
		PatientVisitDiags8 smallint NULL,
		PatientVisitDiags9 smallint NULL,
		Description varchar(255) NULL,
		Fee money NULL,
		Units float NULL,
		TotalCharge money NULL,
		ListOrder smallint NULL,
		CreatedBy varchar(30) NULL,
		Created datetime NULL,
		)
 
/* Insert the Charges into the temp table */
 
IF 1 = 1
 
BEGIN
	INSERT	#Temp
		(	
		Type, 
		MTDCharges,
		PatientVisitId, 
		PatientVisitProcsId, 
		Diagnosis, 
		BatchId, 
		Batch,
		Date, 
		Code,
		Modifier1, 
		Modifier2, 
		Modifier3, 
		Modifier4,
		PatientVisitDiags1, 
		PatientVisitDiags2, 
		PatientVisitDiags3, 
		PatientVisitDiags4, 
		PatientVisitDiags5, 
		PatientVisitDiags6, 
		PatientVisitDiags7, 
		PatientVisitDiags8, 
		PatientVisitDiags9, 
		Description, 
		Fee, 
		Units, 
		TotalCharge, 
		ListOrder, 
		CreatedBy, 
		Created
		)
 
	SELECT	
		1, 
		(
		SELECT
--			SUM(CASE WHEN ara.Type = 'C' THEN ara.InsAmount + ara.PatAmount ELSE -ara.InsAmount + -ara.PatAmount END)
			SUM(InsAmount + PatAmount) * -1 
		FROM
			ARAggregates ara
		WHERE
			ara.Type = 'C' AND
			ara.DateType = 'entry' AND
			ara.Day >= ISNULL('04/01/2009','1/1/1900') AND
			ara.Day < dateadd(month, 1, ISNULL('04/15/2009','1/1/3000'))
			AND  --Filter on doctor
			(
			(NULL IS NOT NULL AND ara.DoctorID IN (NULL)) OR
			(NULL IS NULL)
			)
			AND  --Filter on facility
			(
			(NULL IS NOT NULL AND ara.FacilityID IN (NULL)) OR
			(NULL IS NULL)
			)
		) as MTDCharges, 
		pv.PatientVisitId, 
		pvp.PatientVisitProcsId, 
		0, 
		b.BatchId, 
		b.Name AS Batch,
		pvp.DateOfServiceFrom, 
		pvp.Code, 
		m1.Code, 
		m2.Code, 
		m3.Code, 
		m4.Code,
		pvp.PatientVisitDiags1, 
		pvp.PatientVisitDiags2, 
		pvp.PatientVisitDiags3, 
		pvp.PatientVisitDiags4, 
		pvp.PatientVisitDiags5, 
		pvp.PatientVisitDiags6, 
		pvp.PatientVisitDiags7, 
		pvp.PatientVisitDiags8, 
		pvp.PatientVisitDiags9, 
		pvp.Description, 
		pvp.Fee, 
		pvp.Units, 
		pvp.TotalFee, 
		pvp.ListOrder, 
		pvp.CreatedBy, 
		pvp.Created
 
	FROM	PatientVisitProcs pvp 
		INNER JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId
		INNER JOIN Batch b ON pvp.BatchId = b.BatchId
		LEFT JOIN MedLists m1 ON pvp.Modifier1MId = m1.MedListsId
		LEFT JOIN MedLists m2 ON pvp.Modifier2MId = m2.MedListsId
		LEFT JOIN MedLists m3 ON pvp.Modifier3MId = m3.MedListsId
		LEFT JOIN MedLists m4 ON pvp.Modifier4MId = m4.MedListsId
	
	WHERE	pvp.DateOfEntry >= ISNULL('04/01/2009','1/1/1900') AND pvp.DateOfEntry  < dateadd(day,1,ISNULL('04/15/2009','1/1/3000'))
		AND  --Filter on doctor
		(
		(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
		(NULL IS NULL)
		)
		AND  --Filter on facility
		(
		(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
		(NULL IS NULL)
		)
		AND  --Filter on company
		(
		(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
		(NULL IS NULL)
		)
		AND  --Filter on user
		(
		(NULL IS NOT NULL AND pvp.CreatedBy = NULL) OR
		(NULL IS NULL)
		)
		AND  --Filter on batch
		(
		(NULL IS NOT NULL AND b.BatchID IN (NULL)) OR
		(NULL IS NULL)
		)
 
	/* Insert the Diagnosis into the temp table */
	
	INSERT	#Temp
		(	
		Type, 
		PatientVisitId, 
		PatientVisitProcsId, 
		Diagnosis, 
		BatchId, 	
		Batch,
		Code, 
		Description, 
		ListOrder,
		CreatedBy, 
		Created
		)
 
	SELECT DISTINCT	
		1, 
		t.PatientVisitId, 
		t.PatientVisitProcsId, 
		-1, 
		t.BatchId, 
		t.Batch,
		pvd.Code, 
		pvd.Description, 
		pvd.ListOrder,
		t.CreatedBy, 
		t.Created
	
	FROM	#Temp t 
		INNER JOIN PatientVisitDiags pvd ON t.PatientVisitId = pvd.PatientVisitId
END
 
/* Insert the Payments into the temp table */
 
IF 1 = 1
 
	INSERT	#Temp
		(	
		Type, 
		MTDPayments,
		PatientVisitId, 
		Diagnosis, 
		BatchId, 
		Batch,
		Date, 
		DepositDate, 
		Source, 
		PayerName,
		PaymentMethod, 
		ActionType, 
		DocumentNumber, 
		Amount,
		CreatedBy, 
		Created
		)
 
	SELECT	
		2, 
		(
		SELECT
			SUM(InsAmount + PatAmount) * -1
		FROM
			ARAggregates ara
		WHERE
			ara.Type='P' AND
			ara.DateType = 'entry' AND
			ara.Day >= ISNULL('04/01/2009','1/1/1900') AND
			ara.Day < dateadd(month, 1, ISNULL('04/15/2009','1/1/3000'))
			AND  --Filter on doctor
			(
			(NULL IS NOT NULL AND ara.DoctorID IN (NULL)) OR
			(NULL IS NULL)
			)
			AND  --Filter on facility
			(
			(NULL IS NOT NULL AND ara.FacilityID IN (NULL)) OR
			(NULL IS NULL)
			)
		) as MTDPayments,
		pv.PatientVisitId, 
		0, 
		b.BatchId, 
		b.Name AS Batch,
		ISNULL(pm.CheckDate,b.Entry),
		pm.DepositDate,
		CASE WHEN pm.Source = 1 THEN 'Patient' ELSE 'Insurance' END AS Source,
		pm.PayerName,
		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 'Unknown'
		END AS PaymentMethod, 
		ISNULL(at.Description, 'Unknown'), 
		pm.CheckCardNumber, 
		t.Amount,
		t.CreatedBy, 
		t.Created
 
	FROM	PaymentMethod pm 
		INNER JOIN VisitTransactions vt ON pm.PaymentMethodId = vt.PaymentMethodId AND (pm.InsuranceTransfer = 0 OR pm.InsuranceTransfer IS NULL)
		INNER JOIN Transactions t ON vt.VisitTransactionsId = t.VisitTransactionsId AND t.Action = 'P'
		INNER JOIN PatientVisit pv ON vt.PatientVisitId = pv.PatientVisitId
		INNER JOIN Batch b On pm.BatchId = b.BatchId
		LEFT JOIN MedLists at ON t.ActionTypeMId = at.MedListsId
	
	WHERE	pm.DateOfEntry >= ISNULL('04/01/2009','1/1/1900') AND pm.DateOfEntry  < dateadd(day,1,ISNULL('04/15/2009','1/1/3000'))
		AND  --Filter on doctor
		(
		(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
		(NULL IS NULL)
		)
		AND  --Filter on facility
		(
		(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
		(NULL IS NULL)
		)
		AND  --Filter on company
		(
		(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
		(NULL IS NULL)
		)
		AND  --Filter on user
		(
		(NULL IS NOT NULL AND t.CreatedBy = NULL) OR
		(NULL IS NULL)
		)
		AND  --Filter on batch
		(
		(NULL IS NOT NULL AND b.BatchID IN (NULL)) OR
		(NULL IS NULL)
		)
		AND -- Filter on deposit date
		(
		(pm.DepositDate >= ISNULL(NULL,'1/1/1900') AND pm.DepositDate  < dateadd(day,1,ISNULL(NULL,'1/1/3000'))) OR 
                	(NULL IS NULL AND NULL IS NULL)
		)
	
/* Insert the Adjustments into the temp table */
 
IF 1 = 1
	
	INSERT	#Temp
		(	
		Type, 
		MTDAdjustments, 
		PatientVisitId, 
		Diagnosis, 
		BatchId, 
		Batch,
		Date, 
		DepositDate, 
		Source, 
		PayerName,
		PaymentMethod, 
		ActionType, 
		DocumentNumber, 
		Amount,
		CreatedBy, 
		Created
		)
 
	SELECT	
		3, 
		(
		SELECT
			SUM(InsAmount + PatAmount) * -1
		FROM
			ARAggregates ara
		WHERE
			ara.Type='A' AND
			ara.DateType = 'entry' AND
			ara.Day >= ISNULL('04/01/2009','1/1/1900') AND
			ara.Day < dateadd(month, 1, ISNULL('04/15/2009','1/1/3000'))
			AND  --Filter on doctor
			(
			(NULL IS NOT NULL AND ara.DoctorID IN (NULL)) OR
			(NULL IS NULL)
			)
			AND  --Filter on facility
			(
			(NULL IS NOT NULL AND ara.FacilityID IN (NULL)) OR
			(NULL IS NULL)
			)
		) as MTDAdjustments, 
		pv.PatientVisitId, 
		0, 
		b.BatchId, 
		b.Name AS Batch,
		ISNULL(pm.CheckDate,b.Entry),
		pm.DepositDate,
		CASE WHEN pm.Source = 1 THEN 'Patient' ELSE 'Insurance' END AS Source,
		pm.PayerName,
		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 'Unknown'
		END AS PaymentMethod, 
		ISNULL(at.Description, 'Unknown'), 
		pm.CheckCardNumber, 
		t.Amount,
		t.CreatedBy, 
		t.Created
	
	FROM	PaymentMethod pm 
		INNER JOIN VisitTransactions vt ON pm.PaymentMethodId = vt.PaymentMethodId AND (pm.InsuranceTransfer = 0 OR pm.InsuranceTransfer IS NULL)
		INNER JOIN Transactions t ON vt.VisitTransactionsId = t.VisitTransactionsId AND t.Action = 'A'
		INNER JOIN PatientVisit pv ON vt.PatientVisitId = pv.PatientVisitId
		INNER JOIN Batch b On pm.BatchId = b.BatchId
		LEFT JOIN MedLists at ON t.ActionTypeMId = at.MedListsId
	
	WHERE	pm.DateOfEntry >= ISNULL('04/01/2009','1/1/1900') AND pm.DateOfEntry  < dateadd(day,1,ISNULL('04/15/2009','1/1/3000'))
		AND  --Filter on doctor
		(
		(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
		(NULL IS NULL)
		)
		AND  --Filter on facility
		(
		(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
		(NULL IS NULL)
		)
		AND  --Filter on company
		(
		(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
		(NULL IS NULL)
		)
		AND  --Filter on user
		(
		(NULL IS NOT NULL AND t.CreatedBy LIKE '%NULL%') OR
		(NULL IS NULL)
		)
		AND  --Filter on batch
		(
		(NULL IS NOT NULL AND b.BatchID IN (NULL)) OR
		(NULL IS NULL)
		)
		AND -- Filter on deposit date
		(
		(pm.DepositDate >= ISNULL(NULL,'1/1/1900') AND pm.DepositDate  < dateadd(day,1,ISNULL(NULL,'1/1/3000'))) OR 
                	(NULL IS NULL AND NULL IS NULL)
		)
 
	SELECT		
		t.Type, 
		t.MTDCharges,
		t.MTDPayments.
		t.MTDAdjustments,   
		t.PatientVisitId, 
		t.PatientVisitProcsId, 
		t.Diagnosis, 
		pp.PatientProfileId,
		pp.PatientId, 
		RTRIM(RTRIM(ISNULL(pp.Last,'') + ' ' + ISNULL(pp.Suffix,'')) + ', ' + ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,'')) AS PatientName,
		pv.DoctorId, 
		d.ListName AS Doctor, 
		pv.FacilityId, 
		f.ListName AS Facility, 
		pv.CompanyId, 
		c.ListName AS Company,
		pv.TicketNumber, 
		t.BatchId, 
		t.Batch, 
		t.Date, 
		t.DepositDate,
		t.Source, 
		t.PayerName, 
		t.PaymentMethod, 
		t.ActionType, 
		t.DocumentNumber, 
		t.Amount,
		t.Code, 
		t.Modifier1, 
		t.Modifier2, 
		t.Modifier3, 
		t.Modifier4,
		t.PatientVisitDiags1, 
		t.PatientVisitDiags2, 
		t.PatientVisitDiags3, 
		t.PatientVisitDiags4, 
		t.PatientVisitDiags5, 
		t.PatientVisitDiags6, 
		t.PatientVisitDiags7, 
		t.PatientVisitDiags8, 
		t.PatientVisitDiags9, 
		t.Description, 
		t.Fee, 
		t.Units, 
		t.TotalCharge, 
		t.ListOrder, 
		t.CreatedBy, 
		t.Created, 
		Flag=convert(varchar(50),'(none)')
 
	FROM	#Temp t 
		INNER JOIN PatientVisit pv ON t.PatientVisitId = pv.PatientVisitId
		INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId	
		INNER JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId
		INNER JOIN DoctorFacility f ON pv.FacilityId = f.DoctorFacilityId
		INNER JOIN DoctorFacility c ON pv.CompanyId = c.DoctorFacilityId

Open in new window

0
Jeff S
Asked:
Jeff S
1 Solution
 
udaya kumar laligondlaTechnical LeadCommented:
check line 427 you have specified a "." instead of ","
this is the reason for the error.
change
t.MTDPayments.
t.MTDAdjustments,
to
t.MTDPayments,
t.MTDAdjustments,
 
0
 
Jeff SAuthor Commented:
Oh my goodness .... I need to go to bed. Thanks for the fix.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now