Can someone help me with this Query

Question

Post reply

Can someone help me with this Query

Sahasam

Ten Centuries

Points: 1001
More actions
May 25, 2010 at 7:52 pm

#219357

have two tables

Employees Tests

EMPLID EMPLID

EMP_STATUS TEST_ID

EMP_IDENTITY TEST_DATE

In Employees table I have EMPLID= 121210,121211

In Tests Table I have EMPLID=121210 only i dnt have 121211 like below.

Employees table

EMPLID EMP_STATUS EMP_IDENTITY

121210 A A321

121211 L B123

Tests table

EMPLID TEST_ID TEST_DT

121210 2BC 1/1/2003

121210 H8K 3/2/2007

I need to get Earliest date(Minimum Date) between two dates of the EMPLID=121210 from Tests table

and the data for all EMPLIDs from Employees table

The output should be like below.

EMPLID EMP_STATUS EMP_IDENTITY TEST_ID TEST_DT

121210 A A321 2BC 1/1/2003

121211 L B123 NULL NULL

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

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

Sahasam Ten Centuries Points: 1001 More actions · Answer 1

Just to provide the Input..

CREATE TABLE Employees

(

Emp_ID int Not Null,

Emp_Status Char(2) NULL,

Emp_Identity Varchar(5) NULL

)

INSERT INTO Employees

Select '121210','A','A321' UNION ALL

Select '121211','L','B321'

SELECT * From Employees

--DROP TABLE Employees

CREATE TABLE Tests

(

Emp_ID int Not Null,

Test_ID Char(5) NULL,

Test_date datetime NULL

)

INSERT INTO Tests

Select '121210','2BC','01/01/2003' UNION ALL

Select '121210','H8K','03/02/2007'

SELECT * From Tests

WayneS SSC Guru Points: 95405 More actions · Answer 2

How does this work out for you?

SELECT DISTINCT



       e.EMPLID,

       e.EMP_STATUS,

       e.EMP_IDENTITY,

       t2.TEST_ID,

       t2.TEST_DT

  FROM @Employees e

       LEFT JOIN (SELECT EMPLID, TEST_DT = MIN(TEST_DT) OVER (PARTITION BY EMPLID)

                    FROM @Tests) t1 ON e.EMPLID = t1.EMPLID

       LEFT JOIN @Tests t2

         ON t2.EMPLID = t1.EMPLID

AND t2.TEST_DT = t1.TEST_DT

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 3

Sample data and table:

DECLARE @Employees TABLE



(

EMPLID INT,

EMP_STATUS VARCHAR(3),

EMP_IDENTITY VARCHAR(5)

)

INSERT INTO 	@Employees (EMPLID ,EMP_STATUS ,EMP_IDENTITY)

SELECT 121210,'A','A321' UNION ALL

SELECT 121211,'L','B123'

DECLARE @Tests  TABLE

(

 EMPLID INT,

 TEST_ID VARCHAR(5),

 TEST_DT DATETIME

)

INSERT INTO @Tests(EMPLID , TEST_ID, TEST_DT)

SELECT 121210,'2BC','1/1/2003'  UNION ALL

SELECT 121210, 'H8K' ,'3/2/2007'

Code:

;WITH CTE (EMPLID,TEST_DT) AS



(

	SELECT EMPLID , MIN(TEST_DT) TEST_DT FROM @Tests

    GROUP BY EMPLID

),

CTE2 AS

(

	SELECT

		T.EMPLID , C.TEST_DT , T.TEST_ID

    FROM

        @Tests T

    INNER JOIN CTE C

         ON   T.EMPLID  = C.EMPLID AND

              T.TEST_DT = C.TEST_DT

)

SELECT E.EMPLID ,E.EMP_STATUS,E.EMP_IDENTITY, C.TEST_DT,C.TEST_ID

FROM @Employees E

LEFT JOIN  CTE2 C