CODEWITHSUNDEEP
sqlsql-server
JJ.
JJ.

Reputation: 9960

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

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)?

Upvotes: 3

Views: 72

Answers (3)

Pedro Mu&#241;oz
Pedro Mu&#241;oz

Reputation: 285

Try:

select distinct A.EmployeeId, A.PayrollGap, A.PayrollGapMax, B.PayPeriodStart

(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) A

inner join

(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) B

ON A.EmployeeId=B.EmployeeId and B.PayrollGap=A.PayrollGapMax

Upvotes: 0

Oleg
Oleg

Reputation: 1458

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

Upvotes: 1

Karl Kieninger
Karl Kieninger

Reputation: 9129

If I understand the question correctly you need to join your result set back to EmployeePayroll to add in PayPeriodStart.

Something like:

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
)
SELECT EmployeeId
      ,PayrollGap
      ,PayrollGapMax
      ,PayPeriodStart
 FROM (SELECT  z.EmployeeId,
               SUM(z.PayrollGap) AS PayrollGap,
               MAX(z.PayrollGap) AS PayrollGapMax
         FROM    cte z
        GROUP BY z.EmployeeId) x
      INNER JOIN
      cte ON cte.EmployeeId = x.EmployeeId
         AND cte.PayrollGap = x.PayrollGapMax

Which isn't optimized. Or tested, since no sample data. Or formatted particularly nicely.

Upvotes: 0

Related Questions