# Summing two rows

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.

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
``````

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?

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;``````

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

Thanks again.

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.