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.
Code:
/* 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'