Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Arithmetic overflow error converting int to data type numeric. SQL 2005

Avatar of Jeff S
Jeff SFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005SQL
6 Comments1 Solution867 ViewsLast Modified:
Please note the SQL is handled dynamically by the SQL Server, therefore some items in my WHERE clauses will look odd to you PLEASE ignore this as its not an issue. I am getting the following error in my report and need some help.

Msg 8115, Level 16, State 8, Line 100
Arithmetic overflow error converting int to data type numeric.
The statement has been terminated.


/* Billing Status Report 11/22/03  
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
Avatar of shorvath
shorvathFlag of Canada imageshorvath

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answers