Summing two rows

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/5/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/22/2017 3.25

I would like a query which would produce a distinct listing of empid's which sums up the most recent rates, according to the eff_date, for their two classifications of 'LONG' and whatever the other value may be.

The desired result:

EMPID EFF_DATE RATE
10 3/5/2017 24
20 4/22/2017 18.89

Thanks so much for your help.

2

Try this

CREATE TABLE #tbl
(
  empid int,
  classify varchar(10),
  mDate date,
  rate numeric(5,2)

)

INSERT INTO #tbl 
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/5/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/22/2017 ',3.25)



SELECT   empid, mdate  , rate FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY empid ORDER BY mdate DESC) AS ct, 
empid, mDate,  SUM(rate)OVER (PARTITION BY empid, MONTH(mdate) ORDER BY mdate DESC) AS rate FROM #tbl 
)
AS t
WHERE ct=1

3

Thanks so much. I have a question though. Your query doesn't appear to distinguish between the most recent eff_date of the 'LONG' classification summed with that of another record without the 'LONG' as the classification. Doesn't it just sum up the most recent eff_date regardless regardless of what the classification may be?

4

Here are a couple more options... Based on your sample data, it;s hard to know which would be actually appropriate...

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

CREATE TABLE #tbl (
    empid int,
    classify varchar(10),
    mDate date,
    rate numeric(5,2)
    );

INSERT INTO #tbl 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/5/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/22/2017 ',3.25);

--======================================================
-- option 1
WITH 
    cte_Option_1 AS (
        SELECT 
            t.empid, 
            t.classify, 
            t.mDate, 
            t.rate,
            drnk = DENSE_RANK() OVER (PARTITION BY t.empid ORDER BY t.mDate DESC)
        FROM 
            #tbl t
        )
SELECT 
    o1.empid,
    o1.mDate,
    rate = SUM(o1.rate)
FROM
    cte_Option_1 o1
WHERE
    o1.drnk = 1
GROUP BY 
    o1.empid,
    o1.mDate;

--======================================================
-- option 2
WITH 
    cte_Option_2 AS (
        SELECT 
            t.empid, 
            t.classify, 
            t.mDate, 
            t.rate,
            rn = ROW_NUMBER() OVER (PARTITION BY t.empid, t.classify ORDER BY t.mDate DESC)
        FROM 
            #tbl t
        )
SELECT 
    o2.empid,
    mDate = MAX(o2.mDate),
    rate = SUM(o2.rate)
FROM
    cte_Option_2 o2
WHERE 
    o2.rn = 1
GROUP BY 
    o2.empid;
5

Thanks Jason. My requirements just changed however. haha! I will close this post and start something else similar.

Thanks again.

6

It takes the two most recent dates which coincidentally one belong to LONG and the other to other classify value. If that's not what you need try to post a sample data that cover your whole scenario.