In the meantime, you could also try this version, which provides a slightly different but possibly useable result also.
WITH MyData AS
(
SELECT a.[Contract], a.AdmissionDate, SumofCost, CountofMembers
FROM Members a
JOIN Admission b ON a.[Contract] = b.[Contract] AND
a.AdmissionDate = b.AdmissionDate
)
SELECT [Contract], AdmissionDate, SumofCost, CountofMembers, AvgCost
FROM
(
SELECT a.[Contract], a.AdmissionDate
,SumofCost=a.SumofCost + b.SumofCost
,CountofMembers=a.CountofMembers + b.CountofMembers
,AvgCost=CASE a.CountofMembers + b.CountofMembers WHEN 0 THEN 0
ELSE (a.SumofCost + b.SumofCost) / (a.CountofMembers + b.CountofMembers) END *
CASE WHEN ROW_NUMBER() OVER (ORDER BY AdmissionDate) < 3 THEN NULL ELSE 1 END
FROM MyData a
OUTER APPLY
(
SELECT SumofCost=SUM(SumofCost), CountofMembers=SUM(CountofMembers)
FROM
(
SELECT TOP 2 AdmissionDate, SumofCost, CountofMembers
FROM MyData b
WHERE a.AdmissionDate > b.AdmissionDate
ORDER BY AdmissionDate DESC
) b
) b
) b
These are the results (note difference is in the SumofCost, CountofMembers in the second row):
Contract AdmissionDate SumofCost CountofMembersAvgCost
0606 200701 NULL NULL NULL
0607 200702 31535.3200 188 NULL
0608 200703 155287.6000 278 558.588489
0609 200704 208026.7100 312 666.752275
The AvgCost column is the same in both versions.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St