lundi 13 juin 2016

Alternate way of Exists with Having clause

Hi pls refer the below query

  SELECT PA.PERSON_ID,
   PA.PERSON_ACCOUNT_ID,
   DR.DISBURSEMENT_REQUEST_ID,
   SUM
   (  
        ISNULL(CD.EE_PRE_TAX_AMT,0) + 
        ISNULL(CD.EE_ADDL_PRE_TAX_AMT,0)
   ) AS EE_PRE_TAX_CONTRIB,
   DD.PRE_TAX_AMOUNT AS DISB_DTL_PRE_TAX_AMOUNT,
   SUM
   (     ISNULL(CD.EE_POST_TAX_AMT,0) + 
         ISNULL(CD.EE_ADDL_POST_TAX_AMT,0)
   ) AS EE_POST_TAX_CONTRIB,
   DD.POST_TAX_AMOUNT AS DISB_DTL_POST_TAX_AMOUNT
   FROM SGT_DISBURSEMENT_REQUEST DR
   INNER JOIN SGT_DISBURSEMENT_DETAIL DD ON DR.DISBURSEMENT_REQUEST_ID = DD.DISBURSEMENT_REQUEST_ID 
   INNER JOIN SGT_PERSON_ACCOUNT PA ON PA.PERSON_ACCOUNT_ID = DR.PERSON_ACCOUNT_ID
   INNER JOIN SGT_CONTRIB_DTL CD ON CD.PERSON_ACCOUNT_ID = PA.PERSON_ACCOUNT_ID 
   WHERE DR.REQUEST_CATEGORY_VALUE = 'REFD' AND
         DD.DETAIL_CATEGORY_VALUE = 'REFD' AND
         CD.STATUS_VALUE = 'VALD' AND
         CD.POSTED_DATE <= DR.ACCEPTED_TO_PAYROLL_DATE AND
         DR.DISBURSEMENT_STATUS_VALUE <> 'CANL' AND
      EXISTS
        (
            SELECT 1 FROM SGT_DISBURSEMENT_REQUEST SDR1 
            INNER JOIN SGT_DISBURSEMENT_DETAIL SDD1 ON SDD1.DISBURSEMENT_REQUEST_ID = SDR1.DISBURSEMENT_REQUEST_ID
            WHERE SDR1.PERSON_ACCOUNT_ID = DR.PERSON_ACCOUNT_ID and SDD1.DETAIL_CATEGORY_VALUE = DD.DETAIL_CATEGORY_VALUE
            GROUP BY SDR1.PERSON_ACCOUNT_ID 
            HAVING COUNT(*) = 1
        )
   GROUP BY 
        PA.PERSON_ID, PA.PERSON_ACCOUNT_ID, DR.DISBURSEMENT_REQUEST_ID, DD.PRE_TAX_AMOUNT, DD.POST_TAX_AMOUNT

The above query is to retrieve the records whose(SGT_DISBURSEMENT_REQUEST) count is only one. It is working great.But performance wise it didn't took so much of time since we have lakhs of records.

Is there any alternate way to do it.

Aucun commentaire:

Enregistrer un commentaire