Clever Geek Handbook

How to get MAX () and not group all columns in SQL Server

I have this request.

INNER SELECT

returns multiple records. The outer has SUM and MAX, so I only have 1 entry:

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 MAX(z.PayrollGap)

I need to also grab PayPeriodStart.

The problem is if I add the PayPeriodStart column to the request, it will return more than 1 record and I need to do MAX(z.PayrollGap)

.

How can I run this request but at the same time return the PayPeriodStart related to MAX(z.PayrollGap)

?

+3


source to share


3 answers


Try to split your request:



;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
+1


source


If I understand the question correctly, you need to join your result set back to EmployeePayroll to add to 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 is not optimized. Or checked because there is no sample data. Or formatted especially nicely.

0


source


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
0


source






More articles:


All Articles