Here is what I am looking at which provides the proper results but still seems too RBAR.
declare @today datetime = '9/23/2016'
declare @rc int
declare @complete table (suspid int);
declare @id table (roleid varchar(50),partyid int,memberagreementid int,editablestartdate datetime,previncstartdate datetime,previncenddate datetime,currincstartdate datetime, currincenddate datetime,suspid int);
-- if (object_id('tempdb..#id') is not null) drop table #id;
-- if (object_id('tempdb..#complete') is not null) drop table #complete;
-- select 0 suspid into #complete
;with
incentivedates as(
select roleid,partyid,memberagreementid,editablestartdate,previncstartdate,previncenddate,dateadd(dd,1,previncenddate) currincstartdate,dateadd(mm,3,previncenddate) currincenddate
from(
select row_number() over (PARTITION BY roleid
ORDER BY memberAgreementId desc) RN,roleid,partyid,memberagreementid,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end editablestartdate,
case when dateadd(dd,-1,dateadd(mm,datediff(mm,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ,@today)/3*3,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end )) >= @today
then
dateadd(mm,-3,dateadd(mm,datediff(mm,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ,dateadd(mm,-1,@today))/3*3,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end))
else
dateadd(mm,-3,dateadd(mm,datediff(mm,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ,@today)/3*3,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ))
end previncstartdate,
case when dateadd(dd,-1,dateadd(mm,datediff(mm,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ,@today)/3*3,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ) )>= @today
then
dateadd(dd,-1,dateadd(mm,datediff(mm,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ,dateadd(mm,-1,@today))/3*3,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ))
else
dateadd(dd,-1,dateadd(mm,datediff(mm,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ,@today)/3*3,case when editablestartdate < '9/4/2007' then '9/4/2007' else editablestartdate end ) )
end previncenddate
from memberagreement
) previncedates
where rn = 1
)
insert into @id select *,0 from incentivedates
select * from @id
--
set @rc = 1
while @rc > 0
begin
update id
set previncstartdate = dateadd(dd,datediff(dd,s.begintime,s.endtime),previncstartdate)
,previncenddate = dateadd(dd,datediff(dd,s.begintime,s.endtime),previncenddate)
,currincstartdate = dateadd(dd,datediff(dd,s.begintime,s.endtime),currincstartdate)
,currincenddate = dateadd(dd,datediff(dd,s.begintime,s.endtime),currincenddate)
,suspid = s.suspensionid
output inserted.suspid into @complete
--select *
from @id id
join suspension s on memberagreementid = targetentityid
and endtime <= previncenddate and begintime <= previncstartdate
and s.suspensionid not in (select suspid from @complete)
set @rc = @@ROWCOUNT
end
--select * from @id
--
set @rc = 1
while @rc > 0
begin
update id
set previncenddate = dateadd(dd,datediff(dd,s.begintime,s.endtime),previncenddate)
,currincstartdate = dateadd(dd,datediff(dd,s.begintime,s.endtime),currincstartdate)
,currincenddate = dateadd(dd,datediff(dd,s.begintime,s.endtime),currincenddate)
,suspid = s.suspensionid
output inserted.suspid into @complete
--select *
from @id id
join suspension s on memberagreementid = targetentityid
and endtime <= currincenddate and begintime <= previncenddate
and s.suspensionid not in (select suspid from @complete)
set @rc = @@ROWCOUNT
end
--select * from @id
--
set @rc = 1
while @rc > 0
begin
update id
set currincenddate = dateadd(dd,datediff(dd,s.begintime,s.endtime),currincenddate)
,suspid = s.suspensionid
output inserted.suspid into @complete
--select *
from @id id
join suspension s on memberagreementid = targetentityid
and endtime <= currincenddate and begintime >= currincstartdate
and s.suspensionid not in (select suspid from @complete)
set @rc = @@ROWCOUNT
end
--select * from @id
select * from @id
Also added soem more test data to my test tabes
select 555 partyid,777 roleid, 12345 memberagreementid,convert(datetime,'1/22/2016') editablestartdate
into memberagreement
union all
select 556 partyid,778 roleid, 12346 memberagreementid,convert(datetime,'2/15/2016') editablestartdate
union all
select 557 partyid,779 roleid, 12347 memberagreementid,convert(datetime,'1/5/2016') editablestartdate
select
1 suspensionid,12345 targetentityid, convert(datetime,'3/12/2016') begintime, convert(datetime,'3/30/2016') endtime,'N' complete
into suspension
union all
select 2,12345,'5/10/2016','5/30/2016','N'
union all
select 3,12345,'7/10/2016','7/20/2016','N'
union all
select 4,12345,'9/10/2016','9/30/2016','N'
union all
select 5,12345,'11/10/2016','11/30/2016','N'
union all
select 6,12346,'5/10/2016','5/30/2016','N'
union all
select 7,12346,'7/10/2016','7/20/2016','N'
union all
select 8,12346,'9/10/2016','9/30/2016','N'
union all
select 9,12346,'11/10/2016','11/30/2016','N'