使用 SQL 计算一年中任意 3 个月期间的总成本

您可以在SQL中组合使用窗口函数和子查询.

WITH ThreeMonthPeriods AS (
    SELECT DISTINCT MonthYearofDateofService,NEWID() AS T
    FROM YourTableName
    ORDER BY NEWID()
    OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY
)
, RecursiveCTE AS (
    SELECT
        MemberID,
        MonthYearofDateofService,
        TotalCost,
        CAST(TotalCost AS DECIMAL(10, 2)) AS RunningTotal, -- Adjusted the data type
        1 AS MonthCount
    FROM
        YourTableName
    WHERE
        MonthYearofDateofService IN (SELECT MonthYearofDateofService FROM ThreeMonthPeriods)
    UNION ALL
    SELECT
        t.MemberID,
        t.MonthYearofDateofService,
        t.TotalCost,
        CAST(t.TotalCost + r.RunningTotal AS DECIMAL(10, 2)), -- Adjusted the data type
        r.MonthCount + 1
    FROM
        YourTableName t
    INNER JOIN
        RecursiveCTE r ON r.MemberID = t.MemberID
                       AND r.MonthYearofDateofService < t.MonthYearofDateofService
    WHERE
        r.MonthCount < 3
)
SELECT
    MemberID
FROM
    RecursiveCTE
WHERE
    RunningTotal >= 0.09 * (SELECT SUM(TotalCost) FROM YourTableName WHERE MemberID = RecursiveCTE.MemberID)
GROUP BY
    MemberID
HAVING
    COUNT(*) <= 3;