claim details tables that could be updated only with the changes

Owned by Patrick Delcroix
Oct 06, 2020
5 min read

 

if not exists (select * from sysobjects where name='rptClaimDetails' and xtype='U') CREATE TABLE rptClaimDetails( ID integer NOT NULL IDENTITY(1,1) ,ItemServiceType varchar(255) , ServiceItemCode nvarchar(16) , ServiceItemName varchar(255) ,Gender varchar(16) ,DoBYear int ,INSUREE_ID varchar(32) ,IsHead varchar(16) ,ClaimServiceItemStatus varchar(16) ,QtyProvided int ,QtyApproved int ,PriceAsked DECIMAL (18, 2), PriceAdjusted DECIMAL (18, 2) ,PriceValuated DECIMAL (18, 2) ,PriceApproved DECIMAL (18, 2) ,ServiceItemRejectionReason varchar(255) ,DeductableAmount DECIMAL (18, 2) ,RemuneratedAmount DECIMAL (18, 2) ,ExceedCeilingAmount DECIMAL (18, 2) ,PriceOrigin char(1) ,ExceedCeilingAmountCategory varchar(16) ,LimitationValue int ,Limitation varchar(50) ,ClaimCode varchar(16) ,ClaimStatus varchar(16) ,Valuated DECIMAL (18, 2) ,Approved DECIMAL (18, 2) ,Claimed DECIMAL (18, 2) ,Reinsured DECIMAL (18, 2) ,DateProcessed date ,DateClaimed date ,ClaimRejectionReason varchar(255) ,ApprovalStatus varchar(16) ,ReviewStatus varchar(16) ,FeedbackStatus varchar(16) ,RunID integer ,SubmitStamp date ,ProcessStamp date ,Remunerated DECIMAL (18, 2) ,VisitType varchar(16) ,GuaranteeId varchar(20) ,ClaimCategory char(1) ,ProductCode varchar(16) ,ProductName varchar(255) ,LocationCode varchar(16) ,HFName varchar(255) ,HFLevel varchar(50) ,HFCareType varchar(50) ,LegalForm varchar(50) ,HFCode varchar(50) , mainICDCode varchar(16) , mainICDName varchar(255) , ICD1Code varchar(16) , ICD1Name varchar(255) , ICD2Code varchar(16) , ICD2Name varchar(255) , ICD3Code varchar(16) ,ICD3Name varchar(255) ,ICD4Code varchar(16) ,ICD4Name varchar(255) ,VillageName varchar(255) ,VillageCode varchar(16) ,WardName varchar(255) ,WardCode varchar(16) ,DistrictName varchar(255) ,DistrictCode varchar(16) ,RegionName varchar(255) ,RegionCode varchar(16) ,lastUpdate datetime PRIMARY KEY (ID) ); go DECLARE @lastUpdate date = (case (SELECT count(lastUpdate) FROM rptClaimDetails) when 0 then CONVERT(date, '2000-01-01',102) ELSE (SELECT TOP 1 lastUpdate FROM rptClaimDetails ORDER BY lastUpdate DESC) END ); DECLARE @stopDate date = CONVERT(date, '2021-01-01',102); WITH Locations as ( SELECT * FROM uvwLocations WHERE WardCode IS NULL AND VillageCode IS NULL AND DistrictCode IS NOT NULL AND DistrictCode IS NOT NULL )MERGE INTO rptClaimDetails AS c using (SELECT 'Item' AS ItemServiceType ,tblItems.ItemCode AS ServiceItemCode ,tblItems.ItemName AS ServiceItemName ,tblInsuree.Gender ,DATEPART(yyyy,tblInsuree.DOB) AS DoBYear , CONVERT(VARCHAR(32), HashBytes('MD5',CONCAT(tblInsuree.CHFID,'SSRSCHF')),2) AS INSUREE_ID ,tblInsuree.IsHead ,tblClaimItems.ClaimItemStatus AS [ClaimServiceItemStatus] ,tblClaimItems.QtyProvided ,tblClaimItems.QtyApproved ,tblClaimItems.PriceAsked ,tblClaimItems.PriceAdjusted ,tblClaimItems.PriceValuated ,tblClaimItems.PriceApproved ,tblClaimItems.RejectionReason AS [ServiceItemRejectionReason] ,tblClaimItems.DeductableAmount ,tblClaimItems.RemuneratedAmount ,tblClaimItems.ExceedCeilingAmount ,tblClaimItems.PriceOrigin ,tblClaimItems.ExceedCeilingAmountCategory ,tblClaimItems.LimitationValue ,tblClaimItems.Limitation ,tblClaim.ClaimCode ,tblClaim.ClaimStatus ,tblClaim.Valuated ,tblClaim.Approved ,tblClaim.Claimed ,tblClaim.Reinsured ,tblClaim.DateProcessed ,tblClaim.DateClaimed ,tblClaim.RejectionReason AS [ClaimRejectionReason] ,tblClaim.ApprovalStatus ,tblClaim.ReviewStatus ,tblClaim.FeedbackStatus ,tblClaim.RunID ,tblClaim.SubmitStamp ,tblClaim.ProcessStamp ,tblClaim.Remunerated ,tblClaim.ICDID1 ,tblClaim.ICDID2 ,tblClaim.ICDID3 ,tblClaim.ICDID4 ,tblClaim.VisitType ,tblClaim.GuaranteeId ,tblClaim.ClaimCategory ,tblProduct.ProductCode ,tblProduct.ProductName ,tblLocations.LocationCode ,tblHF.HFName ,tblHF.HFLevel ,tblHF.HFCareType ,tblHF.LegalForm ,tblHF.HFCode ,mainICD.ICDCode as mainICDCode ,mainICD.ICDName as mainICDName ,ICD1.ICDCode as ICD1Code ,ICD1.ICDName as ICD1Name ,ICD2.ICDCode as ICD2Code ,ICD2.ICDName as ICD2Name ,ICD3.ICDCode as ICD3Code ,ICD3.ICDName as ICD3Name ,ICD4.ICDCode as ICD4Code ,ICD4.ICDName as ICD4Name ,l.DistrictName ,l.DistrictCode ,l.RegionName ,l.RegionCode ,GETDATE() as lastUpdate FROM tblClaim RIGHT OUTER JOIN tblClaimItems ON tblClaimItems.ClaimID = tblClaim.ClaimID INNER JOIN tblInsuree ON tblClaim.InsureeID = tblInsuree.InsureeID INNER JOIN tblItems ON tblClaimItems.ItemID = tblItems.ItemID INNER JOIN tblProduct ON tblClaimItems.ProdID = tblProduct.ProdID INNER JOIN tblHF ON tblClaim.HFID = tblHF.HFID INNER JOIN tblLocations ON tblHF.LocationId = tblLocations.LocationId INNER JOIN Locations l ON tblLocations.LocationCode = l.DistrictCode INNER JOIN tblICDCodes as mainICD ON tblClaim.ICDID = mainICD.ICDID LEFT JOIN tblICDCodes as ICD1 ON tblClaim.ICDID1 = ICD1.ICDID LEFT JOIN tblICDCodes as ICD2 ON tblClaim.ICDID2 = ICD2.ICDID LEFT JOIN tblICDCodes as ICD3 ON tblClaim.ICDID3 = ICD3.ICDID LEFT JOIN tblICDCodes as ICD4 ON tblClaim.ICDID4 = ICD4.ICDID WHERE tblClaim.ValidityTo IS NULL and tblClaimItems.ValidityTo IS NULL and tblClaim.[ValidityFrom] BETWEEN @lastUpdate AND @stopDate UNION SELECT 'Service' AS ItemServiceType , tblServices.ServCode AS ServiceItemCode ,tblServices.ServName AS ServiceItemName ,tblInsuree.Gender ,DATEPART(yyyy,tblInsuree.DOB) AS DoBYear , CONVERT(VARCHAR(32), HashBytes('MD5',CONCAT(tblInsuree.CHFID,'SSRSCHF')),2) AS INSUREE_ID ,tblInsuree.IsHead ,tblClaimServices.ClaimServiceStatus AS [ClaimServiceItemStatus] ,tblClaimServices.QtyProvided ,tblClaimServices.QtyApproved ,tblClaimServices.PriceAsked ,tblClaimServices.PriceAdjusted ,tblClaimServices.PriceValuated ,tblClaimServices.PriceApproved ,tblClaimServices.RejectionReason AS [ServiceItemRejectionReason] ,tblClaimServices.DeductableAmount ,tblClaimServices.RemuneratedAmount ,tblClaimServices.ExceedCeilingAmount ,tblClaimServices.PriceOrigin ,tblClaimServices.ExceedCeilingAmountCategory ,tblClaimServices.LimitationValue ,tblClaimServices.Limitation ,tblClaim.ClaimCode ,tblClaim.ClaimStatus ,tblClaim.Valuated ,tblClaim.Approved ,tblClaim.Claimed ,tblClaim.Reinsured ,tblClaim.DateProcessed ,tblClaim.DateClaimed ,tblClaim.RejectionReason AS [ClaimRejectionReason] ,tblClaim.ApprovalStatus ,tblClaim.ReviewStatus ,tblClaim.FeedbackStatus ,tblClaim.RunID ,tblClaim.SubmitStamp ,tblClaim.ProcessStamp ,tblClaim.Remunerated ,tblClaim.ICDID1 ,tblClaim.ICDID2 ,tblClaim.ICDID3 ,tblClaim.ICDID4 ,tblClaim.VisitType ,tblClaim.GuaranteeId ,tblClaim.ClaimCategory ,tblProduct.ProductCode ,tblProduct.ProductName ,tblLocations.LocationCode ,tblHF.HFName ,tblHF.HFLevel ,tblHF.HFCareType ,tblHF.LegalForm ,tblHF.HFCode ,mainICD.ICDCode as mainICDCode ,mainICD.ICDName as mainICDName ,ICD1.ICDCode as ICD1Code ,ICD1.ICDName as ICD1Name ,ICD2.ICDCode as ICD2Code ,ICD2.ICDName as ICD2Name ,ICD3.ICDCode as ICD3Code ,ICD3.ICDName as ICD3Name ,ICD4.ICDCode as ICD4Code ,ICD4.ICDName as ICD4Name ,l.DistrictName ,l.DistrictCode ,l.RegionName ,l.RegionCode ,GETDATE() as lastUpdate FROM tblClaim RIGHT OUTER JOIN tblClaimServices ON tblClaimServices.ClaimID = tblClaim.ClaimID INNER JOIN tblInsuree ON tblClaim.InsureeID = tblInsuree.InsureeID INNER JOIN tblServices ON tblClaimServices.ServiceID = tblServices.ServiceID INNER JOIN tblProduct ON tblClaimServices.ProdID = tblProduct.ProdID INNER JOIN tblHF ON tblClaim.HFID = tblHF.HFID INNER JOIN tblLocations ON tblHF.LocationId = tblLocations.LocationId INNER JOIN Locations l ON tblLocations.LocationCode = l.DistrictCode INNER JOIN tblICDCodes as mainICD ON tblClaim.ICDID = mainICD.ICDID LEFT JOIN tblICDCodes as ICD1 ON tblClaim.ICDID1 = ICD1.ICDID LEFT JOIN tblICDCodes as ICD2 ON tblClaim.ICDID2 = ICD2.ICDID LEFT JOIN tblICDCodes as ICD3 ON tblClaim.ICDID3 = ICD3.ICDID LEFT JOIN tblICDCodes as ICD4 ON tblClaim.ICDID4 = ICD4.ICDID WHERE tblClaim.ValidityTo IS NULL and tblClaimServices.ValidityTo IS NULL AND tblClaim.[ValidityFrom] BETWEEN @lastUpdate AND @stopDate) as u ON (u.HFCode = c.HFCode and u.ClaimCode = c.ClaimCode AND u.ServiceItemCode = c.ServiceItemCode) when MATCHED THEN update SET c.ItemServiceType = u.ItemServiceType ,c.ServiceItemCode = u.ServiceItemCode ,c.ServiceItemName = u.ServiceItemName ,c.Gender = u.Gender ,c.DoBYear = u.DoBYear ,c.INSUREE_ID = u.INSUREE_ID ,c.IsHead = u.IsHead ,c.ClaimServiceItemStatus = u.ClaimServiceItemStatus ,c.QtyProvided = u.QtyProvided ,c.QtyApproved = u.QtyApproved ,c.PriceAsked = u.PriceAsked ,c.PriceAdjusted = u.PriceAdjusted ,c.PriceValuated = u.PriceValuated ,c.PriceApproved = u.PriceApproved ,c.ServiceItemRejectionReason = u.ServiceItemRejectionReason ,c.DeductableAmount = u.DeductableAmount ,c.RemuneratedAmount = u.RemuneratedAmount ,c.ExceedCeilingAmount = u.ExceedCeilingAmount ,c.PriceOrigin = u.PriceOrigin ,c.ExceedCeilingAmountCategory = u.ExceedCeilingAmountCategory ,c.LimitationValue = u.LimitationValue ,c.Limitation = u.Limitation ,c.ClaimCode = u.ClaimCode ,c.ClaimStatus = u.ClaimStatus ,c.Valuated = u.Valuated ,c.Approved = u.Approved ,c.Claimed = u.Claimed ,c.Reinsured = u.Reinsured ,c.DateProcessed = u.DateProcessed ,c.DateClaimed = u.DateClaimed ,c.ClaimRejectionReason = u.ClaimRejectionReason ,c.ApprovalStatus = u.ApprovalStatus ,c.ReviewStatus = u.ReviewStatus ,c.FeedbackStatus = u.FeedbackStatus ,c.RunID = u.RunID ,c.SubmitStamp = u.SubmitStamp ,c.ProcessStamp = u.ProcessStamp ,c.Remunerated = u.Remunerated ,c.VisitType = u.VisitType ,c.GuaranteeId = u.GuaranteeId ,c.ClaimCategory = u.ClaimCategory ,c.ProductCode = u.ProductCode ,c.ProductName = u.ProductName ,c.LocationCode = u.LocationCode ,c.HFName = u.HFName ,c.HFLevel = u.HFLevel ,c.HFCareType = u.HFCareType ,c.LegalForm = u.LegalForm ,c.HFCode = u.HFCode ,c.mainICDCode = u.mainICDCode ,c.mainICDName = u.mainICDName ,c.ICD1Code = u.ICD1Code ,c.ICD1Name = u.ICD1Name ,c.ICD2Code = u.ICD2Code ,c.ICD2Name = u.ICD2Name ,c.ICD3Code = u.ICD3Code ,c.ICD3Name = u.ICD3Name ,c.ICD4Code = u.ICD4Code ,c.ICD4Name = u.ICD4Name ,c.DistrictName = u.DistrictName ,c.DistrictCode = u.DistrictCode ,c.RegionName = u.RegionName ,c.RegionCode = u.RegionCode ,c.lastUpdate = u.lastUpdate WHEN NOT matchED BY TARGET THEN INSERT ( ItemServiceType , ServiceItemCode , ServiceItemName ,Gender ,DoBYear ,INSUREE_ID ,IsHead ,ClaimServiceItemStatus ,QtyProvided ,QtyApproved ,PriceAsked ,PriceAdjusted ,PriceValuated ,PriceApproved ,ServiceItemRejectionReason ,DeductableAmount ,RemuneratedAmount ,ExceedCeilingAmount ,PriceOrigin ,ExceedCeilingAmountCategory ,LimitationValue ,Limitation ,ClaimCode ,ClaimStatus ,Valuated ,Approved ,Claimed ,Reinsured ,DateProcessed ,DateClaimed ,ClaimRejectionReason ,ApprovalStatus ,ReviewStatus ,FeedbackStatus ,RunID ,SubmitStamp ,ProcessStamp ,Remunerated ,VisitType ,GuaranteeId ,ClaimCategory ,ProductCode ,ProductName ,LocationCode ,HFName ,HFLevel ,HFCareType ,LegalForm ,HFCode , mainICDCode , mainICDName , ICD1Code , ICD1Name , ICD2Code , ICD2Name , ICD3Code ,ICD3Name ,ICD4Code ,ICD4Name ,DistrictName ,DistrictCode ,RegionName ,RegionCode ,lastUpdate ) VALUES (ItemServiceType ,u.ServiceItemCode ,u.ServiceItemName ,u.Gender ,u.DoBYear ,u.INSUREE_ID ,u.IsHead ,u.ClaimServiceItemStatus ,u.QtyProvided ,u.QtyApproved ,u.PriceAsked , ,u.PriceAdjusted ,u.PriceValuated ,u.PriceApproved ,u.ServiceItemRejectionReason ,u.DeductableAmount ,u.RemuneratedAmount ,u.ExceedCeilingAmount ,u.PriceOrigin ,u.ExceedCeilingAmountCategory ,u.LimitationValue ,u.Limitation ,u.ClaimCode ,u.ClaimStatus ,u.Valuated ,u.Approved ,u.Claimed ,u.Reinsured ,u.DateProcessed ,u.DateClaimed ,u.ClaimRejectionReason ,u.ApprovalStatus ,u.ReviewStatus ,u.FeedbackStatus ,u.RunID ,u.SubmitStamp ,u.ProcessStamp ,u.Remunerated ,u.VisitType ,u.GuaranteeId ,u.ClaimCategory ,u.ProductCode ,u.ProductName ,u.LocationCode ,u.HFName ,u.HFLevel ,u.HFCareType ,u.LegalForm ,u.HFCode ,u.mainICDCode ,u.mainICDName ,u.ICD1Code ,u.ICD1Name ,u.ICD2Code ,u.ICD2Name ,u.ICD3Code ,u.ICD3Name ,u.ICD4Code ,u.ICD4Name ,u.DistrictName ,u.DistrictCode ,u.RegionName ,u.RegionCode ,u.lastUpdate);

 

Did you encounter a problem or do you have a suggestion?

Please contact our Service Desk


This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. https://creativecommons.org/licenses/by-sa/4.0/