title: selecting rows with id history for a given time interval in one result row
body: <p>A table <code>StatusHistory</code> contains:</p>
  <pre><code>ID      Status  DtSince DtUntil    
  2415891  0     20200816 20200917
  2415891  4     20200917 NULL
  2415892  0     20200904 20200905
  2415892  4     20200905 NULL
  </code></pre>
  <p>I need to select for each id the status for the beginning of each month and the end of it.
  The user will input the date needed. For example:</p>
  <p>User input --&gt; <code>@Month = '202009'</code></p>
  <p>The select statement must return the status for <code>dates 20200901 and 20201001</code></p>
  <p>For records that are new and have dtSince&gt;20200901 it should return the active row.For example for <code>2415892</code> it should return <code>4</code>.</p>
  <p>So the result set for @Month = 202009 should be :</p>
  <pre><code>Id       BeginningOfMonth_status EndOfMonth_status 
  2415891       0                         4
  2415892       4                         4
  </code></pre>
  <p>This is my solution</p>
  <pre><code>select * from
      (
      select * from 
          (
           select *, ROW_NUMBER() over (partition by id  order by dtSince desc) r from  
           dbo.StatusHistory
           where DtUntil  is null or (DtUntil &lt;20201001and dtSince&gt;=20200901) --2432290
          )x
       where x.r=1
  )s1
  inner join 
     (
     select * from 
         (
          select *, ROW_NUMBER() over (partition by id  order by dtSince desc) r from  
          dbo.StatusHistory
          where DtUntil  is null or (DtUntil &lt;20201001and dtSince&gt;=20200901) --2432290
         )x
     where x.r=2
     )s2
  on s1.id = s2.id
  </code></pre>
  <p>Questions:</p>
  <p>1)Is there anything less complex than my code?</p>
  <p>2)with the above,i am not covering the <code>For records that are new and have dtSince&gt;20200901</code> requirement</p>