Question about SQL Server Query
-
Hi every one, I am new member, today I have a question a query, I hope every body help me.
I have 2 table:
Table Employee:
code, name
001, Name A
002,Name B
003,Name C
and table Position
JobCode, Job title, Employee Code, From Date
MRE, abc, 001,2011/09/09
MRO, abcd, 001,2011/10/10
CEO, xy,002,2012/01/01
CFO, xyz,002,2011/01/01
MG, aaa,003,2011/01/01
I want rasult:
Employee Code, JobCode, FromDate
001,MRO,2011/10/10
002,CEO,2012/01/01
003,MG,2011/01/01
Please help me.
Thank you very much.
-
This is a simple join statement.
Select Emp.[Employee Code], JobCode, FromDate from Employee Emp Inner join Position Pos on Emp.[Employee Code] = Pos.[Employee Code]
Please read through the link given below.
http://msdn.microsoft.com/en-us/library/ms191472.aspx
--- Babu
-
Thank for your answer, but result is not right, Your answer have result:
Employee Code, JobCode, FromDate
001,MRO,2011/10/10
001,MRE,2011/09/09
002,CEO,2012/01/01
002,CFO,2011/01/01
003,MG,2011/01/01
but I want result:
Employee Code, JobCode, FromDate
001,MRO,2011/10/10
002,CEO,2012/01/01
003,MG,2011/01/01
The date i want get is Max.
Please help me, thank you
-
DECLARE @Employee Table
(code VARCHAR(50),name VARCHAR(50))
INSERT INTO @Employee
SELECT '001','A' UNION ALL
SELECT '002','B' UNION ALL
SELECT '003','C'
DECLARE @Position Table
(JobCode VARCHAR(50),JobTitle VARCHAR(50),EmployeeCode VARCHAR(50),
FromDate DATETIME
)
INSERT INTO @Position
SELECT 'MRE','abc','001','2011/09/09' UNION ALL
SELECT 'MRO','abcd','001','2011/10/10'UNION ALL
SELECT 'CEO','xy','002','2012/01/01' UNION ALL
SELECT 'CFO','xyz','002','2011/01/01' UNION ALL
SELECT 'MG','aaa','003','2011/01/01'
;WITH CTE AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY EmployeeCode ORDER BY FromDate DESC) AS 'RNo',
EmployeeCode,JobCode,FromDate FROM @Employee Emp INNER JOIN @Position Pos
ON Emp.code = Pos.EmployeeCode
)
SELECT * FROM CTE
WHERE
RNo = 1
Shatrughna
-
With the problem as stated, you don't need the join to employee. This gives the correct result without any joins:
DECLARE @Position TABLE
(
JobCode VARCHAR(50) NOT NULL,
JobTitle VARCHAR(50) NOT NULL,
EmployeeCode VARCHAR(50) NOT NULL,
FromDate DATETIME NOT NULL
)
INSERT @Position
(JobCode, JobTitle, EmployeeCode, FromDate)
VALUES
('MRE','abc','001','2011/09/09'),
('MRO','abcd','001','2011/10/10'),
('CEO','xy','002','2012/01/01'),
('CFO','xyz','002','2011/01/01'),
('MG','aaa','003','2011/01/01')
SELECT
p.EmployeeCode,
p.JobCode,
p.FromDate
FROM @Position AS p
WHERE
p.FromDate =
(
SELECT
MAX(p2.FromDate)
FROM @Position AS p2
WHERE
p2.EmployeeCode = p.EmployeeCode
)
ORDER BY
p.EmployeeCode
-
Thank for your answer very much.
I have done it.
Thank you.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply