SQL Server 2022

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

select @@version;


(No column name)


Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
	Oct  8 2022 05:58:25 
	Copyright (C) 2022 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE deals_table(
   company_id        INTEGER  NOT NULL 
  ,deal_id           INTEGER  NOT NULL
  ,deal_date         DATE  NOT NULL
  ,deal_date_5y_back DATE  NOT NULL
);
INSERT INTO deals_table(company_id,deal_id,deal_date,deal_date_5y_back) VALUES (86,1,'2006-02-27','2001-02-27');
INSERT INTO deals_table(company_id,deal_id,deal_date,deal_date_5y_back) VALUES (86,2,'2012-04-01','2007-04-01');
INSERT INTO deals_table(company_id,deal_id,deal_date,deal_date_5y_back) VALUES (86,3,'2015-03-23','2010-03-23');
INSERT INTO deals_table(company_id,deal_id,deal_date,deal_date_5y_back) VALUES (86,4,'2018-09-19','2013-09-19');
4 rows affected

select d.*, d1.*
from deals_table d
cross apply (
    select count(*) num_deals_5y_back
    from deals_table d1
    where d1.company_id = d.company_id
      and d1.deal_date >= dateadd(year, -5, d.deal_date)
      and d1.deal_date <  d.deal_date
) d1


company_id
deal_id
deal_date
deal_date_5y_back
num_deals_5y_back


86
1
2006-02-27
2001-02-27
0

86
2
2012-04-01
2007-04-01
0

86
3
2015-03-23
2010-03-23
1

86
4
2018-09-19
2013-09-19
1


select d.*,
    count(*) over(
        partition by company_id
        order by datediff(day, '2000-01-01', deal_date)
        range between 1826 preceding and current row -- approx 5 yers, in days
    ) - 1 num_deals_5y_back
from deals_table d
Msg 4194 Level 16 State 1 Line 1
RANGE is only supported with UNBOUNDED and CURRENT ROW window frame delimiters.

company_id	deal_id	deal_date	deal_date_5y_back	num_deals_5y_back
86	1	2006-02-27	2001-02-27	0
86	2	2012-04-01	2007-04-01	0
86	3	2015-03-23	2010-03-23	1
86	4	2018-09-19	2013-09-19	1