SELECT ASV.db_serv_job_no AS [JobNo], 
ASV.prop_seql AS [UPRN], 
QASV.status AS [Status], 
QASVS.status AS [SubStatus], 
CONVERT(VARCHAR(20), ASV.booking_date, 103) AS [Latest/NextApptDate],
CASE 
	WHEN CONVERT(VARCHAR(20), ASV.[1st_na], 103) = CONVERT(VARCHAR(20), ASV.[booking_date], 103) THEN CONVERT(VARCHAR(20), ASV.[booking_date], 103)
	ELSE CONVERT(VARCHAR(20), ASV.[1st_na], 103)
END AS [Appt1],
CONVERT(VARCHAR(20), ASV.[1st_na], 103) AS [NA1],
CASE
	WHEN CONVERT(VARCHAR(20), ASV.[1st_na], 103) = [Appt1] THEN 'NA1'
	WHEN CONVERT(VARCHAR(20), ASV.[1st_na], 103) = CONVERT(VARCHAR(20), ASV.[completion_date], 103) THEN 'CompVisit1'
			ELSE 'UnknAppt1'
END AS [Appt1Outcome],

ASV.[1st_na_letter] AS [NA1Letter], 
null AS [Appt2],
ASV.[2nd_na] AS [NA2], 
ASV.[2nd_na_letter] AS [NA2Letter], 
null AS [Appt3],
ASV.[3rd_na] AS [NA3], 
ASV.[3rd_na_letter] AS [NA3Letter], 
ASV.[1st_section_54] AS [1stSect54], 
ASV.[1st_section_54_appointment] AS [1stSect54Appt], 
ASV.[1st_section_54_no_access] AS [1stSect54NA], 
ASV.[2nd_section_54] AS [2ndSect54], 
ASV.[2nd_section_54_appointment] AS [2ndSect54Appt], 
ASV.[2nd_section_54_no_access] AS [2ndSect54NA], 
ASV.completion_date AS [CompletedDate], 
C.name_short AS Contract,
'ASV' AS [JobType]
FROM dbo.contracts AS C 
INNER JOIN (dbo.property AS P INNER JOIN ((dbo.servicing_jobs AS ASV 
																		INNER JOIN dbo.quicklist_service_status AS QASV ON ASV.status = QASV.id) 
INNER JOIN dbo.quicklist_service_status_sub AS QASVS ON ASV.status_sub = QASVS.id) 
ON P.prop_seql = ASV.prop_seql) ON C.id = P.contract_id
WHERE (((ASV.booking_date)> CONVERT(DATETIME, '2009-04-01 00:00:00', 102)))
AND ((C.name_short) NOT LIKE 'Test'+'%')

quote:

---

Originally posted by JezLisle

Thanks for that :)

So can you have multiple CASE within CASE?

---