SQL Server First Value and second last value in SQL

g6baxovj 于 2023-08-02 发布在其他

关注(0)|答案(2)|浏览(109)

I have a table with 4 columns: worker_id,month,basic_salary. I am trying to write a script that return worker_id, last salary date, second last basic_salary date, last salary amount (from basic_salary column).

Here's the script i wrote:

with cte as (
  select WORKER_ID,  (select max(month) from workers_table) last_salary
    , ROW_NUMBER()over (partition by worker_id order by month) rn
  from workers_table
  group by WORKER_ID,month
)
select *
from cte 
where rn=2

Here is a sample of the data:

enter image description here

and the data I get is:

WORKER_ID   last_salary rn
2011-11-11  2022-01-04  2
2011-11-12  2022-01-04  2
2011-11-13  2022-01-04  2
2011-11-14  2022-01-04  2
2011-11-15  2022-01-04  2
2011-11-16  2022-01-04  2

The last_salary column is incorrect and i couldn't find a way to fix it and get the right results.

sql-server

来源：https://stackoverflow.com/questions/76760939/first-value-and-second-last-value-in-sql

2条答案

按热度按时间

aamkag611#

You can use the LEAD window function to get the next row in the set

WITH cte AS (
    SELECT
      WORKER_ID,
      month,
      basic_salary,
      LEAD(month) OVER (PARTITION BY worker_id ORDER BY month) AS SecondLastSalaryDate,
      ROW_NUMBER() OVER (PARTITION BY worker_id ORDER BY month) AS rn
    FROM WORKERS_TABLE
)
SELECT
  c.WORKER_ID,
  c.month AS LastSalaryDate,
  c.basic_salary As Last_basic_salary,
  c.SecondLastSalaryDate
FROM cte c
WHERE c.rn = 1;

赞(0）回复(0）举报 2023-08-02

vpfxa7rd2#

Try like below. Get your cte & do not use GROUP BY . From cte have self join with condition as first one having rn = 1 and second one having rn = 2 . Update SELECT statement. Please check below query.

;WITH cte AS (
    SELECT WORKER_ID,
           month,
           basic_salary,
           ROW_NUMBER() OVER (PARTITION BY worker_id ORDER BY month) AS rn
    FROM WORKERS_TABLE
)
SELECT c1.WORKER_ID,
       c1.month AS LastSalaryDate,
       c1.basic_salary As Last_basic_salary,
       c2.month AS SecondLastSalaryDate 
FROM cte c1
    JOIN cte c2 ON c1.WORKER_ID = c2.WORKER_ID
WHERE c1.rn = 1 AND c2.rn = 2

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server First Value and second last value in SQL

2条答案

相关问题

热门标签

最新问答