title: Finding events within a given timeframe with SQL
body: <p>My brain must be completely mush at the moment because I'm stuck on this.  I have a table with dates that someone was given a drug and this comes with the amount of days that they are to take the drug.  A person can be prescribed <code>n</code> number of drugs so the CTE of the following query is to find the largest timeframe(the fill date of the drug + the days supply.)  I would then like to find the people that were on at least seven different drugs at the same time.  I'm having trouble finding the drugs the people are on for a given timeframe.  This needs to be restricted to the time period for which a person was on at least <code>seven</code> drugs at once.  Everything works fine up until that last part. </p>
  
  <p>sample data from the CTE  (fillDate + longestscript = endingDate)</p>
  
  <p><img src="https://i.stack.imgur.com/mQlUK.jpg" alt="enter image description here"></p>
  
  <p>concurrent scripts is determined by <code>count(distinct rx.drugname)</code> in the query</p>
  
  <p><img src="https://i.stack.imgur.com/3ampu.jpg" alt="enter image description here"></p>
  
  <pre><code>  ;with cte 
  as
  (
        select rx.patid
          ,rx.fillDate
          ,MAX(rx.dayssup) as longestScript
          ,DATEADD(day,cast(rx.dayssup as int),rx.filldate) as endingDate
          from rx
          group by rx.patid, rx.fillDate,rx.daysSup
   ),
   startends as (
           select patid, FillDate as thedate, 1 as isstart 0 as isend
           from CTE union all
           select patid, EndingDate as thedate, 0 as isstart, 1 as isend
           from CTE
       ),
       cums as (
           select se.*,
                  (select min(thedate) from startends se2 where se2.filledate &gt; se.filldate) as nextdate,
                  (select SUM(isstart) from startends se2 where se2.filldate &lt;= se.filldate) as cumstarts,
                  (select SUM(isend) from startends se2 where se2.filldate &lt;= se.filldate) as cumends
           from startends se
       )
  select *
  from cums
  where sumstarts - cumends &gt;= 7
  </code></pre>
  
  <p>You can see in the second query each patient appears many times with a different span of time between fillDate and endingDate.  How can I write a query to get the nine drugs repeated for the first row of the second screen cap?  SQL Server 08 r2 is my dbms.</p>