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个月前最新的FormSubmit所在的所有FormTemplate行。在上表中：我应该只从FormTemplate获得第3行(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，您可以改用带有前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)