[SOLVED] Self join taking time

Self join taking time

I am having below query which selects SUM of AAD_00TO30 columns depending upon some conditions.

The query executes in 1 sec when I remove below condition, but it takes more than a min when same condition is included.

Can someone please suggest me any alternative to modify the query for better performance.

AND A.AAD_DATE >= (SELECT MAX(B.AAD_DATE) 
FROM MST_AR_AS_ON_DATE B 
WHERE MONTH(B.AAD_DATE) = MONTH(A.AAD_DATE) AND YEAR(B.AAD_DATE) = YEAR(A.AAD_DATE))

Query:

SELECT '00-30 #66ff66',SUM(A.AAD_00TO30)  FROM MST_AR_AS_ON_DATE A 
WHERE MONTH(A.AAD_DATE) = MONTH(DATEADD(MM,-1,GETDATE()))  
AND YEAR(A.AAD_DATE) = YEAR(DATEADD(MM,-1,GETDATE())) 
AND A.AAD_RESP_NOW = 4 
AND A.AAD_DATE >= (SELECT MAX(B.AAD_DATE) 
FROM MST_AR_AS_ON_DATE B 
WHERE MONTH(B.AAD_DATE) = MONTH(A.AAD_DATE) AND YEAR(B.AAD_DATE) = YEAR(A.AAD_DATE))

Solution

Try using RANK() to tag rows that meet the criteria of having the last date of the month. Then eliminate rows without a winning rank:

WITH 
    MST_AR_AS_ON_DATE_RANKED AS (
        SELECT
            *,
            RANK() OVER (
                PARTITION BY
                    YEAR(AAD_DATE),
                    MONTH(AAD_DATE)
                ORDER BY
                    AAD_DATE DESC -- last day of month ranked highest
            ) AS AAD_DATE_RANK
        FROM
            MST_AR_AS_ON_DATE
    )

SELECT 
    '00-30 #66ff66',
    SUM(AAD_00TO30)
FROM 
    MST_AR_AS_ON_DATE_RANKED
WHERE 
    MONTH(AAD_DATE) = MONTH(DATEADD(MM,-1,GETDATE()))  
    AND YEAR(AAD_DATE) = YEAR(DATEADD(MM,-1,GETDATE())) 
    AND AAD_RESP_NOW = 4
    AND AAD_DATE_RANK = 1 
;