I've tried in both direction and after several testings before - the data was cached for both
I can share the code - is this tells something
Text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create proc GetNamesP(
@pref nvarchar(128),
@lcode nvarchar(10) = null, --, -- ''
@no_lcode nvarchar(10) = null, --, -- ''
@code0 nvarchar(5) = null, --, -- 'RA'
@code1 nvarchar(5) = null, --, -- 'MMM'
@no_code nvarchar(5) = null, --, -- ''
@year int = null , -- year(getdate()),
@date date = null -- getdate()
)
as
with LIN as (
select li.PersonId, p.eslid, p.fullname, li.FromDate, li.Departmentid, li.OrgId
, ROW_NUMBER() over (partition by p.id, Departmentid order by fromdate desc) nrin
from persons p
join DepartmentMembersIn LI on p.id = LI.PersonId
where 1=1
and DepartmentId not in (8,26)
and fullname like '%' + @pref + '%'
and FromDate <= coalesce(@date, datefromparts(@year,12,31), getdate())
), L as (
select LIN.*, lout.ToDate
, row_number() over (partition by lin.Departmentid, lin.personid order by todate) nrout
from LIN
left join DepartmentMembersOut lout
on LIN.PersonId = lout.PersonId
and LIN.Departmentid = coalesce(lout.DepartmentId, lin.Departmentid)
and LIN.OrgId = coalesce(lout.OrgId, lin.orgid)
where nrin = 1
), M as (
select * from L
where 1 = 1
and nrout = 1
), U as (
select M.PersonId, m.EslId, m.FullName, l.LCode, o.Code
from M
join Departments l on M.DepartmentId = l.id
join Organizations o on M.OrgId = o.id
where coalesce(ToDate, getdate()) >= coalesce(@date,datefromparts(@year,1,1), getdate())
), V as (
select * from U
where 1=1
and (coalesce(@lcode,'')='' or exists (select * from U U1 where u.personid = u1.PersonId and u1.LCode = @lcode))
and (coalesce(@no_lcode,'') ='' or not exists (select * from U U1 where u.personid = u1.PersonId and u1.LCode =
@no_lcode))
and (coalesce(@code0,'') = '' or exists (select * from U U1 where u.personid = u1.PersonId and u1.Code = @code0))
and (coalesce(@code1,'') = '' or exists (select * from U U1 where u.personid = u1.PersonId and u1.Code = @code1))
and (coalesce(@no_code,'') = '' or not exists (select * from U U1 where u.personid = u1.PersonId and u1.Code =
@no_code))
)
select * from V
pivot (min(LCode) for Code in (ESL, MMM, RAM, RA, RC, TR, CPR, ROS, CRY)) pvt