SQLTeam.com | Weblogs | Forums

How to reuse subquery in stored procedure in sql


#1

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