DECLARE @T TABLE (ID INT PRIMARY KEY, SentDate VARCHAR(10), ReceivedDate VARCHAR(10), CreatedDate VARCHAR(10))

INSERT INTO
  @T
VALUES
  (1, NULL, '2-11-2012', NULL),
  (2, '22-10-2012', NULL, '2-12-2012'),
  (3, '8-8-2012', NULL, NULL), 
  (4, NULL, NULL, '25-12-2012'),
  (5, '7-11-2012', 	'1-8-2012',	'5-1-2012')

SET DATEFORMAT dmy

SELECT
  ID, MAX(CAST(AnyDate AS DATE)) -- Select the maximum date out of the three splitted rows
FROM
  @T
CROSS APPLY
(
	-- Split row into three rows, each row containing a different date
	VALUES (SentDate), (ReceivedDate), (CreatedDate)
) CA(AnyDate)
GROUP BY
  ID

DECLARE @T TABLE (ID INT PRIMARY KEY, SentDate VARCHAR(10), ReceivedDate VARCHAR(10), CreatedDate VARCHAR(10), LB_Submitted VARCHAR(10), LExecuted VARCHAR(10))

INSERT INTO @T VALUES (1, NULL, '2-11-2012', NULL, NULL, NULL)
INSERT INTO @T VALUES (2, '22-10-2012', NULL, '2-12-2012', '', NULL)
INSERT INTO @T VALUES (3, '8-8-2012', NULL, '', NULL, NULL)
INSERT INTO @T VALUES (4, NULL, '', '25-12-2012', '', NULL)
INSERT INTO @T VALUES (5, '7-11-2012', '1-8-2012',	'5-1-2012', NULL, NULL)
INSERT INTO @T VALUES (6, NULL, NULL, NULL, NULL, '30-6-2012')
INSERT INTO @T VALUES (7, NULL, '', NULL, '4-3-2012', '') 


SET DATEFORMAT dmy

SELECT
  ID,
  CASE
    WHEN NULLIF(LExecuted, '') IS NOT NULL THEN
      CAST(LExecuted AS DATETIME)
    WHEN NULLIF(LB_Submitted, '') IS NOT NULL THEN
      CAST(LB_Submitted AS DATETIME)
    ELSE
      MAX(CAST(AnyDate AS DATETIME))
  END AnyDate
FROM
  @T
CROSS APPLY
(
	-- Split row into three rows, each row containing a different date
	--VALUES (SentDate), (ReceivedDate), (CreatedDate)
  SELECT SentDate 
  UNION ALL  
  SELECT ReceivedDate
  UNION ALL 
  SELECT CreatedDate
) CA(AnyDate)
GROUP BY
  ID, LExecuted, LB_Submitted

SELECT
  ID,
  COALESCE(NULLIF(LExecuted, ''), NULLIF(LB_Submitted, ''), MAX(CAST(AnyDate AS DATETIME))) AnyDate
FROM
  @T
CROSS APPLY
(
	-- Split row into three rows, each row containing a different date
	--VALUES (SentDate), (ReceivedDate), (CreatedDate)
  SELECT SentDate 
  UNION ALL  
  SELECT ReceivedDate
  UNION ALL 
  SELECT CreatedDate
) CA(AnyDate)
GROUP BY
  ID, LExecuted, LB_Submitted

 DECLARE @T TABLE (ID INT PRIMARY KEY, SentDate VARCHAR(10), ReceivedDate VARCHAR(10), CreatedDate VARCHAR(10), LB_Submitted VARCHAR(10), LExecuted VARCHAR(10))

INSERT INTO @T VALUES (1, NULL, '2-11-2012', NULL, NULL, NULL)
INSERT INTO @T VALUES (2, '22-10-2012', NULL, '2-12-2012', '', NULL)
INSERT INTO @T VALUES (3, '8-8-2012', NULL, '', NULL, NULL)
INSERT INTO @T VALUES (4, NULL, '', '25-12-2012', '', NULL)
INSERT INTO @T VALUES (5, '7-11-2012', '1-8-2012', '5-1-2012', NULL, NULL)
INSERT INTO @T VALUES (6, NULL, NULL, NULL, NULL, '6-30-2012')
INSERT INTO @T VALUES (7, NULL, '', NULL, '4-3-2012', ''); 

SET DATEFORMAT dmy;

WITH upvt AS
	(
	SELECT 
		ID,
		DATE_TYPE,
		THE_DATE
	  FROM
		(SELECT ID, SentDate, ReceivedDate, CreatedDate, LB_Submitted, LExecuted
		   FROM @t) pvt
	UNPIVOT
		(THE_DATE FOR DATE_TYPE IN ([SentDate], [ReceivedDate], [CreatedDate], [LB_Submitted], [LExecuted])) AS upvt
	)
SELECT
	ID,
	DATE_TYPE,
	THE_DATE
  FROM upvt a
 WHERE THE_DATE = 
	(SELECT MAX(THE_DATE) FROM upvt b
	  WHERE a.ID = b.ID)