Selecting max date and sum of comprate in sql server

Question

I am trying to get the sum(comprate) for only max(effdt) having max(id) from the following query,

SELECT  id, employee_code, effdt, comprate
FROM         dbo.employees_compensation
WHERE     (employee_code = '000321514')
ORDER BY effdt desc, id desc

Result of above query is as follows,

id      employee_code   effdt                   comprate
1473433 000321514       2010-09-02 00:00:00.000 46000
1473432 000321514       2010-09-02 00:00:00.000 138000
1453627 000321514       2010-09-02 00:00:00.000 46000
1453626 000321514       2010-09-02 00:00:00.000 138000
1447165 000321514       2010-09-02 00:00:00.000 46000
1447164 000321514       2010-09-02 00:00:00.000 138000
1424948 000321514       2010-09-02 00:00:00.000 46000
1424947 000321514       2010-09-02 00:00:00.000 138000
1405789 000321514       2010-09-02 00:00:00.000 46000
1405788 000321514       2010-09-02 00:00:00.000 138000

I tried many queries but I am not getting the expected result. What I want is the top first two values from above result i.e,

id    employee_code effdt                   comprate
1473433 000321514   2010-09-02 00:00:00.000 46000
1473432 000321514   2010-09-02 00:00:00.000 138000

and then get the sum(comprate) from the above result i.e,

sum(comprate)
184000

Could you help me in getting the expected result? Thanks in Advance.

score 0 · Answer 1 · edited May 23 '17 at 12:27

0

I would recommend looking up how to limit the size of your result set here LIMIT 10..20 in SQL Server. And then how to make calculations on sub-queries here http://www.sqlteam.com/article/aggregating-correlated-sub-queries

edited May 23 '17 at 12:27

Community

1
1

answered Jun 21 '12 at 05:09

John Moses

1,283
1
12
18

score 0 · Answer 2 · answered Jun 21 '12 at 05:24

If you're using SQL Server 2005 or newer (you didn't specify in your question), you can use a CTE (Common Table Expression) with the ROW_NUMBER function:

;WITH OrderedData AS
(
   SELECT 
       id, employee_code, effdt, comprate,
       RowNum = ROW_NUMBER() OVER(ORDER BY effdt DESC, id DESC) 
   FROM         
       dbo.employees_compensation
   WHERE     
      employee_code = '000321514'
   FROM dbo.YourTable
)
SELECT 
   SUM(comprate)
FROM  
   OrderedData
WHERE 
   RowNum <= 2

This CTE orders your data and ROW_NUMBER function hands out sequential numbers, starting at 1 and ordered by effdt DESC, id DESC - so the latest row (latest effdt and highest id) gets RowNum = 1. So then I basically select the top two rows and sum them up.

score 0 · Accepted Answer · answered Jun 21 '12 at 05:37

How about the following?

SELECT     SUM(comprate) AS ComprateSum
FROM         (SELECT     TOP (2) id, employee_code, effdt, comprate
                       FROM          _Test
                       WHERE      (employee_code = '000321514')
                       ORDER BY effdt DESC, id DESC) AS derivedtbl_1

Selecting max date and sum of comprate in sql server

3 Answers3