Populating bridge table with conditions I have a bridge table trajectory_detail that I am supposed to populate with millions of records from the corresponding parent tables. The problem is that every value from parent table trajectory_id is supposed to have at least 3 values of activity_id from parent table activities and the date for all these values must be less than 3 months apart.
            create table medical_trajectory
        (
            traject_id int identity(1,1) not null,
            patient_id int not null,
            PRIMARY KEY(traject_id)
        
        )
    
        create table activities (
        activity_id int identity(1,1) not null,
        name varchar(256) not null,
        PRIMARY KEY(activity_id)
    )
    
        create table trajectory_detail ( -- bridge table
            traject_id int not null,
            activity_id int not null,
            date_performed date not null,
            FOREIGN KEY(traject_id) REFERENCES medical_trajectory(traject_id),
            FOREIGN KEY(activity_id) REFERENCES activities(activity_id)
)

The desired outcome would look like this:

I have somehow managed to resolve this using nested while loops but the performance is just terrible and I am kinda stuck and would really appreciate any help.
I'm going to assume there is a table with the activities and dates, and you are only inserting the conforming records into your table.  I'm going to call that data_source.
So we need to do a few things here:

Establish intervals of three months around traject_id for each date_performed.
Find the number of activities that occurred within those intervals, and a distinct count of activity_id.
Remove intervals that do not contain at least three distinct activities.
Ensure the intervals do not overlap then join to the original data.

So here goes:
WITH traj_interval AS
(
  SELECT
    interval.traject_id
   ,interval.start_date
   ,CASE
      WHEN interval.end_date &lt; LEAD(interval.start_date,1,'9999-12-31') OVER (PARTITION BY interval.traject_id ORDER BY interval.start_date) THEN interval.end_date
      ELSE DATEADD(DAY,-1,LEAD(interval.start_date) OVER (PARTITION BY interval.traject_id ORDER BY interval.start_date))
    END AS end_date
  FROM
    (
      SELECT DISTINCT
        traject_id
       ,date_performed AS start_date
       ,DATEADD(MONTH,3,date_performed) AS end_date
      FROM
        data_source
    ) interval
  INNER JOIN
    data_source data_source
      ON data_source.traject_id = interval.traject_id
           AND data_source.date_performed &gt;= interval.start_date
           AND data_source.date_performed &lt;= interval.end_date
  GROUP BY
    interval.traject_id
   ,interval.start_date
   ,interval.end_date
  HAVING
    COUNT(DISTINCT data_source.activity_id) &gt;= 3
)
SELECT
  data_source.traject_id
 ,data_source.activity_id
 ,data_source.date_performed
FROM
  data_source data_source
INNER JOIN
  traj_interval traj_interval
    ON traj_interval.traject_id = data_source.traject_id
         AND traj_interval.start_date &lt;= data_source.date_performed
         AND traj_interval.end_date &gt;= data_source.date_performed

So this handles gaps and overlapping intervals, which is the major headache from before.
This does not make the prettiest execution plan, but it has to be faster than performing loops.