Request help in writing the following sql code.
EFFDT = Effective Date
EFFSEQ = incremental change number of the record on a given day - starts with a zero. If there are two records in a day for the employee + employee record number combination, the EFFSEQ will be 0 and 1; 1 being the latest change for the day for that combination.
DATA
EFFDTEFFSEQEMPLIDEMPL_RCD_NBREMPL_CLASS
2014-09-20 00:00:00.0000000
0
0000100555
0
C
2015-07-05 00:00:00.0000000
0
0000100555
0
A
2015-07-12 00:00:00.0000000
0
0000100555
0
A
2015-07-12 00:00:00.0000000
1
0000100555
0
G
2015-07-26 00:00:00.0000000
0
0000100555
0
A
Below is the expected result from the sql code
Expected Result
EFFDTEFFSEQEMPLIDEMPL_RCD_NBRFROM_DATETO_DATEEMPL_CLASS2014-09-20 00:00:00.0000000
0
0000100555
0
2014-09-20 00:00:00.0000000
2015-07-11 00:00:00.0000000
2015-07-12 00:00:00.0000000
2015-07-12 00:00:00.0000000
1
0000100555
0
2015-07-12 00:00:00.0000000
2015-07-25 00:00:00.0000000
G
2015-07-26 00:00:00.0000000
00000100555
0
2015-07-26 00:00:00.0000000
Today's Date
Here is my code which is wrong.
use ev5;
WITH BaseData_CTE
(
EFFDT
,EFFSEQ
,EMPLID
,EMPL_RCD_NBR
,EMPL_CLASS
,LVL
,FROM_DT
)
AS
(
select EFFDT, EFFSEQ, EMPLID, EMPL_RCD_NBR, EMPL_CLASS
,ROW_NUMBER()OVER (PARTITIONBY EMPLID, EMPL_RCD_NBR ORDERBY EFFDT, EFFSEQ DESC)as LVL
,EFFDT as FROM_DT
from ev5.dbo.PS_JOB j
where j.EFFDT =(
selectMIN(j1.EFFDT)from ev5.dbo.PS_JOB j1
WHERE
j1.EMPLID = j.EMPLID
and j1.EMPL_RCD_NBR = j.EMPL_RCD_NBR
and j1.EMPL_CLASS = j.EMPL_CLASS
and j1.EFFDT <=GETDATE()
)
and j.EFFSEQ =(
selectMAX(j2.EFFSEQ)from ev5.dbo.PS_JOB j2
WHERE
j2.EMPLID = j.EMPLID
and j2.EMPL_RCD_NBR = j.EMPL_RCD_NBR
and j2.EMPL_CLASS = j.EMPL_CLASS
and j2.EFFDT = j.EFFDT
)
)
Select Parent.*
,CASEWHEN Child.EFFDT ISNULLTHEN'2100-12-31'
ELSEDATEADD(D,-1,Child.EFFDT)ENDas TO_DATE
from BaseData_CTE Parent
Leftjoin BaseData_CTE as Child
on Parent.EMPLID = Child.EMPLID
and Parent.EMPL_RCD_NBR = Child.EMPL_RCD_NBR
and Parent.LVL =(Child.LVL-1)
WHERE
Parent.EMPLID ='0000117321'
and Parent.EMPL_RCD_NBR = 1
Thank you,
Sunil
