May 25, 2010 at 7:52 pm
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
May 25, 2010 at 8:03 pm
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
May 25, 2010 at 8:13 pm
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
May 25, 2010 at 8:24 pm
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
ON E.EMPLID = C.EMPLID
Hope this helps you! Tell us if that worked!
C'est Pras!
May 25, 2010 at 8:38 pm
Thanks for your help Guys.
May 25, 2010 at 8:42 pm
You're welcome, sahasam!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply