Calculating expired vouchers, given a fixed window

Using the tidyverse libraries, in particular dplyr, and magrittr, you can write this code

df %<>%  
  mutate(VouchersEarnedCumsum = cumsum(VouchersEarned),
         VouchersRedeemedCumsum = cumsum(VouchersRedeemed),
         VouchersCumsumDifference = VouchersEarnedCumsum - lead(VouchersRedeemedCumsum, 2, default = max(VouchersRedeemedCumsum)),
         VouchersCumsumDifference = as.numeric(VouchersCumsumDifference),
         VouchersCumsumDifference = case_when(VouchersCumsumDifference < 0 ~ 0, T ~ VouchersCumsumDifference),
         ExpiredVouchers = VouchersCumsumDifference - lag(VouchersCumsumDifference, default = 0),
         ExpiredVouchersInDate = lag(ExpiredVouchers, 3, default = 0))

The resulting table, starting from your dataframe df, is

    # A tibble: 8 x 8
      Date       VouchersEarned VouchersRedeemed VouchersEarnedCumsum VouchersRedeemedCumsum VouchersCumsumDifference ExpiredVouchers ExpiredVouchersInDate
      <chr>               <int>            <int>                <int>                  <int>                    <dbl>           <dbl>                 <dbl>
    1 01/01/2020             10                0                   10                      0                        0               0                     0
    2 02/01/2020              8                9                   18                      9                        1               1                     0
    3 03/01/2020              4                4                   22                     13                        1               0                     0
    4 04/01/2020              2                4                   24                     17                        2               1                     0
    5 05/01/2020              1                4                   25                     21                        2               0                     1
    6 06/01/2020              0                1                   25                     22                        2               0                     0
    7 07/01/2020              0                1                   25                     23                        2               0                     1
    8 08/01/2020              0                0                   25                     23                        2               0                     0

Then, it is quite easy to translate each function in corresponding SQL queries using dbplyr, with translate_sql.

I'm not sure this code works in every situation you're going to face, I recommend to test it extensively.

EDIT I think this corrected code solves the problem.

df %<>%  
  mutate(VouchersEarnedCumsum = cumsum(VouchersEarned),
         VouchersRedeemedCumsum = cumsum(VouchersRedeemed),
         VouchersCumsumDifference = VouchersEarnedCumsum - lead(VouchersRedeemedCumsum, 2, default = max(VouchersRedeemedCumsum)),
         VouchersCumsumDifference = as.numeric(VouchersCumsumDifference),
         VouchersCumsumDifference = case_when(VouchersCumsumDifference < 0 ~ 0, T ~ VouchersCumsumDifference),
         VouchersCumsumDifference = accumulate(VouchersCumsumDifference, max),
         ExpiredVouchers = VouchersCumsumDifference - lag(VouchersCumsumDifference, default = 0),
         ExpiredVouchersInDate = lag(ExpiredVouchers, 3, default = 0))

If you just need the total vouchers expired at a point in time you can use the following query and get the value from the max date line. If not exactly what you need, perhaps it can be a good starting point.

declare @vouchers table (VDate date, VouchersEarned int, VouchersRedeemed int);
insert  @vouchers values
('2020-01-01', 10, 0),
('2020-01-02', 8, 9),
('2020-01-03', 4, 4),
('2020-01-04', 2, 4),
('2020-01-05', 1, 4),
('2020-01-06', 0, 1),
('2020-01-07', 0, 1),
('2020-01-08', 0, 0);

--select * from @vouchers;

declare @expiry_days int = 3;
declare @start_date date = '2020-01-01';
declare @end_date date = '2020-01-08';

with tally_cte (N) as ( --just a list of numbers 1-n
        --need at lease as many rows as dates in date range other options including a permanent tally table here: https://www.sqlservercentral.com/blogs/tally-tables-in-t-sql
        -- 1000 rows
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
        CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
        CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
), days_cte as (    --a list of all dates in date range to include records for days with no earned or redeemed
        select VDate = dateadd(day, t.N - 1, @start_date)
        from tally_cte t
        where t.N <= datediff(day, @start_date, @end_date) + 1
)
select  xed.VDate, xed.VouchersEarned, xed.VouchersRedeemed, xed.CumulativeEarned, xed.CumulativeRedeemed
                , Expiring = lag(xed.Expiring, 1, 0) over (order by xed.VDate)
