This should get you started 😎
DECLARE @REF_DATE DATE = '2014-06-01';
DECLARE @MEM_PAY_DATE TABLE
(
PAYMENT_ID INT NOT NULL
,MEMBER_ID INT NOT NULL
,PAYMENT_DATE DATE NOT NULL
,PAYMENT_AMOUNT INT NOT NULL
)
INSERT INTO @MEM_PAY_DATE (PAYMENT_ID,MEMBER_ID,PAYMENT_DATE,PAYMENT_AMOUNT)
SELECT
PAYMENT_ID,MEMBER_ID,CAST(PAYMENT_DATE AS DATE) AS PAYMENT_DATE,PAYMENT_AMOUNT
FROM (VALUES
( 1,2,'2013-01-01',500.00),( 2,2,'2013-02-01',500.00),( 3,2,'2013-03-01',500.00)
,( 4,2,'2013-04-01',500.00),( 5,2,'2013-05-01',500.00),( 6,2,'2013-06-01',500.00)
,( 7,2,'2013-07-01',500.00),( 8,2,'2013-08-01',500.00),( 9,2,'2013-09-01',500.00)
,(10,2,'2013-10-01',500.00),(11,2,'2013-11-01',500.00),(12,2,'2013-12-01',500.00)
,(13,2,'2014-01-01',500.00),(14,2,'2014-02-01',500.00),(15,2,'2014-03-01',500.00)
,(16,2,'2014-04-01',500.00),(17,2,'2014-05-01',500.00),(18,2,'2014-06-01',500.00)
,(19,2,'2014-07-01',500.00),( 1,3,'2013-01-01',500.00),( 2,3,'2013-02-01',500.00)
,( 3,3,'2013-03-01',500.00),( 4,3,'2013-04-01',500.00),( 5,3,'2013-05-01',500.00)
,( 6,3,'2013-06-01',500.00),( 7,3,'2013-07-01',500.00),( 8,3,'2013-08-01',500.00)
,( 9,3,'2013-09-01',500.00),(10,4,'2013-10-01',500.00),(11,4,'2013-11-01',500.00)
,(12,4,'2013-12-01',500.00),(13,4,'2014-01-01',500.00),(14,4,'2014-02-01',500.00)
,(15,4,'2014-03-01',500.00),(16,4,'2014-04-01',500.00),(17,4,'2014-05-01',500.00)
,(18,4,'2014-06-01',500.00),(19,4,'2014-07-01',500.00),( 8,2,'2014-12-01',500.00)
) AS X(PAYMENT_ID,MEMBER_ID,PAYMENT_DATE,PAYMENT_AMOUNT);
;WITH DIST_MONTHS AS
(SELECT * FROM
(
SELECT
MPD.MEMBER_ID
,MPD.PAYMENT_DATE
,ROW_NUMBER() OVER
(
PARTITION BY MEMBER_ID, YEAR(MPD.PAYMENT_DATE), MONTH(MPD.PAYMENT_DATE)
ORDER BY YEAR(MPD.PAYMENT_DATE) ASC, MONTH(MPD.PAYMENT_DATE) ASC
) AS MPD_RID
,(YEAR(MPD.PAYMENT_DATE) * 100) + MONTH(MPD.PAYMENT_DATE) AS MPD_YM
FROM @MEM_PAY_DATE MPD
) AS X WHERE X.MPD_RID = 1)
,FULL_YEAR_PAY AS
(
SELECT
DM.MEMBER_ID
,COUNT(DM.PAYMENT_DATE) OVER
(PARTITION BY DM.MEMBER_ID) AS MP_COUNT
,ROW_NUMBER() OVER
(
PARTITION BY DM.MEMBER_ID
ORDER BY (SELECT NULL)
) AS MP_RID
FROM DIST_MONTHS DM
WHERE DM.MPD_YM BETWEEN ((YEAR(DATEADD(MONTH,-11,@REF_DATE)) * 100) + MONTH(DATEADD(MONTH,-11,@REF_DATE)))
AND ((YEAR(@REF_DATE) * 100) + MONTH(@REF_DATE))
)
SELECT
*
FROM FULL_YEAR_PAY FYP
WHERE FYP.MP_RID = 1
AND FYP.MP_COUNT = 12