I have a following SQL server stored procedure implemented by a consultant. Give a date range between @EarnedDate_frm and @EarnedDate_to to calculate the Over Time DOLLAR_TOTALS and TIME_TOTALS and compare last year same date period these two data grouped by Reason_desc.

My question is what is the relationship between table dcOverTimeSummary and Alias table D2? Self Join but no Join keyword?

SELECT reason_desc,
       DOLLAR_TOTALS = SUM(DOLLAR_TOTALS) OVER (PARTITION BY REASON_CODE, UNF_CIV_IND, Cash_Time_Ind),
       TIME_TOTALS = SUM(TIME_TOTALS) OVER (PARTITION BY REASON_CODE, UNF_CIV_IND, Cash_Time_Ind),
       ISNULL(
       (SELECT SUM(DISTINCT DOLLAR_TOTALS)
        FROM dcOverTimeSummary D2
        WHERE (D2.Earned_Date >= FORMAT(DATEADD("yyyy", -1, @EarnedDate_frm), 'MM-dd-yyyy') AND Earned_Date <= FORMAT(DATEADD("yyyy", -1, @EarnedDate_to), 'MM-dd-yyyy'))
            AND (D2.REASON_CODE = dcOverTimeSummary.REASON_CODE AND D2.UNF_CIV_IND = dcOverTimeSummary.UNF_CIV_IND AND D2.Cash_Time_Ind = dcOverTimeSummary.Cash_Time_Ind)), 0.00) AS prev_DOLLAR_TOTALS,
       ISNULL(
       (SELECT SUM(DISTINCT TIME_TOTALS)
        FROM dcOverTimeSummary D2
        WHERE (D2.Earned_Date >= FORMAT(DATEADD("yyyy", -1, @EarnedDate_frm), 'MM-dd-yyyy') AND Earned_Date <= FORMAT(DATEADD("yyyy", -1, @EarnedDate_to), 'MM-dd-yyyy'))
            AND (D2.REASON_CODE = dcOverTimeSummary.REASON_CODE AND D2.UNF_CIV_IND = dcOverTimeSummary.UNF_CIV_IND AND D2.Cash_Time_Ind = dcOverTimeSummary.Cash_Time_Ind)), 0) AS prev_TIME_TOTALS
FROM dcOverTimeSummary
WHERE (Earned_Date >= @EarnedDate_frm AND Earned_Date <= @EarnedDate_to)
ORDER BY reason_desc;

CodePudding user response：

D2 is a table alias for dcOverTimeSummary in the inner scopes of the subqueries. It differentiates the use of the table in the subqueries from the use of dcOverTimeSummery in the outer query. It is not a self join. Using the D2 alias is required in the subquery to do the comparisons to the values from the outer query scope.