data discrepancy issue

Sign in to answer this question.

• 

  Jayraj Chhaya2y agoPosted Dec 19, 2023, 5:59 AM

  Problem

  The issue with the code is that the result from the select query and the count from the #totalinvoices table are mismatching. This means that the count of records in the #totalinvoices table is not accurate.

  Cause

  The cause of the issue is that the count of records in the #totalinvoices table is not being updated correctly. This could be due to an error in the logic of the code or a problem with the data being inserted into the table.

  Solution

  To fix the issue, we need to ensure that the count of records in the #totalinvoices table is accurate. We can do this by modifying the code to update the count after inserting the records into the table.

  Here's the modified code:

  ALTER PROCEDURE [dbo].[spGetPerCaseInvoiceWorkItemsForStaff]
  @StatusID INT,
  @BranchID INT,
  @PageNumber INT = 1,
  @PageSize INT = 50
  AS
  BEGIN
  SET NOCOUNT ON
      CREATE TABLE #TotalInvoices(InvoiceWorkItemID INT, ClientID INT, ContractClaimStatusTypeID INT, CreatedDate DATETIME, ModifiedDate DATETIME, ApprovedDate DATETIME, CATStaffApprovedDate DATETIME);
      DECLARE @strSql NVARCHAR(MAX);
      DECLARE @strSqlClouse NVARCHAR(MAX);
      IF @StatusID IS NULL
      BEGIN
          SET @StatusID = 3;
      END
      SET @strSqlClouse = CONCAT(N' WHERE ISNULL(IsDeleted, 0) = 0 AND ContractClaimStatusTypeID = ', @StatusID);
      IF @BranchID IS NOT NULL AND @BranchID <> 0
      BEGIN
          SET @strSqlClouse = CONCAT(@strSqlClouse, ' AND BranchID = ', @BranchID);
      END
      SET @strSql = N'INSERT INTO #TotalInvoices (InvoiceWorkItemID, ClientID, ContractClaimStatusTypeID, CreatedDate, ModifiedDate, ApprovedDate, CATStaffApprovedDate)'
                  + 'SELECT InvoiceWorkItemID, ClientID, ContractClaimStatusTypeID, CreatedDate, ModifiedDate, ApprovedDate, CATStaffApprovedDate FROM [dbo].[InvoiceWorkItem] '
                  + @strSqlClouse + ' UNION ALL '
                  + 'SELECT ContractInvoicePerCaseID, ClientID, ContractClaimStatusTypeID, CreatedDate, ModifiedDate, ApprovedDate, CATStaffApprovedDate FROM [dbo].[ContractInvoicePerCase] '
                  + @strSqlClouse;
      EXEC sp_executesql @strSql;
      
      -- Update the count of records in #TotalInvoices table
      DECLARE @TotalRecords INT;
      SELECT @TotalRecords = COUNT(InvoiceWorkItemID) FROM #TotalInvoices;
      
      SELECT * INTO #TempInvoices FROM #TotalInvoices T
      ORDER BY
      CASE WHEN ContractClaimStatusTypeID = 3 THEN ModifiedDate
      WHEN ContractClaimStatusTypeID = 4 THEN ApprovedDate
      WHEN ContractClaimStatusTypeID = 5 THEN CATStaffApprovedDate
      ELSE CreatedDate END DESC
      OFFSET((@PageNumber - 1) * @PageSize) ROWS FETCH NEXT(@PageSize) ROWS ONLY;
      SELECT I.TimeCardId, I.ClaimId, I.InvoiceNumber, I.InvoiceTypeID, IPST.InvoiceProcessStatusTypeName AS ClaimStatus
      INTO #TCInvoices
      FROM (SELECT TimeCardId, InvoiceId, InvoiceNumber, ClaimId, InvoiceTypeId, InvoiceSourceTypeID,  ROW_NUMBER() OVER(PARTITION BY TimeCardId ORDER BY CreatedDate DESC) RN FROM [dbo].[Invoice] WHERE InvoiceSourceTypeID = 2) I
      LEFT JOIN (SELECT *, ROW_NUMBER() OVER(PARTITION BY InvoiceId, TimeCardId ORDER BY CreatedDate DESC) RN FROM [dbo].[InvoiceProcessLog] WHERE InvoiceSourceTypeID = 2) IPL
      ON IPL.InvoiceId = I.InvoiceId AND IPL.TimeCardId = I.TimeCardId AND IPL.InvoiceSourceTypeID = I.InvoiceSourceTypeID AND I.RN = 1 AND IPL.RN = 1
      LEFT JOIN [dbo].[InvoiceProcessStatusType] IPST
      ON IPL.InvoiceProcessStatusTypeId = IPST.InvoiceProcessStatusTypeId
      WHERE I.RN = 1;
      SELECT I.TimeCardId, I.ClaimId, I.InvoiceTypeID, I.InvoiceNumber, IPST.InvoiceProcessStatusTypeName AS ClaimStatus
      INTO #TCCInvoices
      FROM (SELECT TimeCardId, InvoiceId, InvoiceNumber, ClaimId, InvoiceTypeId, InvoiceSourceTypeID,  ROW_NUMBER() OVER(PARTITION BY TimeCardId ORDER BY CreatedDate DESC) RN FROM [dbo].[Invoice] WHERE InvoiceSourceTypeID = 5) I
      LEFT JOIN (SELECT *, ROW_NUMBER() OVER(PARTITION BY InvoiceId, TimeCardId ORDER BY CreatedDate DESC) RN FROM [dbo].[InvoiceProcessLog] WHERE InvoiceSourceTypeID = 5) IPL
      ON IPL.InvoiceId = I.InvoiceId AND IPL.TimeCardId = I.TimeCardId AND IPL.InvoiceSourceTypeID = I.InvoiceSourceTypeID AND I.RN = 1 AND IPL.RN = 1
      LEFT JOIN [dbo].[InvoiceProcessStatusType] IPST
      ON IPL.InvoiceProcessStatusTypeId = IPST.InvoiceProcessStatusTypeId
      WHERE I.RN = 1;
      SELECT *
      FROM
      (
          SELECT IWI.InvoiceWorkItemID AS InvoiceWorkItemID, IWI.ClientContractID, IWI.DeploymentReportId, IWI.WorkEnvironmentTypeID,WET.WorkEnvironmentTypeName,IWI.ClaimNumber,IWI.DateInspected,IWI.GrossLoss,IWI.City,IWI.ProvinceID,PR.ProvinceName
          ,CT.CountryID,CT.CountryName,IWI.PostalCode,IWI.LocationAddress,LocationAddress2 = CAST(NULL AS NVARCHAR(1000)),IWI.ContractClaimStatusTypeID,ST.ContractClaimStatusTypeName,IWI.IsConMasterPolicy,IWI.IsConUnitPolicy,
          IWI.ContactID,RTRIM(LTRIM(ISNULL(VIWI.FirstName,''))) FirstName, RTRIM(LTRIM(ISNULL(VIWI.MiddleName,''))) MiddleName, RTRIM(LTRIM(ISNULL(VIWI.LastName,''))) LastName, VIWI.EmployeeNumber,
          IWI.BranchID, VIWI.BranchName, VIWI.Code AS BranchCode, VIWI.Code AS BranchNumber,(ISNULL(IWI.policyholderLastName,'')) AS policyholderLastName , (ISNULL(IWI.Apartment,''))  Apartment,
          (ISNULL(IWI.Building,'')) Building ,(ISNULL(IWI.IsSupplimental,0)) IsSupplimental ,
          (ISNULL(IWI.Supplimental,''))  Supplimental, CAST(NULL AS NVARCHAR(255)) AS PolicyNumber, CAST(0 AS INT) AS ContactAssignmentID,
          IWI.ClientID,VIWI.ClientName,STUFF((SELECT  DISTINCT (', '+ISNULL((LTRIM(RTRIM(CTMM.FirstName)) +' '+ LTRIM(RTRIM(CTMM.LastName))),'')) FROM
          [dbo].vw_BranchMgrSummary CTMM  WHERE CTMM.ContactID=VIWI.ManagerContactID AND IWI.BranchID = CTMM.BranchID FOR XML PATH('')),1,1,'')BranchManagers,
          Comment= '',InvoiceAmount=(SELECT ISNULL(SUM(IDWI.InvoiceAmount),0) FROM [dbo].InvoiceWorkItemDetail IDWI WITH(NOLOCK) WHERE IDWI.InvoiceWorkItemID=IWI.InvoiceWorkItemID)
          ,InvoiceAmount_Suppliment=(SELECT ISNULL(SUM(IDWI.InvoiceAmount),0) FROM [dbo].InvoiceWorkItemDetail_Suppliment IDWI WITH(NOLOCK) WHERE IDWI.InvoiceWorkItemID=IWI.InvoiceWorkItemID)
          ,(LTRIM(RTRIM(ISNULL(CTM.FirstName,''))) + ' ' + LTRIM(RTRIM(ISNULL(CTM.LastName,'')))) AS ApprovedBy,IWI.ApprovedDate
          ,(LTRIM(RTRIM(ISNULL(CTAU.FirstName,''))) + ' ' + LTRIM(RTRIM(ISNULL(CTAU.LastName,'')))) AS CATStaffApprovedBy,IWI.CATStaffApprovedDate
          ,IWI.isClaimToCMS,VIWI.AssignmentDate, VIWI.CompletionDate,IWI.wastheLossSettled,IWI.isTotalLoss,IWI.PolicyholderName,VIWI.AliasName
          ,IsWaterBackUp = CAST(NULL AS BIT),WaterBackUpPolicyLimit = CAST(NULL AS money)
          ,IsCondominium=CAST((CASE WHEN IWI.IsConMasterPolicy=1 OR IWI.IsConUnitPolicy=1 THEN 1 ELSE  0  END ) AS BIT)
          ,IsMarine=CAST((CASE WHEN VIWI.CcompCode='MFS' THEN  1 ELSE  0 END ) AS BIT)
          ,ContractPerilID = CAST(NULL AS INT),ContractPerilName = CAST(NULL AS NVARCHAR(1000)),DateOfLoss = CAST(NULL AS DATETIME),OrigGrossAmountLoss = CAST(NULL AS DECIMAL(18,3)), IWI.CreatedDate,IsExpressClaim=CAST(NULL AS BIT), IsDifferentSUPPAdjuster=CAST(NULL AS BIT),ContractInvoicePerCaseCWPTypeID = CAST(NULL AS INT),CWPName = CAST(NULL AS NVARCHAR(500))
          ,IWI.ModifiedDate,ActivityLogCount = (SELECT COUNT(1) FROM [dbo].[InvoiceActivityLog] TRHH
          WHERE TRHH.InvoiceWorkItemID=IWI.InvoiceWorkItemID AND ISNULL(TRHH.InvoiceSourceTypeID, 2) = 2 AND TRHH.ContractClaimStatusTypeID=2)
          ,(CASE WHEN TINC.InvoiceTypeId = 2 THEN 'Credited' ELSE ISNULL(TINC.ClaimStatus, 'Pending') END) AS ClaimStatus
          ,TINC.ClaimID CMSClaimID
          ,TINC.InvoiceNumber CMSInvoiceNumber
          FROM (SELECT InvoiceWorkItemID FROM #TempInvoices WHERE ClientID = 2) TI
          INNER JOIN [dbo].InvoiceWorkItem IWI WITH(NOLOCK) ON TI.InvoiceWorkItemID = IWI.InvoiceWorkItemID
          INNER JOIN [dbo].[vw_InvoiceWorkItemSummary]  VIWI ON VIWI.ContactID=IWI.ContactID AND VIWI.BranchID=IWI.BranchID AND VIWI.DeploymentReportID=IWI.DeploymentReportID
          INNER JOIN [dbo].[WorkEnvironmentType] WET WITH(NOLOCK) ON WET.WorkEnvironmentTypeID=IWI.WorkEnvironmentTypeID
          INNER JOIN [dbo].[ContractClaimStatusType] ST WITH(NOLOCK) ON ST.ContractClaimStatusTypeID=IWI.ContractClaimStatusTypeID
          LEFT JOIN [dbo].[Country] CT WITH(NOLOCK) ON CT.CountryID=IWI.CountryID
          LEFT JOIN [dbo].[Province] PR WITH(NOLOCK) ON PR.ProvinceID=IWI.ProvinceID
          LEFT JOIN [dbo].[Contact] CTM ON IWI.ApprovedBy = CTM.ContactID
          LEFT JOIN [dbo].[ApplicationUser] CTAU ON IWI.CATStaffApprovedBy = CTAU.ApplicationUserID
          LEFT JOIN #TCInvoices TINC ON IWI.InvoiceWorkItemID = TINC.TimeCardId
          UNION ALL
          SELECT IWI.ContractInvoicePerCaseID AS InvoiceWorkItemID, IWI.ClientContractID, DeploymentReportId = CAST(NULL AS INT), IWI.WorkEnvironmentTypeID,WET.WorkEnvironmentTypeName,
          --CONCAT(ISNULL(IWI.ClaimNumber, ''), ((CASE WHEN IsSupplimental = 1 THEN CONCAT(' (Supp: ', Supplimental, ')') ELSE '' END))) AS ClaimNumber
          IWI.ClaimNumber,IWI.DateInspected,GrossLoss = CAST(IWI.GrossLoss AS DECIMAL(18,3)),IWI.City,IWI.ProvinceID,PR.ProvinceName         ,CT.CountryID,CT.CountryName,IWI.PostalCode,IWI.LocationAddress,IWI.LocationAddress2,IWI.ContractClaimStatusTypeID,ST.ContractClaimStatusTypeName,IsConMasterPolicy = CAST(NULL AS BIT),IsConUnitPolicy = CAST(NULL AS BIT),
          IWI.ContactID,RTRIM(LTRIM(ISNULL(VIWI.FirstName,''))) FirstName, RTRIM(LTRIM(ISNULL(VIWI.MiddleName,''))) MiddleName, RTRIM(LTRIM(ISNULL(VIWI.LastName,''))) LastName, VIWI.EmployeeNumber,
          IWI.BranchID, VIWI.BranchName, VIWI.Code AS BranchCode, VIWI.Code AS BranchNumber,(ISNULL(IWI.PolicyholderLastName,'')) AS policyholderLastName , (ISNULL(IWI.Apartment,''))  Apartment,
          (ISNULL(IWI.Building,'')) Building ,(ISNULL(IWI.IsSupplimental,0)) IsSupplimental ,
          (ISNULL(IWI.Supplimental,''))  Supplimental, IWI.PolicyNumber, IWI.ContactAssignmentID,
          IWI.ClientID,VIWI.ClientName,STUFF((SELECT  DISTINCT (', '+ISNULL((LTRIM(RTRIM(CTMM.FirstName)) +' '+ LTRIM(RTRIM(CTMM.LastName))),'')) FROM
          [dbo].vw_BranchMgrSummary CTMM  WHERE CTMM.ContactID=VIWI.ManagerContactID AND IWI.BranchID = CTMM.BranchID  FOR XML PATH('')),1,1,'')BranchManagers,
          Comment='',InvoiceAmount=(SELECT ISNULL(SUM(IDWI.InvoiceAmount),0) FROM [dbo].[ContractInvoicePerCaseDetail] IDWI WITH(NOLOCK) WHERE IDWI.ContractInvoicePerCaseID=IWI.ContractInvoicePerCaseID)
          ,InvoiceAmount_Suppliment=(SELECT ISNULL(SUM(IDWI.InvoiceAmount),0) FROM [dbo].[ContractInvoicePerCaseDetailSupplimental] IDWI WITH(NOLOCK) WHERE IDWI.ContractInvoicePerCaseID=IWI.ContractInvoicePerCaseID)
          ,(LTRIM(RTRIM(ISNULL(CTM.FirstName,''))) + ' ' + LTRIM(RTRIM(ISNULL(CTM.LastName,'')))) AS ApprovedBy,IWI.ApprovedDate
          ,(LTRIM(RTRIM(ISNULL(CTAU.FirstName,''))) + ' ' + LTRIM(RTRIM(ISNULL(CTAU.LastName,'')))) AS CATStaffApprovedBy,IWI.CATStaffApprovedDate
          ,isClaimToCMS = CAST(NULL AS BIT),VIWI.AssignmentDate, VIWI.CompletionDate,wastheLossSettled = CAST(NULL AS BIT),isTotalLoss = CAST(NULL AS BIT),IWI.PolicyholderName,VIWI.AliasName
          ,IWI.IsWaterBackUp,IWI.WaterBackUpPolicyLimit
          ,IsCondominium = CAST(NULL AS BIT),IsMarine = CAST(NULL AS BIT),IWI.ContractPerilID, CP.PerilName AS ContractPerilName,IWI.DateOfLoss,OrigGrossAmountLoss = CAST(IWI.OrigGrossAmountLoss AS DECIMAL(18,3)), IWI.CreatedDate,IWI.IsExpressClaim, IWI.IsDifferentSUPPAdjuster,IWI.ContractInvoicePerCaseCWPTypeID ,CWPName =(SELECT TOP 1 CWPName FROM ContractInvoicePerCaseCWPType WHERE ContractInvoicePerCaseCWPTypeID=iwi.ContractInvoicePerCaseCWPTypeID)
          ,IWI.ModifiedDate,ActivityLogCount = (SELECT COUNT(1) FROM [dbo].[InvoiceActivityLog] TRHH
          WHERE TRHH.InvoiceWorkItemID=IWI.ContractInvoicePerCaseID AND ISNULL(TRHH.InvoiceSourceTypeID, 2) = 5 AND TRHH.ContractClaimStatusTypeID=2)
          ,(CASE WHEN TINC.InvoiceTypeId = 2 THEN 'Credited' ELSE ISNULL(TINC.ClaimStatus, 'Pending') END) AS ClaimStatus
          ,TINC.ClaimID CMSClaimID
          ,TINC.InvoiceNumber CMSInvoiceNumber
          FROM (SELECT InvoiceWorkItemID FROM #TempInvoices WHERE ClientID <> 2)  TI
          INNER JOIN [dbo].[ContractInvoicePerCase] IWI WITH(NOLOCK) ON TI.InvoiceWorkItemID = IWI.ContractInvoicePerCaseID
          INNER JOIN [dbo].[vw_ContractInvoicePerCaseSummary]  VIWI ON VIWI.ContactID=IWI.ContactID AND VIWI.BranchID=IWI.BranchID AND VIWI.ContactAssignmentID = IWI.ContactAssignmentID
          INNER JOIN [dbo].[WorkEnvironmentType] WET WITH(NOLOCK) ON WET.WorkEnvironmentTypeID=IWI.WorkEnvironmentTypeID
          INNER JOIN [dbo].[ContractClaimStatusType] ST WITH(NOLOCK) ON ST.ContractClaimStatusTypeID=IWI.ContractClaimStatusTypeID
          LEFT JOIN [dbo].[Country] CT WITH(NOLOCK) ON CT.CountryID=IWI.CountryID
          LEFT JOIN [dbo].[Province] PR WITH(NOLOCK) ON PR.ProvinceID=IWI.ProvinceID
          LEFT JOIN [dbo].[ContractPeril] CP WITH(NOLOCK) ON CP.ContractPerilID = IWI.ContractPerilID
          LEFT JOIN [dbo].[Contact] CTM ON IWI.ApprovedBy = CTM.ContactID
          LEFT JOIN [dbo].[ApplicationUser] CTAU ON IWI.CATStaffApprovedBy = CTAU.ApplicationUserID
          LEFT JOIN #TCCInvoices TINC ON IWI.ContractInvoicePerCaseID = TINC.TimeCardId
      ) T;
      
      SELECT @TotalRecords AS TotalRecords; -- Return the accurate count of records
      
      DROP TABLE #TotalInvoices;
      DROP TABLE #TempInvoices;
      DROP TABLE #TCInvoices;
      DROP TABLE #TCCInvoices;
  END
  

   
• 

  Amit Mohanty2y agoPosted Dec 19, 2023, 5:37 AM

  Without specific data for reference, recreating the issue and proposing viable solutions becomes problematic.