How to get the MAX() while NOT grouping all columns in SQL Server

Question

I have this query.

The INNER SELECT brings back multiple records. The outer does a SUM & MAX so I only have 1 record:

SELECT  z.EmployeeId,
                    SUM(z.PayrollGap) AS PayrollGap,
                    MAX(z.PayrollGap) AS PayrollGapMax
            FROM    (SELECT  DISTINCT
                            a.EmployeeId,
                            a.PayPeriodStart,
                            a.PayPeriodEnd,
                            b.PayPeriodStart AS NextStartDate,
                            CASE WHEN DATEDIFF(d, a.PayPeriodEnd, b.PayPeriodStart) - 1 < 0 THEN 0
                                 ELSE DATEDIFF(d, a.PayPeriodEnd, b.PayPeriodStart) - 1
                            END AS PayrollGap
                     FROM   EmployeePayroll a
                     LEFT JOIN EmployeePayroll b
                     ON     b.EmployeeId = a.EmployeeId
                            AND b.rn = a.rn + 1
                     WHERE  b.PayPeriodStart IS NOT NULL) z
            GROUP BY z.EmployeeId

Along with the MAX(z.PayrollGap), I need to grab the PayPeriodStart as well.

The problem is that if I add the column PayPeriodStart to the query, it'll bring back more than 1 record and I need to do a MAX(z.PayrollGap).

How do I go about running this query but at the same time bringing back the PayPeriodStart RELATED TO MAX(z.PayrollGap)?

Answer 1

Try to split query:

;with cte as
(
    SELECT  DISTINCT
            a.EmployeeId,
            a.PayPeriodStart,
            a.PayPeriodEnd,
            b.PayPeriodStart AS NextStartDate,
            CASE WHEN DATEDIFF(d, a.PayPeriodEnd, b.PayPeriodStart) - 1 < 0 THEN 0
                 ELSE DATEDIFF(d, a.PayPeriodEnd, b.PayPeriodStart) - 1
            END AS PayrollGap
     FROM   EmployeePayroll a
     LEFT JOIN EmployeePayroll b
     ON     b.EmployeeId = a.EmployeeId
            AND b.rn = a.rn + 1
     WHERE  b.PayPeriodStart IS NOT NULL
),
res as
(
    SELECT  z.EmployeeId,
            SUM(z.PayrollGap) AS PayrollGap,
            MAX(z.PayrollGap) AS PayrollGapMax
    FROM    cte z   
    GROUP BY z.EmployeeId
)
select r.EmployeeId, r.PayrollGap, r.PayrollGapMax, c.PayPeriodStart
from res r
    join cte c on c.EmployeeId = r.EmployeeId
        and c.PayrollGap = r.PayrollGapMax