title: forward rolling sum with different stopping points by row
body: <p>First, some sample data so the business problem can be explained -</p>
  <pre><code>select
  ItemID = 276,
  Quantity,
  Bucket,
  DaysInMonth = day(eomonth(Bucket)),
  DailyQuantity = cast(Quantity * 1.0 / day(eomonth(Bucket)) as decimal(4, 0)),
  DaysFactor
  into #data
  from
  (
      values
      ('1/1/2021', 95, 5500),
      ('2/1/2021', 75, 6000),
      ('3/1/2021', 80, 5000),
      ('4/1/2021', 82, 5300),
      ('5/1/2021', 90, 5200),
      ('6/1/2021', 80, 6500),
      ('7/1/2021', 85, 6100),
      ('8/1/2021', 90, 5100),
      ('9/1/2021', null, 5800),
      ('10/1/2021', null, 5900)
  ) d (Bucket, DaysFactor, Quantity);
  
  select * from #data;
  </code></pre>
  <p><a href="https://i.stack.imgur.com/PZkvB.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/PZkvB.png" alt="enter image description here" /></a></p>
  <p>Now, the business problem -<br>
  The first row has a <code>DaysFactor</code> of 95.<br>
  The forward rolling sum for this row is calculated as
  (31 x 177) + (28 x 214) + (31 x 161) + (5 x 177) = 17,355<br>
  That is...<br>
  the daily quantity for all 31 days of the 1/1/2021 bucket plus<br>
  the daily quantity for all 28 days of the 2/1/2021 bucket plus<br>
  the daily quantity for all 31 days of the 3/1/2021 bucket plus<br>
  the daily quantity for 5 days of the 4/1/2021 bucket.<br>
  This results in 95 days of forward looking quantity.<br>
  95 days = 31 + 28 + 31 + 5<br></p>
  <p>For the second row, with a <code>DaysFactor</code> of 75, it would start with daily quantity for the 28 days in the 2/1/2021 bucket and go out until a total of 75 days' worth of quantity were summed, like so:<br>
  (28 x 214) + (31 x 161) + (16 x 177) = 13,815<br>
  75 days = 28 + 31 + 16</p>
  <p>One approach to this is building a calendar of daily demand and then summing quantity over the specified days.  However, I'm stuck on how to do the summing.  Here is the code that builds the calendar with daily quantities:</p>
  <pre><code>with
  dates as
  (
      select
      FirstDay = min(cast(Bucket as date)),
      LastDay = eomonth(max(cast(Bucket as date)))
      from #data
  ),
  tally as (
      select top (select datediff(d, FirstDay, LastDay) + 1 from dates)   --restrict to number of rows equal to number of days between first and last days
      n = row_number() over(order by (select null)) - 1
      from sys.messages
  ),
  calendar as (
      select
      Bucket = dateadd(d, t.n, d.FirstDay)
      from tally t
      cross join dates d
  )
  select
  c.Bucket,
  d.DailyQuantity
  from #data d
  inner join calendar c
  on year(d.Bucket) = year(c.Bucket)
  and month(d.Bucket) = month(c.Bucket);
  </code></pre>
  <p>Here's a screenshot of a subset of rows from this query:<br>
  <a href="https://i.stack.imgur.com/hjZK9.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hjZK9.png" alt="enter image description here" /></a></p>
  <p>I was hoping to use T-SQL's <code>LEAD()</code> to do this but don't see a way to put the <code>DaysFactor</code> into the ROWS clause within OVER().  Is there a way to do that?  If not, is there a set based approach to calculating the rolling forward sum?</p>
  <p>Expected result set:<br>
  <a href="https://i.stack.imgur.com/BBlot.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/BBlot.png" alt="enter image description here" /></a></p>