Using Eirikur's data set, here's a different way:
;WITH MyOriginalQuery AS ( -- a query which contains related records of payments made grouped by month and year
SELECT PAYMENT_ID, MEMBER_ID, [Year] = YEAR(PAYMENT_DATE), [Month] = MONTH(PAYMENT_DATE), PAYMENT_AMOUNT
FROM @MEM_PAY_DATE
)
SELECT m.*
FROM (
SELECT
MEMBER_ID,
MonthsPaid = COUNT(*) OVER(PARTITION BY MEMBER_ID)
FROM MyOriginalQuery q
INNER JOIN (
SELECT
[Year] = YEAR(DATEADD(month,-n,GETDATE())),
[Month] = MONTH(DATEADD(month,-n,GETDATE()))
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) d (n)
) ym
ON ym.[Year] = q.[Year] AND ym.[Month] = q.[Month]
) d
INNER JOIN Members m
ON m.MEMBER_ID = d.MEMBER_ID
WHERE d.MonthsPaid = 12
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden