flyingmeniskus

mein Vorschlag:

komplett, zum nachvollziehen:

DECLARE @T1 AS TABLE(Nr INT, Name VARCHAR(50))

INSERT INTO @T1 SELECT 1,'Mustermann'

INSERT INTO @T1 SELECT 15 ,'Mayr'

INSERT INTO @T1 SELECT 33 ,'Bacher'

INSERT INTO @T1 SELECT 20 ,'Max'

DECLARE @T2 AS TABLE (Nr INT, Uhrzeit TIME, Status INT )

INSERT INTO @T2 SELECT 1,'14:00',1

INSERT INTO @T2 SELECT 15,'14:00',1

INSERT INTO @T2 SELECT 1,'15:00',1

INSERT INTO @T2 SELECT 33,'11:00',1

INSERT INTO @T2 SELECT 15,'17:30',1

INSERT INTO @T2 SELECT 33,'15:00',1

INSERT INTO @T2 SELECT 34,'19:00',1

SELECT * FROM @T1 t1

LEFT JOIN (SELECT Nr,MAX(Uhrzeit) Datum

FROM @T2

GROUP BY Nr) ua ON t1.nr=ua.Nr

SELECT * FROM @T1 t1

FULL JOIN (SELECT Nr,MAX(Uhrzeit) Datum

FROM @T2

GROUP BY Nr) ua ON t1.nr=ua.Nr

Nr Name          Nr        Uhrzeit
1   Mustermann 1         15:00:00

15 Mayr             15       17:30:00
33 Bacher          33       15:00:00
20 Max               NULL  NULL

Nr      Name            Nr Uhrzeit
1        Mustermann 1        15:00:00
15      Mayr             15      17:30:00
33      Bacher          33      15:00:00
20      Max              NULL  NULL
NULL NULL            34      19:00:00