Hi, I have created a stored procedure which ran very slow. Not sure why? Could someone give a help please?
Thank you and really appreciate it!
USE [HHSQLDB]
GO
ALTER PROCEDURE [dbo].[sp_additionalMACReduction]
(
@ID_Payer INT,
@Transaction_SDate date,
@Transaction_EDate date
) AS
BEGIN
SELECT
Trn.ID_Item,
Clm.ID AS ID_Claim,
convert(varchar(10),Trn.Transaction_Date,20) AS Transaction_Date,
Trn.Transaction_Number,
Trn.Transaction_Type,
Payer.Insurance_Type,
ClHist.BatchPostingNumber,
Mem.Account,
Mem.FullName AS "Patient Name",
convert(varchar(10),Itm.From_Date,20) AS DOS,
Itm.Qty,
E.ID_Provider,
Prov.OrganizationName AS ProviderName,
Clm.ID_Payer,
Payer.ID_PayerType,
Payer.Name AS "Payer Name",
PayerTypeName.title AS "Title",
BillingCode.Code AS "Billing Code",
Prod.ProductCode,
Prod.ProductDescription,
ISNULL(ProdCat.CategoryDescription, '') AS ProductCategory,
ISNULL(ProcGroup.Procedure_Code, '') AS HCPC,
--total charge
ISNULL
(
(SELECT SUM(Amount) AS Expr1
FROM dbo.tbl_Transaction_File
WHERE (Void_Date IS NULL) AND (Transaction_Type = 1) AND (ID_Claim = Clm.ID) AND (ID_Item = Itm.ID)
)
, 0) AS Charge,
--payment
ISNULL
(
(SELECT SUM(Amount) AS Expr1
FROM dbo.tbl_Transaction_File
WHERE (Void_Date IS NULL) AND (Transaction_Type = 20 OR Transaction_Type = 21) AND (ID_Claim = Clm.ID) AND (ID_Item = Itm.ID)
)
, 0) AS Payment,
--allowed Amount:charge subtracts previous mac
(
--CONDITIONAL LOGIC
CASE
WHEN
-- charge
( ISNULL
(
(SELECT SUM(Amount) AS Expr1
FROM dbo.tbl_Transaction_File
WHERE (Void_Date IS NULL) AND (Transaction_Type = 1) AND (ID_Claim = Clm.ID) AND (ID_Item = Itm.ID)
), 0) -
--previous mac
(
(
ISNULL
(
(SELECT SUM(Amount)AS Expr1
FROM dbo.tbl_Transaction_File
WHERE (Void_Date IS NULL) AND (Transaction_Type = 10) AND (ID_Claim = Clm.ID) AND (ID_Item = Itm.ID)
), 0) -
Trn.Amount )*-1)
) > '0.00'
THEN
concat('-',
-- charge
( ISNULL
(
(SELECT SUM(Amount) AS Expr1
FROM dbo.tbl_Transaction_File
WHERE (Void_Date IS NULL) AND (Transaction_Type = 1) AND (ID_Claim = Clm.ID) AND (ID_Item = Itm.ID)
), 0) -
--previous mac
(
(
ISNULL
(
(SELECT SUM(Amount)AS Expr1
FROM dbo.tbl_Transaction_File
WHERE (Void_Date IS NULL) AND (Transaction_Type = 10) AND (ID_Claim = Clm.ID) AND (ID_Item = Itm.ID)
), 0) -
Trn.Amount )*-1) )
)
ELSE '0.00'
END
) AS Allowed,
--additional mac
ISNULL
(
(SELECT TOP (1) ClaimRemark1
FROM dbo.tbl_ERN_History AS ERH
WHERE (ClaimRemark1 > '') AND (ClaimNo = Clm.ID)), '') AS ClaimRemarkCode1, Trn.Amount AS "Additional MAC",
--Previous MAC
( ISNULL
(
(SELECT SUM(Amount)AS Expr1
FROM dbo.tbl_Transaction_File
WHERE (Void_Date IS NULL) AND (Transaction_Type = 10) AND (ID_Claim = Clm.ID) AND (ID_Item = Itm.ID)), 0) -
Trn.Amount) AS "Previous MAC",
--Sum of MAC
ISNULL
(
(SELECT SUM(Amount) AS Expr1
FROM dbo.tbl_Transaction_File
WHERE (Void_Date IS NULL) AND (Transaction_Type = 10) AND (ID_Claim = Clm.ID) AND (ID_Item = Itm.ID)), 0) AS "Sum of MAC",
--Balance is total charge subtracts sum of mac and
--charge
(ISNULL
(
(SELECT SUM(Amount) AS Expr1
FROM dbo.tbl_Transaction_File
WHERE (Void_Date IS NULL) AND (Transaction_Type = 1) AND (ID_Claim = Clm.ID) AND (ID_Item = Itm.ID)), 0) -
--allowed
( (
-- charge
ISNULL
(
(SELECT SUM(Amount) AS Expr1
FROM dbo.tbl_Transaction_File
WHERE (Void_Date IS NULL) AND (Transaction_Type = 1) AND (ID_Claim = Clm.ID) AND (ID_Item = Itm.ID)), 0) -
--previous mac
(
ISNULL
( (SELECT SUM(Amount)AS Expr1
FROM dbo.tbl_Transaction_File
WHERE (Void_Date IS NULL) AND (Transaction_Type = 10) AND (ID_Claim = Clm.ID) AND (ID_Item = Itm.ID)), 0) -
Trn.Amount
)*-1
))-
--payment
( ISNULL
(
(SELECT SUM(Amount) AS Expr1
FROM dbo.tbl_Transaction_File
WHERE (Void_Date IS NULL) AND (Transaction_Type = 20 OR
Transaction_Type = 21) AND (ID_Claim = Clm.ID) AND (ID_Item = Itm.ID)), 0)*-1)-
--ADJ
( ISNULL
(
(SELECT SUM(Amount) AS Expr1
FROM dbo.tbl_Transaction_File
WHERE (Void_Date IS NULL) AND (Transaction_Type = 40) AND (ID_Claim = Clm.ID) AND (ID_Item = Itm.ID)), 0)) -
--COI
(ISNULL
(
(SELECT SUM(Amount) AS Expr1
FROM dbo.tbl_Transaction_File
WHERE (Void_Date IS NULL) AND (Transaction_Type = 30) AND (ID_Claim = Clm.ID) AND (ID_Item = Itm.ID)), 0)) -
--DED
( ISNULL
(
(SELECT SUM(Amount) AS Expr1
FROM dbo.tbl_Transaction_File
WHERE (Void_Date IS NULL) AND (Transaction_Type = 35) AND (ID_Claim = Clm.ID) AND (ID_Item = Itm.ID)), 0))
) AS Balance,
--ADJ
ISNULL
(
(SELECT SUM(Amount) AS Expr1
FROM dbo.tbl_Transaction_File
WHERE (Void_Date IS NULL) AND (Transaction_Type = 40) AND (ID_Claim = Clm.ID) AND (ID_Item = Itm.ID)), 0) AS ADJ,
--Co-insurance
ISNULL
(
(SELECT SUM(Amount) AS Expr1
FROM dbo.tbl_Transaction_File
WHERE (Void_Date IS NULL) AND (Transaction_Type = 30) AND (ID_Claim = Clm.ID) AND (ID_Item = Itm.ID)), 0) AS COIs,
--Deduction, Check number, check date
ISNULL
(
(SELECT SUM(Amount) AS Expr1
FROM dbo.tbl_Transaction_File
WHERE (Void_Date IS NULL) AND (Transaction_Type = 35) AND (ID_Claim = Clm.ID) AND (ID_Item = Itm.ID)), 0) AS DED,
ClHist.CheckNumber,convert(varchar(10),
ClHist.CheckDate,20) AS CheckDate,
ClHist.DepositDate AS [Deposit Date]
FROM dbo.tbl_Transaction_File AS Trn
INNER JOIN dbo.tbl_Claim_History AS ClHist
ON Trn.ID_Claim = ClHist.ID_Claim
AND Trn.Transaction_Number = ClHist.Tx_Number
INNER JOIN dbo.tbl_Claims AS Clm
ON Trn.ID_Claim = Clm.ID
INNER JOIN dbo.tbl_Account_Member AS Mem
ON Clm.Account = Mem.Account
AND Clm.Member = Mem.Member
INNER JOIN dbo.tbl_Items AS Itm
ON Trn.ID_Item = Itm.ID
INNER JOIN dbo.tbl_Encounters AS E
ON Trn.ID_Encounter = E.ID
INNER JOIN dbo.tbl_Payer_Table AS Payer
ON Clm.ID_Payer = Payer.ID
INNER JOIN dbo.tbl_Provider_Table AS Prov
ON E.ID_Provider = Prov.ID
LEFT OUTER JOIN dbo.tbl_Procedure_Groups_Table AS ProcGroup
INNER JOIN dbo.tbl_Product_Table AS Prod
ON ProcGroup.ID_Billing_Code = Prod.ID_BillingCode
ON Payer.ID_Procedure_Group = ProcGroup.ID_Group_No
AND Itm.ID_Product = Prod.ID
LEFT OUTER JOIN dbo.tbl_ProductCategory_Table AS ProdCat
ON Prod.ID_ProductCategory = ProdCat.ID
LEFT OUTER JOIN dbo.tbl_Name_PayerTypes AS PayerTypeName
ON PayerTypeName.ID = Payer.ID_PayerType
LEFT OUTER JOIN dbo.tbl_Billing_Code_Table AS BillingCode
ON BillingCode.ID = Itm.ID_Billing_Code
WHERE (Trn.Void_Date IS NULL) AND (ClHist.HistoryRecType = 'R')
AND(Transaction_Date BETWEEN @Transaction_SDate and @Transaction_EDate)
AND(ID_Payer = @ID_Payer)
GROUP BY Trn.ID_Item,
Trn.Transaction_Date,
Trn.Transaction_Number,
Trn.Transaction_Type,
Trn.Amount,
Trn.Comment,
ClHist.CheckNumber,
ClHist.BatchPostingNumber,
Mem.Account,
Mem.FullName,
Itm.From_Date,
Itm.Qty,
E.ID_Provider,
Prov.OrganizationName,
Clm.ID_Payer,
Payer.Name,
Payer.ID_PayerType,
Payer.Insurance_Type,
PayerTypeName.title,
Prod.ProductCode,
Prod.ProductDescription,
BillingCode.Code,
ClHist.DepositDate,
ISNULL(ProdCat.CategoryDescription, ''),
ISNULL(ProcGroup.Procedure_Code, ''),
Clm.ID,
ClHist.CheckDate,
Itm.ID
HAVING (Trn.Transaction_Type = 10)
AND (NOT (Trn.Transaction_Number IN (
SELECT Transaction_Number
FROM dbo.tbl_Transaction_File AS tbl_Transaction_File_1
WHERE (Transaction_Type = 1)
)))
END