3 Month Rolling Average

Question

I need some help calculating a rolling 3 month average cost from the two dataset below. Which is the 3 month Average of Dataset1 / Dataset 2. I'm not sure if using cte is the correct route. Any insight is appreciated.

  Dataset 1:

        SELECT(factAdmissions.ContractCode + '-' +factAdmissions.BenefitPlanCode) AS [Contract Code],
        factAdmissions.AdmitCCYYMM,
        ISNULL(sum(AmountPaid),0)As [Amount Paid]
        FROM factAdmissions

        WHERE factAdmissions.AdmitCCYYMM >='200701'
        AND factAdmissions.AcuteSNFIndAdmit ='a'
        AND factAdmissions.ContractCode Is Not Null
        AND factAdmissions.BenefitPlanCode Is Not Null
        AND BusinessUnitCode In('EP','HF', 'VN')
        AND AdmitCCYYMM BETWEEN (@StartDate) AND (@EndDate)

        AND factAdmissions.ContractCode + '-' +factAdmissions.BenefitPlanCode IN (@Product)

        Group by factAdmissions.ContractCode,
        factAdmissions.BenefitPlanCode,
        factAdmissions.AdmitCCYYMM
        Order by factAdmissions.ContractCode,
        factAdmissions.BenefitPlanCode,
        factAdmissions.AdmitCCYYMM;



        Dataset2:

        Select

        (factMembership.ContractCode+'-'+ factMembership.BenefitPlanCode) As Product,
        EffectiveCCYYMM,
        ISNULL(count(Distinct MemberId),0) As MemberCount
        From factMembership
        Where EffectiveCCYYMM >= '200701'
        AND BusinessUnitCode In('EP','HF', 'VN')
        AND EffectiveCCYYMM BETWEEN (@StartDate) AND (@EndDate)

        AND factMembership.ContractCode + '-' +factMembership.BenefitPlanCode IN (@Product)


        Group by
        ContractCode,
        BenefitPlanCode,
        EffectiveCCYYMM

        Order by 1; 

    Dataset 1: Output 201101 201102 201103
    Cost $420,627 $73,608 $93,792 


    Dataset 2:Output 201103
    Memberscount 400



    Desired Output: 201103 
    3 Month Rolling Average $490.02 

Answer 1

DECLARE @Admission TABLE (
    Code VARCHAR(100),
    YearMonth CHAR(6),
    TotalPaid DECIMAL
)

DECLARE @Membership TABLE (
    Code VARCHAR(100),
    YearMonth CHAR(6),
    TotalMembers int
)

INSERT @Admission (Code, YearMonth, TotalPaid) VALUES 
('123', '201301', 10),
('xyz', '201301', 20),
('123', '201302', 15),
('xyz', '201302', 15),
('123', '201303', 30),
('xyz', '201303', 40),
('123', '201304', 30),
('xyz', '201304', 40),
('123', '201305', 15),
('xyz', '201305', 15),
('123', '201306', 20),
('xyz', '201306', 10)

INSERT @Membership (Code, YearMonth, TotalMembers) VALUES 
('123', '201301', 1),
('xyz', '201301', 2),
('123', '201302', 1),
('xyz', '201302', 3),
('123', '201303', 3),
('xyz', '201303', 5),
('123', '201304', 3),
('xyz', '201304', 5),
('123', '201305', 1),
('xyz', '201305', 3),
('123', '201306', 1),
('xyz', '201306', 2)


SELECT 
    a.Code,
    a.YearMonth,
    TotalPaid,
    TotalMembers,
    TotalPaid / CAST(TotalMembers AS FLOAT) CostPerMember,
    CAST(a.YearMonth + '01' AS date) AS ProperDate
INTO #Values
FROM @Admission a
    INNER JOIN @Membership m ON a.Code = m.Code AND a.YearMonth = m.YearMonth

SELECT
    main.Code,
    main.YearMonth,
    main.TotalPaid,
    main.TotalMembers,
    COUNT(*) AS PointsToAverage,
    SUM(sub.TotalPaid) AS TotalPaidOverPeriod,
    SUM(sub.TotalMembers) AS TotalMembersOverPeriod,
    SUM(sub.CostPerMember) AS TotalCostPerMemberOverPeriod,
    ROUND(SUM(sub.TotalPaid) / CAST(COUNT(*) AS FLOAT), 2) AS RollingAverageTotalPaid,
    ROUND(SUM(sub.TotalMembers) / CAST(COUNT(*) AS FLOAT), 2) AS RollingAverageTotalMembers,
    ROUND(SUM(sub.CostPerMember) / CAST(COUNT(*) AS FLOAT), 2) AS RollingAverageCostPerMember
FROM
    #Values main
    INNER JOIN #Values sub ON main.Code = sub.Code
        AND sub.ProperDate BETWEEN DATEADD(month, -2.9, main.ProperDate) AND main.ProperDate
GROUP BY
    main.Code,
    main.YearMonth,
    main.TotalPaid,
    main.TotalMembers