Jeff S
asked on
Arithmetic overflow error converting numeric to data type numeric.
Please note, the SQL is handled dynamically by the SQL Server, therefore some items in my WHERE clause will look odd to you. Please disregard. I am getting the following Error in my report.
Server: Msg 8115, Level 16, State 8, Line 112
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
Server: Msg 8115, Level 16, State 8, Line 112
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
/* Billing Status Report 11/22/03 CySolutions
Revised 5/6/06 */
SET NOCOUNT ON
CREATE TABLE #Records
(
TicketNumber VARCHAR(20) ,
Visit DATETIME ,
LastDateFiled DATETIME ,
FirstDateFiled DATETIME ,
InsBalance MONEY ,
PatBalance MONEY ,
PrimaryInsuranceCarrier VARCHAR(50) ,
CurrentInsuranceCarrier VARCHAR(50) ,
CurrentCarrier NUMERIC(1 , 0) ,
BillStatus VARCHAR(50) ,
BillStatusID INT ,
Entered DATETIME ,
FilingType INT ,
DaysSinceFiled INT ,
PatientName VARCHAR(50) ,
VisitOwner VARCHAR(50) ,
Description VARCHAR(100) ,
Doctor VARCHAR(100) ,
Credentialed VARCHAR(1)
)
-- Insert the New Records
IF '1' = '1'
OR 1 IN ( NULL )
INSERT INTO
#Records
SELECT
ISNULL(a.TicketNumber , 'None') ,
CONVERT(VARCHAR , a.ApptStart , 101) ,
'' ,
'' ,
0 ,
0 ,
ISNULL(ic.ListName , 'None') ,
ISNULL(ic.ListName , 'None') ,
1 ,
'New' ,
1 ,
'' ,
0 ,
0 ,
pp.Last + ', ' + pp.First + ' - ' + pp.PatientID ,
'Appointment' ,
'' ,
d.ListName ,
CASE WHEN icd.InsuranceCarriersDoctorID IS NOT NULL THEN 'X'
ELSE ' '
END
FROM
dbo.InsuranceCarriers ic
INNER JOIN dbo.PatientInsurance pi ON ic.InsuranceCarriersId = pi.InsuranceCarriersId
RIGHT OUTER JOIN dbo.Appointments a ON pi.PatientProfileId = a.OwnerId
INNER JOIN dbo.PatientProfile pp ON a.OwnerID = pp.PatientProfileID
INNER JOIN DoctorFacility d ON ISNULL(a.DoctorID , a.ResourceID) = d.DoctorFacilityID
LEFT JOIN InsuranceCarriersDoctor icd ON pi.InsuranceCarriersID = icd.InsuranceCarriersID
AND ISNULL(a.DoctorID , a.ResourceID) = icd.DoctorID
WHERE
( pi.OrderForClaims = 1 )
AND ( a.PatientVisitId IS NULL )
AND ( a.ApptKind = 1 )
AND (
a.HideNewVisit IS NULL
OR a.HideNewVisit = 0
)
AND (
a.Canceled IS NULL
OR a.Canceled = 0
)
AND ( a.ApptStart < GETDATE() )
AND a.ApptStart >= ISNULL(NULL , '1/1/1900')
AND a.ApptStart < DATEADD(day , 1 , ISNULL(NULL , '1/1/3000'))
AND --Filter on insurance carrier
(
(
NULL IS NOT NULL
AND ic.InsuranceCarriersId IN ( NULL )
)
OR ( NULL IS NULL )
)
AND --Filter on facility
(
(
NULL IS NOT NULL
AND a.FacilityID IN ( NULL )
)
OR ( NULL IS NULL )
)
AND --Filter on insurance group
(
(
NULL IS NOT NULL
AND ic.InsuranceGroupId IN ( NULL )
)
OR ( NULL IS NULL )
)
AND --Filter on Doctor
(
(
NULL IS NOT NULL
AND a.ResourceID IN ( NULL )
)
OR ( NULL IS NULL )
)
-- Enter the Visit Records
INSERT INTO
#Records
SELECT
pv.TicketNumber ,
pv.Visit ,
pv.LastFiledDate ,
pv.FirstFiledDate ,
pva.InsBalance ,
pva.PatBalance ,
ISNULL(ic.ListName , 'None') ,
ISNULL(ic.ListName , 'None') ,
pv.CurrentCarrier ,
bs.Description ,
pv.BillStatus ,
pv.Entered ,
ISNULL(pv.FilingType , 0) ,
CASE WHEN pv.LastFiledDate IS NULL THEN 0
ELSE DATEDIFF(d , pv.LastFiledDate , GETDATE())
END ,
pp.Last + ', ' + pp.First + ' - ' + pp.PatientID ,
ISNULL(vo.Description , 'No Owner') ,
ISNULL(LEFT(pv.Description , 100) , '') ,
d.ListName ,
CASE WHEN icd.InsuranceCarriersDoctorID IS NOT NULL THEN 'X'
ELSE ' '
END
FROM
dbo.PatientVisit pv
INNER JOIN dbo.PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
LEFT OUTER JOIN dbo.InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersId = ic.InsuranceCarriersId
LEFT OUTER JOIN dbo.PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
LEFT OUTER JOIN dbo.InsuranceCarriers cic ON pv.CurrentInsuranceCarriersId = cic.InsuranceCarriersId
LEFT OUTER JOIN dbo.MedLists bs ON bs.JoinId = pv.BillStatus
AND bs.TableName = 'BillStatus'
INNER JOIN DoctorFacility d ON pv.DoctorID = d.DoctorFacilityID
LEFT OUTER JOIN MedLists vo ON pv.VisitOwnerMID = vo.MedListsID
LEFT JOIN InsuranceCarriersDoctor icd ON pv.CurrentInsuranceCarriersId = icd.InsuranceCarriersID
AND pv.DoctorID = icd.DoctorID
WHERE
pv.Visit >= ISNULL(NULL , '1/1/1900')
AND pv.Visit < DATEADD(day , 1 , ISNULL(NULL , '1/1/3000'))
AND pv.BillStatus NOT IN ( 12 , 10 )
--Filter on BillStatus
AND (
(
NULL IS NULL
AND '1' = '1'
)
OR (
'1' = '2'
AND NULL IS NULL
)
OR pv.BillStatus IN ( NULL )
)
AND --Filter on insurance carrier
(
(
NULL IS NOT NULL
AND ic.InsuranceCarriersId 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 insurance group
(
(
NULL IS NOT NULL
AND ic.InsuranceGroupId IN ( NULL )
)
OR ( NULL IS NULL )
)
AND -- Carrier priority to include
(
( 1 = 1 )
OR (
1 = 2
AND pv.CurrentCarrier = 1
)
OR (
1 = 3
AND pv.CurrentCarrier > 1
)
)
AND --Filter on visitowner
(
(
NULL IS NOT NULL
AND pv.VisitOwnerMID IN ( NULL )
)
OR ( NULL IS NULL )
)
AND --Filter on Doctor
(
(
NULL IS NOT NULL
AND pv.DoctorID IN ( NULL )
)
OR ( NULL IS NULL )
)
IF 0 <> 1
SELECT
*
FROM
#Records
ELSE
SELECT
*
FROM
#Records
WHERE
BillStatus <> 'New'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.