SQL issue: Get Previous data based on a action code

Question

I am stuck with this issue. Please refer to screenshot too for table structure.

So we have 2 tables : HISTORY table keeps histiry of payrate, location etc & POSITION table only gives details of Job action changes datewise. Desired output we want to only consider Action_code as 'JOB-CHANGE' and get the latest payrate and previous payrate before the action- job change.

--- table1: HISTORY ---

EMPLOYEE	EFFECT_DATE	END_DATE	PAY_RATE	LOCAT_CODE
344	4/1/2021	current - TBD	42.44	ATL
344	3/2/2021	3/31/2021	41.81	ATL
344	3/31/2020	3/1/2021	41.81	DTW

--- table2 ACTION ---

COMPANY	EMPLOYEE	EFFECT_DATE	ACTION_CODE	REASON_01
100	344	3/31/2021	LOA	ST-RTW
100	344	3/2/2021	JOB CHANGE	JC-TRANS(L
100	344	5/31/2020	LOA	ST-COVID90

--- DESIRED OUTPUT --

Employee	Action code	Reason_1	Effective_Date	Previous_Location	Previous_Rate	New_Location	New_Pay_Rate
344	JOB CHANGE	JC-TRANS(L	3/2/2021	DTW	41.81	ATL	42.44

Could you please help me out here. Thanks much :) in advance

How do you propose to join these two tables, what are the common columns, unclear? And what have you tried so far — Charlieface
– Charlieface, Commented Jun 30, 2021 at 20:18
Common columns are Action code, dates, payrates. pretty much everything. The tricky part is to consider only the action code-JOB CHANGE and get max and one level down payrates like its shown in desired output — Samrocker
– Samrocker, Commented Jun 30, 2021 at 21:03
That's not complicated: just a simple LEAD/LAG. The difficult bit is how do you want to match the rows. The only common columns I see are EMPLOYEE, EFFECT_DATE. If you match on those two, is the date with a time component, then do we do an exact match? Or do we get the first row on or before that date from the history table? And how is New_Pay_Rate: 42.44 worked out, I don't get the logic — Charlieface
– Charlieface, Commented Jun 30, 2021 at 21:17
Okay yes, then we should join by EMPLOYEE. We want to consider Effect date of action. In ACTION the job change happens on 03/02/2021 so we want to check what was the payrate and location before the JOB CHANGE happened and what is the pay rate and location after that (which becomes NEW pay rate and NEW location) — Samrocker
– Samrocker, Commented Jul 1, 2021 at 15:52
So whats bugging me is... if we consider DTW as previous location then new pay rate will still be be the 41.81 cz in second line of HISTORY table... new location is already captured as ATL with the same 41.81 rate... — Samrocker
– Samrocker, Commented Jul 1, 2021 at 16:05

Charlieface · Accepted Answer · 2021-07-04 22:26:41Z

0

You can use OUTER APPLY (SELECT TOP (1) to get the first matching row on or before the date.

Within the APPLY you can use LEAD to access the values on the next row going backwards.

SELECT
  a.EMPLOYEE,
  [Action code] = h.ACTION_CODE,
  a.REASON_01,
  Effective_Date = a.EFFECT_DATE,
  hPrev.Previous_Location,
  hPrev.Previous_Rate,
  hNew.New_Location,
  hNew.New_Pay_Rate
FROM ACTION a
OUTER APPLY (
    SELECT TOP (1)
      Previous_Location = h.LOCAT_CODE,
      Previous_Rate = h.PAY_RATE
    FROM HISTORY h
    WHERE h.EFFECT_DATE < a.EFFECT_DATE
      AND h.Employee = a.Employee
    ORDER BY h.EFFECT_DATE DESC
) hPrev
OUTER APPLY (
    SELECT TOP (1)
      New_Location = h.LOCAT_CODE,
      New_Pay_Rate = h.PAY_RATE,
    FROM HISTORY h
    WHERE h.EFFECT_DATE > a.EFFECT_DATE
      AND h.Employee = a.Employee
    ORDER BY h.EFFECT_DATE
) hNew
WHERE a.ACTION_CODE = 'JOB CHANGE';

edited Jul 4, 2021 at 22:26

answered Jul 1, 2021 at 20:09

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Samrocker Over a year ago

Ohkay lemme try that in DB, Thanks very much, I will let you know soon how it goes

Samrocker Over a year ago

Its for MS SQL? cz its not binding properly. I am getting error : Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '.'. Msg 156, Level 15, State 1, Line 20 Incorrect syntax near the keyword 'ORDER'.

Charlieface Over a year ago

Sorry missing commas

Samrocker Over a year ago

Gotcha, il add them

Samrocker Over a year ago

Hii, so in the output the 'New_Pay rate' is still coming as 41.81. Its not coming as 42.44

|

Collectives™ on Stack Overflow

SQL issue: Get Previous data based on a action code

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related