Sorry if this is a simple/silly question I am new to SQL so still learning and asking lots of questions about it.
In the code below I want to call ‘Location’ in my where statement but this obviously doesn’t work, I know I can copy down the case statement but is there not a way to recall ‘Location’? Thanks
SELECT
case when [Case Type1] = 'Telephone Consultation' and ([Case Closed At]) is not null then 'Telephone Consultation & Base Visit'
when [Case Closed At] is null then 'Telephone Consultation'
when [Case Type1] = 'Home Visit' and [Case Closed At] not like '%Car%' then [Case Closed At] + ' Car'
when [Case Type1] = 'Home Visit' and [Case Closed At] like '%1%' and left ([case closed at],4) = 'East' then 'East Oxford Car'
when [Case Type1] = 'Home Visit' and [Case Closed At] like '%2%' and left ([case closed at],4) = 'East' then 'East Oxford Car'
when [Case Type1] = 'Home Visit' and [Case Closed At] like '%1%' then left ([Case Closed At], charindex(' ',[Case Closed At])-1) + ' Car'
when [Case Type1] = 'Home Visit' and [Case Closed At] like '%2%' then left ([Case Closed At], charindex(' ',[Case Closed At])-1) + ' Car'
when [Case Type1] in ('Home Vis it','Base Visit') then [Case Closed At]
else 'NOOOOooo'
end as 'Location'
FROM [db_Source_UrgentCare].[dbo].[tbl_Adastra_OOH_CaseExtract_alldata_to_31032016]
where [Case Started] BETWEEN '2014-04-01 00:00:01.000' AND '31-March-2016 23:59:59.000'
and [Location] = 'Abingdon'
