Employee transfer details - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Employee transfer details

Author

Topic

vipinjha123
Starting Member

45 Posts

Posted - 2012-05-28 : 02:39:30

Dear All,
I am looking for a query where i can find employee transfer details
like employee a transfereed from locatioon a to b , employee project shifted from project C to project D.

table name transfer(empid,location,project,transfer_effective_date)

please suggest the best solution.

regards,
Vipin jha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-28 : 02:43:41

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vipinjha123
Starting Member

45 Posts

Posted - 2012-05-28 : 02:50:41

Hi Visakh thankx for your reply.
my query is

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'
ORDER BY B.EMP_EFFECTIVE_FROM ASC,A.EMP_STAFFID

what modification required to get locationto_location from,projectto_project from

regards,
vipin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-28 : 03:00:26

[code]
;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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vipinjha123
Starting Member

45 Posts

Posted - 2012-05-28 : 03:37:33

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-28 : 13:51:18

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.

that means you dont have date values in correct sequence i guess

post some sample data for us to see why

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources