Question about SQL Server Query

Question

Post reply

Question about SQL Server Query

vantuan02t1

Mr or Mrs. 500

Points: 598
More actions
January 2, 2012 at 7:40 pm

#248926

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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

baabhu SSCertifiable Points: 6202 More actions · Answer 1

baabhu

SSCertifiable

Points: 6202

January 2, 2012 at 7:59 pm

#1427528

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

vantuan02t1 Mr or Mrs. 500 Points: 598 More actions · Answer 2

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

shatrughna Ten Centuries Points: 1037 More actions · Answer 3



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

Paul White SSC Guru Points: 150457 More actions · Answer 4

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

vantuan02t1 Mr or Mrs. 500 Points: 598 More actions · Answer 5

vantuan02t1

Mr or Mrs. 500

Points: 598

January 2, 2012 at 11:41 pm

#1427559

Thank for your answer very much.

I have done it.

Thank you.