Two most recent rows per employee number

Transact-SQL
1

Hi all, I greatly need your help with something.

Let's say I have data like the following.

empid classify date rate
10 6136 1/1/2017 20.22
10 6136 3/5/2017 22.45
10 LONG 2/22/2016 1.23
10 LONG 3/12/2017 1.55
20 1241 2/5/2016 10.50
20 1241 4/22/2017 15.64
20 LONG 12/22/2016 2.33
20 LONG 4/24/2017 3.25

I would like a query which would produce two rows per empid. One lists the most recent rate (according to date) where the classify is 'LONG' and the other shows the most recent rate where the classify is not 'LONG' whatever that value may be.

The desired result:

EMPID DATE RATE
10 3/5/2017 22.45
10 3/12/2017 1.55
20 4/22/2017 15.64
20 4/25/2017 3.25

Thanks so much for your help.

2

Try this...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
    empid INT,
    classify CHAR(4) NOT NULL,
    [date] DATE NOT NULL,
    rate MONEY
    );

INSERT #TestData (empid, classify, [date], rate)  VALUES
    (10, '6136', '1/1/2017', 20.22),
    (10, '6136', '3/5/2017', 22.45),
    (10, 'LONG', '2/22/2016', 1.23),
    (10, 'LONG', '3/12/2017', 1.55),
    (20, '1241', '2/5/2016', 10.50),
    (20, '1241', '4/22/2017', 15.64),
    (20, 'LONG', '12/22/2016', 2.33),
    (20, 'LONG', '4/24/2017', 3.25);

WITH
    cte_AddRN AS (
        SELECT 
            td.empid, 
            td.classify, 
            td.date, 
            td.rate, 
            c.ClassGroup,
            rn = ROW_NUMBER() OVER (PARTITION BY td.empid, c.ClassGroup ORDER BY td.date DESC)
        FROM
            #TestData td
            CROSS APPLY ( VALUES (CASE WHEN td.classify = 'LONG' THEN 1 ELSE 0 END) ) c (ClassGroup)
        )
SELECT 
    arn.empid, 
    arn.classify, 
    arn.date, 
    arn.rate
FROM
    cte_AddRN arn
WHERE
    arn.rn = 1;
3

Or this:

DECLARE @t table
        (
         empid tinyint
       , classify char(4)
       , date date
       , rate smallmoney
        );
INSERT @t
        (empid, classify, date, rate)
    VALUES
        (10, '6136', '20170101', 20.22)
     ,  (10, '6136', '20170305', 22.45)
     ,  (10, 'LONG', '20160222', 1.23)
     ,  (10, 'LONG', '20170312', 1.55)
     ,  (20, '1241', '20160205', 10.50)
     ,  (20, '1241', '20170422', 15.64)
     ,  (20, 'LONG', '20161222', 2.33)
     ,  (20, 'LONG', '20170424', 3.25);
 
SELECT
        t.empid
      , t.classify
      , t.date
      , t.rate
    FROM
        @t t
    JOIN (
          SELECT
                empid
              , Max(date) date
            FROM
                @t
            WHERE
                classify = 'LONG'
            GROUP BY empid
         ) t2 ON t2.empid = t.empid
                 AND t.date = t2.date
UNION
SELECT
        t.empid
      , t.classify
      , t.date
      , t.rate
    FROM
        @t t
    JOIN (
          SELECT
                empid
              , Max(date) date
            FROM
                @t
            WHERE
                classify <> 'LONG'
            GROUP BY empid
         ) t2 ON t2.empid = t.empid
                 AND t.date = t2.date;

4

Joe, Unless the OP is using an ancient (pre 2005) version of SQL Server, there should be no need to take more than a single pass over the data.