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: 414
  • Last Modified:

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

First and Foremost, the SQL is handled dynamically be the server, therefore, items in my WHERE clauses will look odd - Please disregard these.

Msg 8115, Level 16, State 8, Line 102
Arithmetic overflow error converting int to data type numeric.
The statement has been terminated.
/* Billing Status Report 
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 '2' = '1' OR
        1 IN ( 2 ) 
        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
                (
                  (
                    '2' IS NULL AND
                    '2' = '1' ) OR
                  (
                    '2' = '2' AND
                    '2' IS NULL ) OR
                  pv.BillStatus IN ( 2 ) ) 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'

Open in new window

0
Jeff S
Asked:
Jeff S
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
try this way
/* Billing Status Report 
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(10 , 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 '2' = '1' OR
        1 IN ( 2 ) 
        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
                (
                  (
                    '2' IS NULL AND
                    '2' = '1' ) OR
                  (
                    '2' = '2' AND
                    '2' IS NULL ) OR
                  pv.BillStatus IN ( 2 ) ) 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'

Open in new window

0
 
Jeff SAuthor Commented:
Thanks!!!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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