Select most recent record (with expiration date)

Let’s say that we have 2 tables named Records and Opportunities:

Records:

RecordID	CustomerID	CreateDate
777	1	1/1/2021
888	2	1/1/2021
999	1	2/1/2021

Opportunities:

OppID	CustomerID	OppCreateDate
10	1	12/31/2020
11	1	1/10/2021
12	2	2/1/2021
13	1	4/1/2021
14	1	8/5/2025

Desired Output:

RecordID	CustomerID	CreateDate	#Opportunities
777	1	1/1/2021	1
888	2	1/1/2021	1
999	1	2/1/2021	1

As you can see, the Records table provides the first 3 columns of the desired output, and the “#Opportunities” column is created by counting the number of opportunities that happen after the record is created for a given customer.

Two key things to note on this logic:

1. Only count opportunities when they occur within 6 months of a record.
2. If another record is created for a customer, only count opportunities for the most recent record.

More specifically, OppID = 11 will get credited to RecordID = 777; 12 to 888; and 13 to 999. 10 and 14 will not get credited to either RecordID.

I wrote the below code, which does not take into account #2 above:

CREATE TABLE #Records
(
RecordID int
, CustomerID int
, CreateDate Date
)

INSERT INTO #Records
VALUES 
(777, 1, '2021-01-01')
, (888, 2, '2021-01-31')
, (999, 1, '2021-02-01')


CREATE TABLE #Opportunities
(
OppID int
, CustomerID int
, OppCreateDate Date
)

INSERT INTO #Opportunities
VALUES 
(10, 1, '2020-12-31')
, (11, 1, '2021-01-10')
, (12, 2, '2021-02-01')
, (13, 1, '2021-04-01')
, (14, 1, '2025-08-25')

select * 
from #Records

select * 
from #Opportunities

select rec.*
, (select count(*)
from #Opportunities opp
where rec.CustomerID=opp.CustomerID
and rec.CreateDate<=opp.OppCreateDate --record happened on the same day or before the opportunity
and datediff(month,rec.CreateDate,opp.OppCreateDate) < 6 --opened and created within 6 months
) as [#Opportunities]
from #Records rec

Any suggestions to incorporate #2 above and generate the desired output?

Answer

Decide on which #records row is related to an #Opportunities row based on #records.CreateDate

select RecordID, CustomerID, CreateDate, count(*) cnt
from (
   select r.RecordID, r.CustomerID, r.CreateDate,
      row_number() over(partition by op.OppID  order by r.CreateDate desc) rn
   from #records r
   join #Opportunities op on r.CustomerID = op.CustomerID and datediff(month, r.CreateDate, op.OppCreateDate) < 6 and r.CreateDate <= op.OppCreateDate
   ) t
where rn = 1 
group by RecordID, CustomerID, CreateDate

Returns

RecordID    CustomerID  CreateDate  cnt
777 1   2021-01-01  1
888 2   2021-01-31  1
999 1   2021-02-01  1