Code :
Declare @Alpha TABLE (NomServer varchar(255) NOT NULL, DateEntree date NOT NULL) Declare @Beta TABLE (NomServer varchar(255) NOT NULL, DateEntree date NOT NULL) Declare @Gamma TABLE (NomServer varchar(255) NOT NULL, DateEntree date NOT NULL) Declare @Delta TABLE (NomServer varchar(255) NOT NULL, DateEntree date NOT NULL) INSERT @Alpha (NomServer, DateEntree) VALUES ('S1', '2013-02-09') INSERT @Alpha (NomServer, DateEntree) VALUES ('S1', '2013-02-08') INSERT @Alpha (NomServer, DateEntree) VALUES ('S2', '2013-03-24') INSERT @Alpha (NomServer, DateEntree) VALUES ('S2', '2013-03-23') INSERT @Alpha (NomServer, DateEntree) VALUES ('S3', '2013-03-30') INSERT @Alpha (NomServer, DateEntree) VALUES ('S3', '2013-03-29') INSERT @Alpha (NomServer, DateEntree) VALUES ('S5', '2013-02-02') INSERT @Alpha (NomServer, DateEntree) VALUES ('S5', '2013-02-01') INSERT @Beta (NomServer, DateEntree) VALUES ('S1', '2013-01-09') INSERT @Beta (NomServer, DateEntree) VALUES ('S1', '2013-01-08') INSERT @Beta (NomServer, DateEntree) VALUES ('S3', '2013-07-12') INSERT @Beta (NomServer, DateEntree) VALUES ('S3', '2013-07-12') INSERT @Delta (NomServer, DateEntree) VALUES ('S1', '2013-01-03') INSERT @Delta (NomServer, DateEntree) VALUES ('S1', '2013-01-02') ;With cte AS ( SELECT 'ALPHA' [TABLE], NomServer, Max(DateEntree) DateEntree FROM @Alpha GROUP BY NomServer UNION ALL SELECT 'BETA' [TABLE], NomServer, Max(DateEntree) DateEntree FROM @Beta GROUP BY NomServer UNION ALL SELECT 'GAMMA' [TABLE], NomServer, Max(DateEntree) DateEntree FROM @Gamma GROUP BY NomServer UNION ALL SELECT 'DELTA' [TABLE], NomServer, Max(DateEntree) DateEntree FROM @Delta GROUP BY NomServer ) SELECT * FROM cte a JOIN ( SELECT NomServer, MAX(DateEntree) DateEntree FROM cte GROUP BY NomServer ) b ON b.DateEntree = a.DateEntree AND b.NomServer = a.NomServer ORDER BY a.NomServer, a.[TABLE]
|