To avoid the subquery from a sql query

I have a query like this:

SELECT emppayslipgenerationdetails.EMPpayslipgenerationdetailid,
       emppayslipgenerationdetails.MonthNumber                 ,
       emppayslipgenerationdetails.YearNumber                  ,
       emppayslipgenerationdetails.FinancialYearID             ,
       emppayheadvalues.PayHeadMasterID                        ,
       emppayheadvalues.PayHeadValue                           ,
       ISNULL(
       ( SELECT  SUM(EPH.PayHeadValue) AS YTD
       FROM     emppayheadvalues EPH
       WHERE    EPH.EmppayslipgenerationdetailID<=emppayslipgenerationdetails.EMPpayslipgenerationdetailid
       AND      EPH.PayHeadMasterID              =emppayheadvalues.PayHeadMasterID
       AND      Emppayslipgenerationde开发者_开发知识库tailID IN
                (SELECT EmppayslipgenerationdetailID
                FROM    emppayslipgenerationdetails
                WHERE   FinancialYearID=2
                AND     EmpNumber      ='E0012'
                )
       GROUP BY PayheadMasterID
       )
       ,0)AS YTD
FROM   emppayheadvalues
       INNER JOIN emppayslipgenerationdetails
       ON     emppayheadvalues.EMPpayslipgenerationdetailid= emppayslipgenerationdetails.EMPpayslipgenerationdetailid
WHERE  emppayslipgenerationdetails.empnumber               ='E0012'
AND    emppayslipgenerationdetails.FinancialYearID         =2

How can I remove the subquery which affects the performance

The general approach to eliminate a subquery is to use join.

Just to prove the point - to eliminate the most inner query you should try more or less something like:

   SELECT   SUM(EPH.PayHeadValue) AS YTD
   FROM     emppayheadvalues EPH
   JOIN     emppayslipgenerationdetails
   WHERE    EPH.EmppayslipgenerationdetailID<=emppayslipgenerationdetails.EMPpayslipgenerationdetailid
   AND      EPH.PayHeadMasterID              =emppayheadvalues.PayHeadMasterID
   AND      EmppayslipgenerationdetailID     =emppayslipgenerationdetails.EmppayslipgenerationdetailID
   AND      EmpNumber                        ='E0012'
   AND      emppayslipgenerationdetails.FinancialYearID=2
   GROUP BY PayheadMasterID

But be aware that this could be more expensive in some cases than a subquery.