Sql 验证某个日期前后的连续代码

I have two tables:
Table 1 consists of ID and INDX. INDX is the date around the CODE from Table2 needs to be verified.
Table2 consists of ID, CODE, DAT, QRTR. DAT is the date on which the CODE is given with corresponding quarter of the year in QRTR.

表1:

表2:

Now I need to know if CODE 'A' was present within 300 days until INDX date.
If so, I need to verify if CODE 'A' is given at least one QRTR per year starting with the first data entry of CODE 'A' (within 300 days prior INDX) including the future CODEs.

我try 了以下几种方法:

SELECT
    t1.ID,
    years.Year,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM table2 t2
            WHERE t2.ID = t1.ID
                AND t2.CODE = 'A'
                AND years.Year = YEAR(DATEADD(QUARTER, t2.QRTR % 10 - 1, DATEADD(YEAR, t2.QRTR / 10 - 1900, '19000101')))
        ) THEN 'Yes'
        ELSE 'No'
    END AS IsQuarterly
FROM
    (
        SELECT DISTINCT ID
        FROM table1
    ) AS t1
CROSS JOIN
    (
        SELECT DISTINCT YEAR(DATEADD(QUARTER, (QRTR % 10) - 1, DATEADD(YEAR, (QRTR / 10) - 1900, '19000101'))) AS Year
        FROM table2
        WHERE CODE = 'A'
    ) AS years
ORDER BY
    t1.ID,
    years.Year;

日期:db<>fiddle.(用谷歌翻译翻译)

为了表达清晰起见，我在上面的表格中只包含了ID 1.

通过我的查询，我只得到每年是否至少有一个CODE‘A’存在，但直到CODE‘A’出现在哪个时间，以及是否已经有一个CODE‘A’的新时期，我才能得到信息.

我预计会出现以下情况，因为我认为最好是得出一个CODE个‘A’每年至少出现一个QRTR个‘A’的时期.

我不确定显示结果的最佳解决方案是什么.