Get primary table entries where the latest secondary entry was 6 months ago

Question

I have two tables 1- `FormTemplate` where the columns are Id, CreatedDate, Creator, Schema and 2 - `FormSubmissions` where the columns are Id, FormTemplateId(Foreign key), Created Date, Creator.

FormTemplate

| Id         | CreatedDate            | Creator | Schema  |
|------------|------------------------|---------|---------|
|          1 | 2023-01-25 00:52:43.717| John    | {}      |
|          2 | 2023-01-25 00:52:43.717| Same    | {}      |
|          3 | 2023-01-25 00:52:43.717| Alee    | {}      |

FormSubmission

| Id     | FormTemplateId (Foreign key)|  CreatedDate           |   Creator  | 
|--------|-----------------------------|------------------------|------------|
|      1 |                            2| 2023-05-25 00:52:43.717|       Kyle |
|      2 |                            2| 2023-02-25 00:52:43.717|      Kayla |
|      3 |                            3| 2019-03-08 00:52:43.717|     Arthur |
|      4 |                            3| 2019-02-25 00:52:43.717|      Milli |

I would like to get all FormTemplate rows where the LATEST FormSubmission was 6 months ago. in the above table: I should get only the 3rd row (Id=3) from FormTemplate

I cannot create a query or find a way to fetch FormTemplates with this criteria efficiently.

Answer 1

You can also use a pre-grouped join.

SELECT
  t.*,
  s.*
FROM FormTemplate t
JOIN (
    SELECT
      s.FormTemplateId,
      MaxCreated = MAX(s.CreatedDate)
    FROM FormSubmission s
    GROUP BY s.FormTemplateId
    HAVING MAX(s.CreatedDate) <= DATEADD(MONTH, -6, GETDATE())
) s ON t.ID = s.FormTemplateId;

To get the actual ID of the subquery, you can instead use an APPLY subquery with a TOP 1

SELECT
  t.*,
  s.*
FROM FormTemplate t
CROSS APPLY (
    SELECT TOP (1)
      s.FormTemplateId,
      s.CreatedDate
    FROM FormSubmission s
    WHERE t.ID = s.FormTemplateId
    ORDER BY s.CreatedDate DESC
) s
WHERE s.CreatedDate <= DATEADD(MONTH, -6, GETDATE());

As far as the other answers are concerned, as long as you are grouping by t.ID the performance will almost certainly be the same.