CODEWITHSUNDEEP
sql-serverplsqlpeoplesoft
Lazar Markovic
Lazar Markovic

Reputation: 1

Return the current month salary and previous month salary in a same table

I have a task to prepare a report generated from a run control page and retrieve the current month salary and previous month salary. In that page, the user will choose the cal_id they want for example in this case the user choose cal id = FEB. Assume the table as below named table_salary:

emplid | cal_id | salary | pymt_date
101    | JAN    | 10000  | 2018-01-01
101    | FEB    | 15000  | 2018-02-01

And my expected output is

emplid | cur_sal| prev_sal 
101    | 15000  | 10000

What I have done so far is like below

SELECT 
    A.EMPLID, A.SALARY AS CUR_SAL, B.SALARY AS PREV_SAL
FROM  
    TABLE_SALARY A 
LEFT OUTER JOIN 
    TABLE_SALARY B ON A.EMPLID AND B.EMPLID
                   AND A.CAL_ID = B.CAL_ID
                   AND B.PYMT_DT = (SELECT MAX(B1.PYMT_DT)
                                    FROM TABLE_SALARY B1
                                    WHERE B1.EMPLID = B.EMPLID
                                      AND B1.PYMT_DT >= DATEADD(mm, DATEDIFF(mm, 0, B.PYMT_DT) - 1, 0)
                                      AND B1.PYMT_DT < DATEADD(mm, DATEDIFF(mm, 0, PYMT_DT), 0))

But above SQL didn't return the expected output.

Does anyone have an idea how to achieve my expected output?

Upvotes: 0

Views: 4313

Answers (2)

saarrrr
saarrrr

Reputation: 2877

Use a window function to retrieve the previous row in a sorted set. I think this should work.

SELECT TOP 1
    EMPLID, SALARY AS CUR_SAL, LEAD(SALARY, 1, 0) OVER (ORDER BY PYMT_DT DESC) AS PREV_SAL
FROM
    TABLE_SALARY
ORDER BY
    PYMT_DT DESC

Upvotes: 1

gulshan arora
gulshan arora

Reputation: 473

It should be like this Use Lead instead of Lag

Create table #t ( id int identity (1,1), Empid int , Month varchar
(10), Salary int, Paymentdate date )


insert into #t (Empid ,Month,Salary,Paymentdate) Select
'1','Jan',1000, '2018-01-01'

insert into #t (Empid ,Month,Salary,Paymentdate) Select
'1','Feb',1500, '2018-02-01'

Select * from #t

SELECT TOP 1
    Empid, SALARY AS CUR_SAL, Lead(SALARY, 1, 0) OVER (ORDER BY PaymentDate DESC) AS PREV_SAL FROM
    #t ORDER BY
    Paymentdate DESC




SELECT TOP 1
    Empid, SALARY AS CUR_SAL, LAG(SALARY, 1, 0) OVER (ORDER BY PaymentDate DESC) AS PREV_SAL FROM
    #t ORDER BY
    Paymentdate DESC

Upvotes: 1

Related Questions