from    (--determine expired vouchers with a lag
        select  xing.VDate, xing.VouchersEarned, xing.VouchersRedeemed, xing.CumulativeEarned, xing.CumulativeRedeemed
                , Expiring = case when xing.Expiring < 0 then 0 else xing.Expiring end
        from    (--determine expiring vouchers
                select  v.*
                        , Expiring = lag(v.CumulativeEarned, @expiry_days - 1, 0) over (order by v.VDate) - v.CumulativeRedeemed
                from    (--basic query with cumulative earned and redeemed
                        select  v.*
                                , CumulativeEarned = sum(v.VouchersEarned) over (order by v.VDate)
                                , CumulativeRedeemed = sum(v.VouchersRedeemed) over (order by v.vDate)
                        from    days_cte d
                        join    @vouchers v
                                on  v.VDate = d.VDate
                        ) v
                ) xing
        ) xed
order   by xed.VDate;

Output:

VDate      VouchersEarned VouchersRedeemed CumulativeEarned CumulativeRedeemed Expiring
---------- -------------- ---------------- ---------------- ------------------ -----------
2020-01-01 10             0                10               0                  0
2020-01-02 8              9                18               9                  0
2020-01-03 4              4                22               13                 0
2020-01-04 2              4                24               17                 0
2020-01-05 1              4                25               21                 1
2020-01-06 0              1                25               22                 1
2020-01-07 0              1                25               23                 2
2020-01-08 0              0                25               23                 2

Didn't find a vectorized solution either, so back to loops with a queue to handle vouchers expiration period:

# Create new columns
df$calcExpired <- rep(0,nrow(df))
df$calcActive <- rep(0,nrow(df))

# Voucher validity
validity <- 3

# Create an empty queue
qEarned <- rep(0,validity)

for (i in 1:nrow(df)) {
  
  # Expired vouchers
  if (qEarned[1]>0) {
    Expired <-qEarned[1] 
  } else { 
    Expired <- 0
  }
  
  # New values
  Earned <- df$VouchersEarned[i]
  Redeemed <- df$VouchersRedeemed[i]
  
  # Push new value in queue
  qEarned <- c(qEarned[2:validity],Earned)
  
  # Empty the queue
  for (j in 1:validity) {
    if (Redeemed == 0) {
      break
    } 
    else {
      if (Redeemed <= qEarned[j]) {
        qEarned[j] <- qEarned[j] - Redeemed;
        Redeemed <- 0
        break;
      } else {
        Redeemed <- Redeemed - qEarned[j]
        qEarned[j] <- 0
      }
    }
  }

  # Store / output results
  Active <- sum(qEarned)
  df$calcActive[i] <- Active
  df$calcExpired[i] <- Expired
  cat('queue:',qEarned,' - Active:',Active,' - Expired:',Expired,'\n')
}

queue: 0 0 10  - Active: 10  - Expired: 0 
queue: 0 1 8  - Active: 9  - Expired: 0 
queue: 0 5 4  - Active: 9  - Expired: 0 
queue: 1 4 2  - Active: 7  - Expired: 0 
queue: 0 2 1  - Active: 3  - Expired: 1 
queue: 1 1 0  - Active: 2  - Expired: 0 
queue: 0 0 0  - Active: 0  - Expired: 1 
queue: 0 0 0  - Active: 0  - Expired: 0 

In T-SQL, you'll probably need a stored procedure returning a table to handle this.

For interest I am adding another option that works with both data sets (even though you have already solved it!). Note that this is pretty fixed on a 3 day expiry...if the expiry is larger then you need an extra column and an extra set of case statements that follow the same pattern. This one uses a recursive CTE. Perhaps it will be useful to someone else :-)

declare @vouchers table (VDate date, VouchersEarned int, VouchersRedeemed int);
insert  @vouchers values
--('2020-01-01', 10, 0),
--('2020-01-02', 8, 9),
--('2020-01-03', 4, 4),
--('2020-01-04', 2, 4),
--('2020-01-05', 1, 4),
--('2020-01-06', 0, 1),
--('2020-01-07', 0, 1),
--('2020-01-08', 0, 0);
('2020-01-01', 8, 1),
('2020-01-02', 3, 1),
('2020-01-03', 6, 3),
('2020-01-04', 0, 1),
('2020-01-05', 1, 1),
('2020-01-06', 12, 5),
('2020-01-07', 6, 5),
('2020-01-08', 10, 3),
('2020-01-09', 13, 7),
('2020-01-10', 1, 8);

--select * from @vouchers;

with cte as (
        --anchor element - day 1 data
        select  v.VDate, v.VouchersEarned, v.VouchersRedeemed, RemToday = v.VouchersEarned - v.VouchersRedeemed, Rem2Day = 0, Rem3Day = 0, ExpiredToday = 0
        from    @vouchers v
        where   v.VDate = (select min(VDate) from @vouchers)

        union all

        --recursive element - based on previous day totals
        select  v.vdate, v.VouchersEarned, v.VouchersRedeemed
                , RemToday = case   when v.VouchersEarned + c.RemToday + c.Rem2Day < v.VouchersRedeemed then 0  --overflow shouldn't happen - can't redeem more than exist
                                    when c.RemToday + c.Rem2Day < v.VouchersRedeemed            --partial use
                                        then v.VouchersEarned + c.RemToday + c.Rem2Day - v.VouchersRedeemed 
                                    else v.VouchersEarned end                                   --not used
                , Rem2Day = case    when c.RemToday + c.Rem2Day < v.VouchersRedeemed then 0     --overflow use
                                    when c.Rem2Day < v.VouchersRedeemed                         --partial use
                                        then c.RemToday + c.Rem2Day - v.VouchersRedeemed 
                                    else c.RemToday end                                         --not used
                                    --when c.Rem2Day > v.VouchersRedeemed then c.RemToday       --not used
                                    --else c.RemToday + c.Rem2Day - v.VouchersRedeemed end      --partial use
                , Rem3Dat = case    when c.Rem2Day < v.VouchersRedeemed then 0                  --overflow use
                                    else c.Rem2Day - v.VouchersRedeemed end                     --partial use
                , Expired = c.Rem3Day
        from    cte c
        join    @vouchers v
                on  v.VDate = dateadd(day, 1, c.VDate)
)
select  *
from    cte c
order   by c.VDate;

For those interested I have at least a working version of Edoch's R code in SQL: I've added a grouping variable StoreId here to mimic closer my actual data, but the logic doesn't change.

declare @vouchers table (Date date, StoreId int, VouchersEarned int, VouchersRedeemed int);
insert  @vouchers values
('2020-01-01', 1, 8, 1), ('2020-01-02', 1, 3, 1), ('2020-01-03', 1, 6, 3), ('2020-01-04', 1, 0, 1), ('2020-01-05', 1, 1, 1), ('2020-01-06', 1, 12, 5), ('2020-01-07', 1, 6, 5), ('2020-01-08', 1, 10, 3), ('2020-01-09', 1, 13, 7), ('2020-01-10', 1, 1, 8),
('2020-01-01', 2, 7, 2), ('2020-01-02', 2, 4, 0), ('2020-01-03', 2, 3, 1), ('2020-01-04', 2, 1, 1), ('2020-01-05', 2, 3, 1), ('2020-01-06', 2, 10, 5), ('2020-01-07', 2, 6, 4), ('2020-01-08', 2, 8, 2), ('2020-01-09', 2, 11, 6), ('2020-01-10', 2, 3, 7);

declare @expiry_days int = 3;

-- add the cumulatives in to the raw data
With base as (
    Select 
    *
    ,SUM(VouchersEarned) OVER(PARTITION BY StoreId ORDER BY Date ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS VouchersEarnedCumsum
    ,SUM(VouchersRedeemed) OVER(PARTITION BY StoreId ORDER BY Date ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS VouchersRedeemedCumsum
    from @vouchers
),
-- pull the redemption point back to the time of earning the vouchers, the lead
base_lead as (
    select 
    * 
    ,VouchersEarnedCumsum - lead(VouchersRedeemedCumsum, @expiry_days-1) over (PARTITION BY StoreId ORDER BY Date) as VouchersCumsumDifference
    from base
),
-- handle the negative values
base_lead_nonzero as (
    select 
    *
    ,Case when VouchersCumsumDifference < 0 then 0 else VouchersCumsumDifference end as VouchersCumsumDifference2
    from base_lead
),
-- now handle the periods where negatives and positives balance out and should be 0, by doing accumulate 
    base_lead_corrected as (
    select 
    *
    ,MAX(VouchersCumsumDifference2) OVER (PARTITION BY StoreId ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS VouchersCumsumDifference3
    from base_lead_nonzero
-- take the difference to isolate the expirations
), final_view as (
    select
    *
    ,VouchersCumsumDifference3 - lag(VouchersCumsumDifference3, 1, 0) over (PARTITION BY StoreId ORDER BY Date) as ExpiredVouchers
    from base_lead_corrected
)
select 
*,
lag(ExpiredVouchers, @expiry_days, 0) over (PARTITION BY StoreId ORDER BY Date) as ExpiredVouchersInDate
from final_view

Thanks also to Waldi & SMM for respectively providing me with a reliable method for testing the new answers and a framework for the SQL code :)