SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE your_table_name (
    cust_id INT,
    eom_date DATE,
    email_sent INT
);

INSERT INTO your_table_name (cust_id, eom_date, email_sent)
VALUES 
(1, '2023-06-30', 1),
(1, '2023-05-31', NULL),
(1, '2023-04-30', 1),
(1, '2023-03-31', NULL),
(1, '2023-02-28', NULL),
(1, '2023-01-31', 1),
(2, '2023-06-30', 1),
(2, '2023-05-31', NULL),
(2, '2023-04-30', 1),
(2, '2023-03-31', NULL),
(2, '2023-02-28', NULL),
(2, '2023-01-31', NULL),
(2, '2022-12-31', 1);
13 rows affected

select *,
       datediff(
       month,
       (
         select max(t2.eom_date)
           from your_table_name t2
          where t1.cust_id = t2.cust_id
            and t1.eom_date >= t2.eom_date
            and t2.email_sent = 1
          group
             by t2.cust_id
       ),
       eom_date) as months_since_last_email
  from your_table_name t1
 order
    by cust_id,
       eom_date desc


cust_id
eom_date
email_sent
months_since_last_email


1
2023-06-30
1
0

1
2023-05-31
null
1

1
2023-04-30
1
0

1
2023-03-31
null
2

1
2023-02-28
null
1

1
2023-01-31
1
0

2
2023-06-30
1
0

2
2023-05-31
null
1

2
2023-04-30
1
0

2
2023-03-31
null
3

2
2023-02-28
null
2

2
2023-01-31
null
1

2
2022-12-31
1
0


select *,
       datediff(
       month,
       max(case when email_sent = 1 then 
                     eom_date
            end
          ) over ( partition by cust_id
                       order by eom_date
                      rows between unbounded preceding and current row
                 ),
       eom_date) as months_since_last_email
  from your_table_name t1
 order
    by cust_id,
       eom_date desc


cust_id
eom_date
email_sent
months_since_last_email


1
2023-06-30
1
0

1
2023-05-31
null
1

1
2023-04-30
1
0

1
2023-03-31
null
2

1
2023-02-28
null
1

1
2023-01-31
1
0

2
2023-06-30
1
0

2
2023-05-31
null
1

2
2023-04-30
1
0

2
2023-03-31
null
3

2
2023-02-28
null
2

2
2023-01-31
null
1

2
2022-12-31
1
0


Warning: Null value is eliminated by an aggregate or other SET operation.

cust_id	eom_date	email_sent	months_since_last_email
1	2023-06-30	1	0
1	2023-05-31	null	1
1	2023-04-30	1	0
1	2023-03-31	null	2
1	2023-02-28	null	1
1	2023-01-31	1	0
2	2023-06-30	1	0
2	2023-05-31	null	1
2	2023-04-30	1	0
2	2023-03-31	null	3
2	2023-02-28	null	2
2	2023-01-31	null	1
2	2022-12-31	1	0