SQL Query to get the latest effective date and sum for a column-CodePudding

I have a table Bonus_tab with data like this:

person_number     effective_start_Date    Bonus     Bonus_amount
----------------------------------------------------------------    
    4546          28-01-2022              Bonus      18000
    4536          27-12-2021              Bonus      10000
   82727          28-01-2022              Bonus     100000
   82727          28-01-2022              Bonus      20000

I want to pick up rows that have latest effective_start_Date.

But if the effective_start_Date is the same for two rows, then I need to SUM the bonus_amount.

I used the below query to get the latest effective start date -

SELECT *
FROM   
    (SELECT 
         person_number,
         paf.assignment_number,
         bonus,
         bonus_amt,
         effective_start_date,
         ROW_NUMBER() OVER (PARTITION BY person_number
                            ORDER BY effective_start_date DESC) rn
     FROM   
         bonus_tab)
--where rn = 1

I get an output like this:

person_number    effective_start_Date    Bonus     Bonus_amount    RN
----------------------------------------------------------------------
    4546         28-01-2022              Bonus        18000         1
    4536         27-12-2021              Bonus        10000         1
   82727         28-01-2022              Bonus       100000         1
   82727         28-01-2022              Bonus        20000         2

How can I tweak the above query such that it gives me row_number as 1 for both the rows in 82727 as it is for the same effective start date ?

Then I can do the sum of amount.

CodePudding user response：

try:

SELECT effective_start_Date, SUM(bonus) from Bonus_tab 
group by effective_start_Date
order by effective_start_Date desc

this will group all records with the same date. The sum the bonus fields for that grouping.

Any fields selected in the query however, must either be passed to an aggregate function (e.g. sum) or included int he group by clause

CodePudding user response：

How can I tweak the above query such that it gives me row_number as 1 for both the rows in 82727 as it is for the same effective start date?

Use RANK or DENSE_RANK rather than ROW_NUMBER:

SELECT *
FROM   (
  SELECT person_number,
         bonus,
         bonus_amt,
         effective_start_date,
         RANK() OVER (PARTITION BY person_number
                      ORDER BY effective_start_date DESC) AS rn
  FROM   bonus_tab
)
where rn = 1

Then I can do the sum of amount.

Something like:

SELECT person_number,
       SUM(bonus_amt) AS total_bonus_amt
FROM   (
  SELECT person_number,
         bonus_amt,
         RANK() OVER (PARTITION BY person_number
                      ORDER BY effective_start_date DESC) AS rn
  FROM   bonus_tab
)
where rn = 1
GROUP BY person_number

Or you could use aggregation and KEEP:

SELECT person_number,
       SUM(bonus_amt) KEEP (DENSE_RANK LAST ORDER BY effective_start_date)
         AS total_bonus_amt
FROM   bonus_tab
GROUP BY person_number;