Jeff S
asked on
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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER