Need to calc start and end date from single effective date
<p>I am trying to write SQL to calculate the start and end date from a single date called effective date for each item.  Below is a idea of how my data looks.  There are times when the last effective date for an item will be in the past so I want the end date for that to be a year from today.  The other two items in the table example have effective dates in the future so no need to create and end date of a year from today.</p>

<p><img src="https://i.stack.imgur.com/q5D0K.jpg"></p>

<p>I have tried a few ways but always run into bad data.  Below is an example of my query and the bad results</p>

<pre><code>select distinct tb1.itemid,tb1.EffectiveDate as startdate
, case 
when dateadd(d,-1,tb2.EffectiveDate) &lt; getdate() 
or tb2.EffectiveDate is  null
then getdate() +365 
else dateadd(d,-1,tb2.EffectiveDate) 
end as enddate
from #test tb1

left join #test as tb2 on (tb2.EffectiveDate &gt; tb1.EffectiveDate 
   or tb2.effectivedate is null) and tb2.itemid = tb1.itemid

left join #test tb3 on (tb1.EffectiveDate &lt; tb3.EffectiveDate 
  andtb3.EffectiveDate &lt;tb2.EffectiveDate or tb2.effectivedate is null) 
  and tb1.itemid = tb3.itemid

left join #test tb4 on tb1.effectivedate = tb4.effectivedate \
  and tb1.itemid = tb4.itemid

where tb1.itemID in (62741,62740, 65350)
</code></pre>

<p>Results - there is an extra line for 62740
<a href="https://i.stack.imgur.com/Q4Ffn.jpg" rel="nofollow noreferrer">Bad Results</a></p>

<p>I expect to see below since the first two items have a future end date no need to create an end date of today + 365 but the last one only has one effective date so we have to calculate the end date.</p>

<p><img src="https://i.stack.imgur.com/RY45t.jpg"></p>