There is a chance of null value in the concatenated fields. I have applied ISNULL function to the columns from the outer joined table.
Just try with the below formatted code.
SELECT CASE
WHEN a.Sex = 1
THEN 'M'
WHEN a.Sex = 2
THEN 'F'
END AS Derived_Sex,
CASE
WHEN LTRIM(RTRIM(CONCAT(ISNULL(b.Complete,''), a.[CALCULATED_AGE]))) LIKE 'A%'
THEN 'Less than 1'
WHEN LTRIM(RTRIM(concat(ISNULL(b.Complete,''), a.[CALCULATED_AGE]))) LIKE 'B%'
THEN 'Less than 1'
WHEN LTRIM(RTRIM(concat(ISNULL(b.Complete,''), a.[CALCULATED_AGE]))) LIKE 'C%'
THEN 'Less than 1'
ELSE '1 year and above'
END AS Derived_InfantCat,
YEAR(a.DATE_OF_DEATH) AS [Derived_Year of Death],
FORMAT(a.DATE_OF_DEATH, 'MM') AS [Derived_Month of Death],
YEAR(a.DATE_OF_REGISTRATION) AS [Derived_Year of Registration],
FORMAT(a.DATE_OF_REGISTRATION, 'MM') AS [Derived_Month of Registration],
CONCAT(LEFT(CONVERT(nvarchar, a.DATE_OF_DEATH, 21), 4),
SUBSTRING(CONVERT(nvarchar, a.DATE_OF_DEATH, 21), 6, 2)) AS [Derived_YearMonth of Death],
CONCAT(LEFT(CONVERT(nvarchar, a.DATE_OF_REGISTRATION, 21), 4),
SUBSTRING(CONVERT(nvarchar, a.DATE_OF_REGISTRATION, 21), 6, 2)) AS [Derived_YearMonth of Registration],
CASE
WHEN LEN(
CASE
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) = ''
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) IS NULL
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
ELSE a.UNDERLYING_CAUSE_OF_DEATH
END) = 3
THEN CONCAT(
CASE
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) = ''
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) IS NULL
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
ELSE a.UNDERLYING_CAUSE_OF_DEATH
END, 'X')
ELSE
(CASE
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) = ''
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) IS NULL
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
ELSE a.UNDERLYING_CAUSE_OF_DEATH
END)
END AS [Derived_Cause of Death Code],
c.[4 character DESCRIPTION],
c.[3 character Sub-Chapter Description],
d.[District_Name] AS [LA NAME],
e.[WD14NM] AS [WARD NAME],
(
CASE
WHEN a.[NHS_ESTABLISHMENT_IND] = '1'
THEN 'NHS'
WHEN a.[NHS_ESTABLISHMENT_IND] = '2'
THEN 'NON-NHS'
ELSE 'OTHER'
END) AS [NHS ESTABLISHMENT TYPE DESCRIPTION],
(
CASE
WHEN a.[NHS_ESTABLISHMENT_IND] = '1'
THEN 'NHS'
WHEN a.[NHS_ESTABLISHMENT_IND] = '2'
THEN 'NON-NHS'
ELSE 'OTHER'
END) AS [NHS ESTABLISHMENT TYPE DESCRIPTION],
(
CASE
WHEN a.[NHS_ESTABLISHMENT_IND] = '1'
THEN 'NHS'
WHEN a.[NHS_ESTABLISHMENT_IND] = '2'
THEN 'NON-NHS'
ELSE 'OTHER'
END) AS [NHS ESTABLISHMENT TYPE DESCRIPTION],
(
CASE
WHEN a.PLACE_OF_DEATH_CODE = 'H'
THEN 'HOME'
WHEN a.PLACE_OF_DEATH_CODE = 'E'
THEN 'ELSEWHERE'
ELSE 'OTHER'
END
) AS [PLACE OF DEATH CODE],
concat(a.[NHS_ESTABLISHMENT_IND],'-',a.PLACE_OF_DEATH_CODE) as code
FROM RAW.dbo.ONS_Death_DOD201516FYQ1 AS a
LEFT OUTER JOIN REFERENCE.dbo.[ONS-PCMD_AGE] AS b
ON a.CALCULATED_AGE_UNIT = b.[Age Unit]
LEFT OUTER JOIN REFERENCE.dbo.[OTH_ICD-10] AS c
ON
CASE
WHEN LEN(
CASE
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) = ''
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) IS NULL
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
ELSE a.UNDERLYING_CAUSE_OF_DEATH
END) = 3
THEN CONCAT
(
CASE
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) = ''
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) IS NULL
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
ELSE a.UNDERLYING_CAUSE_OF_DEATH
END, 'X')
ELSE (
CASE
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) = ''
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
WHEN (a.UNDERLYING_CAUSE_OF_DEATH) IS NULL
THEN a.[CAUSE_OF_DEATH_ICD_CODE_1]
ELSE a.UNDERLYING_CAUSE_OF_DEATH
END)
END = c.[4 CharacterDiagnosis Code]
LEFT OUTER JOIN REFERENCE.dbo.GEOG_LANames_1991 AS d
ON a.[ULA_OF_RESIDENCE_CODE] = d.[ONS_District_Code]
LEFT OUTER JOIN REFERENCE.dbo.GEOG_WardNames_2014 AS e
ON CONCAT(ISNULL(a.[ULA_OF_RESIDENCE_CODE],''),ISNULL(a.[WARD_OF_RESIDENCE_CODE],'')) =e.WD14CDO
与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…
