Happy to help mic.con87
Seeing what you try to do, I think you can create a view that returns the result of your subquery. Then use that view in an outer join and use it's ClaCaseId in the case of your select, like so:
create view vGetMaxYearOfIncidentForClaCaseId
as
select max(rl.YearOfIncident) as YearOfIncident
, cc.ClaCaseID
from Staging.ClaCases cc
, Staging.Losses rl
where cc.NameID = rl.NameID
and rl.YearOfIncident < dateadd(dd, 0, datediff(dd, 0, cc.DiscoverDate))
and rl.LossType < 1000
and rl.RecordCreated < dateadd(dd, 0, datediff(dd, 0, cc.DiscoverDate))
and cc.QuestionClassID in (20, 25)
and datediff(day, dateadd(dd, 0, datediff(dd, 0, cc.DiscoverDate)), rl.YearOfIncident) < 1095
group by cc.ClaCaseID
go
select case when z.ClaCaseID is not null then 'Y' else 'N' end
from [YourTable] as ccx
left outer join vGetMaxYearOfIncidentForClaCaseId as z on z.ClaCaseID = ccx.ClaCaseID
_____________________________________________________
Do not go past the mark you aimed for, but learn when to stop.
You can find me on LinkedIn.
I support The Programmer's Bill of Rights.
MCITP, MCDBA, MCSD