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; 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 COUNT(InvoiceWorkItemID) AS TotalRecords FROM #TotalInvoices; DROP TABLE #TotalInvoices; DROP TABLE #TempInvoices; DROP TABLE #TCInvoices; DROP TABLE #TCCInvoices; END
The issue is the result from select query and count from #totalinvoices are mismatching