DECLARE @T1 TABLE(ID INT, Dept CHAR(3), Cat INT)

INSERT INTO @T1(ID,Dept,Cat)

VALUES

(1, 'WER', 2),

(2, 'TTR', 7);

DECLARE @T2 TABLE(ID INT, [Date] DATE, Type CHAR(2))

INSERT INTO @T2(ID, [Date], Type)

VALUES

(1, '2013-07-01', 'GA'),

(2, '2013-07-04', 'FS'),

(2, '2013-07-08', 'TR'),

(2, '2013-07-01', 'TT'),

(1, '2013-04-05', 'RT'),

(1, '2013-05-13', 'GG'),

(2, '2013-04-18', 'TT');

WITH CTE AS (

SELECT t1.ID,

t1.Cat,

t2.[Date],

t2.Type,

ROW_NUMBER() OVER(PARTITION BY t1.ID ORDER BY t2.[Date]) AS rn,

ROW_NUMBER() OVER(PARTITION BY t1.ID ORDER BY t2.[Date] DESC) AS rnRev

FROM @T1 t1

INNER JOIN @T2 t2 ON t2.ID = t1.ID)

SELECT ID,

Cat,

MIN([Date]) AS FirstDate,

MAX(CASE WHEN rn=1 THEN Type END) AS FirstType,

MAX([Date]) AS LastDate,

MAX(CASE WHEN rnRev=1 THEN Type END) AS LastType

FROM CTE

GROUP BY ID, Cat;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537