Lots of ways of doing this, here's one

WITH dpCTE AS (

SELECT [nr],

[type],

[DT],

RANK() OVER(PARTITION BY [DT] ORDER BY [type]) AS rn1,

RANK() OVER(PARTITION BY [DT] ORDER BY [type] DESC) AS rn2

FROM [dbo].[dp]),

lnCTE AS (

SELECT [nr],

[type],

[DT],

RANK() OVER(PARTITION BY [DT] ORDER BY [type]) AS rn1,

RANK() OVER(PARTITION BY [DT] ORDER BY [type] DESC) AS rn2

FROM [dbo].[ln])

SELECT [nr],

[type],

[DT]

FROM dpCTE

WHERE rn1=1 AND rn2=1

UNION ALL

SELECT [nr],

[type],

[DT]

FROM lnCTE

WHERE rn1=1 AND rn2=1;

____________________________________________________

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