Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
First and last based on same SQL
Message
De
10/10/2014 01:12:19
 
 
À
09/10/2014 22:23:13
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Versions des environnements
SQL Server:
SQL Server 2012
Application:
Web
Divers
Thread ID:
01609079
Message ID:
01609098
Vues:
41
OK, here's one way. Again, there's no way (at least that I can think of) to solve this without multiple SELECT statements. I've done this as a common table expression subquery.

I created a table with rows for 2 clients:
CREATE TABLE dbo.Test  (ClientNo int, AddDate Date, Name varchar(20))
INSERT INTO dbo.Test 
   VALUES  (1,'05/05/2014', 'Michel Fournier'), 
                  (1,'05/06/2014', 'Michel Fournier'), 
                  (1,'06/15/2014', 'James Bond'), 
	  (1,'07/01/2014', 'James Bond'), 
	  (1,'10/10/2014', 'King Kong') , 

	   (2,'05/05/2014', 'Kevin Goff'), 
	  (2,'06/15/2014', 'Katy Goff'), 
	  (2,'07/01/2014', 'Katy Goff'), 
	  (2,'10/10/2014', 'John Smith')  
Here's one solution with a CTE and then 2 correlated subqueries. If there's a risk of a tie (2 names for the same date), you could do a SELECT TOP 1 Name

As I look at this, I'm pretty sure there's an easier way. I'll post again if I can think of one (or maybe someone else has an idea).
I'm sure there are variations of this that could be done with CROSS APPLY and other approaches, but this at least should work.
;WITH TempCTE AS (SELECT ClientNo, MAX(AddDate) as MaxDate, MIN(AddDate) as MinDate
                                     FROM dbo.Test  GROUP BY ClientNo)


SELECT TempCTE.ClientNo , 
        (SELECT Name from dbo.Test  WHERE TempCTE.ClientNo = Test.ClientNo AND Tempcte.MinDate = Test.AddDate) as FirstName, 
        (SELECT Name from dbo.Test  WHERE TempCTE.ClientNo = Test.ClientNo AND Tempcte.MaxDate = Test.AddDate) as LastName
FROM TempCTE 
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform