As Sean suggested (1 and 2):

DECLARE @Admission TABLE

(

[Contract] VARCHAR(4)

,Admissiondate VARCHAR(6)

,SumofCost MONEY

);

INSERT INTO @Admission

SELECT '0606','200701',8639.38

UNION ALL SELECT '0607','200702',22895.94

UNION ALL SELECT '0608','200703',123752.28

UNION ALL SELECT null,'200704',61378.49;

DECLARE @Members TABLE

(

[Contract] VARCHAR(4)

,Admissiondate VARCHAR(6)

,CountofMembers INT

);

INSERT INTO @Members

SELECT '0606','200701',86

UNION ALL SELECT '0607','200702',102

UNION ALL SELECT '0608','200703',90

UNION ALL SELECT null,'200704',120;

I agree that there is a certain lack of clarity in requirements which expected results (Sean's #3) would resolve, but I'll give it a shot anyway and hope this is at least something to get you close.

WITH Tally (n) AS

(

SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3

),

MyData AS

(

SELECT a.[Contract], a.AdmissionDate, SumofCost, CountofMembers

FROM @Members a

JOIN @Admission b ON ISNULL(a.[Contract], '') = ISNULL(b.[Contract], '') AND

a.AdmissionDate = b.AdmissionDate

)

SELECT AdmissionDate=MAX(AdmissionDate), SummaryDate

,SumofCost=SUM(SumofCost), CountofMembers=SUM(CountofMembers)

,AvgCost=SUM(SumofCost)/CASE WHEN SUM(CountofMembers) = 0 THEN 1 ELSE SUM(CountofMembers) END

FROM

(

SELECT *, SummaryDate=DATEADD(month, n-1, CAST(AdmissionDate + '01' AS DATE))

FROM MyData

CROSS APPLY Tally

) a

GROUP BY SummaryDate

HAVING MAX(CAST(AdmissionDate + '01' AS DATE)) >= SummaryDate;