select t.empid,
t1.project as from_project,
t.project as to_project
from transfer t
outer apply (select top 1 project
             from transfer
             where empid = t.empid
             and transfer_effective_date < t.transfer_effective_date
             order by transfer_effective_date desc) t1

;With Emp_Projects
AS
(
SELECT DISTINCT
A.EMP_STAFFID "Employee code",
A.EMP_FIRSTNAME + ' '+ isnull(A.EMP_MIDDLENAME,'') + ' '+ isnull(A.EMP_LASTNAME,'') "Employee name",
LOCATION_NAME,
D.OU_NAME Project,
G.GRADE_DESCRIPTION,
E.SHIFT_NAME,
B.EMP_EFFECTIVE_FROM-1 "LWD Before Transfer",
B.EMP_EFFECTIVE_FROM "Transfer Effective Date"
FROM ERM_EMPLOYEE_MASTER A inner join ERM_EMPLOYEE_MASTER_HISTORY B ON A.EMP_STAFFID=B.EMP_STAFFID
inner join ERM_LOCATION_MASTER C ON C.LOCATION_ID=A.EMP_LOCATION_ID
INNER JOIN ERM_OU_MASTER D ON D.OU_ID=B.OU_ID
INNER JOIN SHIFT_MASTER E ON E.SHIFT_CODE=A.EMP_SHIFTCODE
LEFT JOIN ERM_GRADE_MASTER G ON A.EMP_GRADE_CODE=G.GRADE_CODE
WHERE A.EMP_ISACTIVE LIKE '%1%' AND A.EMP_STAFFID='A0007'
)

SELECT t.empid,
t1.project as from_project,
t.project as to_project,
other columns....
FROM Emp_Projects t
outer apply (select top 1 project
             from Emp_Projects 
             where empid = t.empid
             and transfer_effective_date < t.transfer_effective_date
             order by transfer_effective_date desc) t1
ORDER BY EMP_EFFECTIVE_FROM ASC,EMP_STAFFID 

quote:

---

Originally posted by vipinjha123

thank you very much friend,
its really a very useful,
but i dont know ahy all first from clumn showing null.

---