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

我想获得6个月前最新FormSubmission所在的所有FormTemplate行。在上表中：我应该只从FormTemplate中获得第三行（Id=3）

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

我无法创建查询，也找不到使用此条件有效获取FormTemplates的方法。

DROP TABLE IF EXISTS #FormTemplate;
DROP TABLE IF EXISTS #FormSubmission;

CREATE TABLE #FormTemplate 
(
Id          INT          NOT NULL,
CreatedDate datetime     NOT NULL,
Creator     varchar(50)  NOT NULL
);

INSERT INTO #FormTemplate
VALUES
  (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')
;

CREATE TABLE #FormSubmission 
(
Id             INT         NOT NULL,
FormTemplateId INT         NOT NULL,
CreatedDate    datetime    NOT NULL,
Creator        varchar(50) NOT NULL
);

INSERT INTO #FormSubmission 
VALUES
  (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')
;

SELECT T.Id, MAX(T.CreatedDate) AS CreatedDate, T.Creator
FROM #FormTemplate AS T
INNER JOIN #FormSubmission AS S
ON  T.ID = S.FormTemplateId
GROUP BY T.Id, T.Creator
HAVING MAX(S.CreatedDate) <= DATEADD(MONTH, -6, GETDATE())
;

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

要获得子查询的实际ID，可以使用带有TOP 1的APPLY子查询

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.

就其他答案而言，只要你按t.ID分组，性能几乎肯定是一样的。

select FormTemplateId
into #old
from FormSubmission
group by FormTemplateId
having max(CreatedDate)<=dateadd(month,-6,getdate())

select * from FormTemplate where Id in (select FormTemplateId from #old)