Nested query to get add'l data afterward

[LT2]

Using SQL 2000

I've got a nested query that pulls the top 25 most expensive clients by treatment date where Provider is not included because a client can be at more than one Provider during the treatment time. When Provider is included, I only get the most expensive by Provider and not most expense overall for a client.

Now I want to know of those 25 which Providers make up the cost of the client's services. How can I accomplish this?

SELECT TOP 25 c.ClientID, c.Client, c.TotalPd, Min(c.Age)as 'Age', c.TotalUnits
FROM
(
	Select b.ClientID, b.Client, sum(b.Payor_Amount)as 'TotalPd', sum(b.Units)as 'TotalUnits', 
	 Min(b.Age)as 'Age'
	From 
	(
		
		Select *
		From
		(
select  c.ClientID, (dg.LastName+ ','+ ' '+ dg.FirstName)as 'Client',
	t.TreatDate as 'TxDate', tc.TreatCode as 'TxCode',
	t.TreatUnits as 'Units', (isnull(yn.Val, 'No')) as 'Billed', (isnull (bds.Stat, 'No'))
	as 'Bill Status',  pv.TotPayPdAmt as 'Payor_Amount', pv.TotPatpdAmt as 'Copay',
	p.PayShortName as 'Payor', tp.payresponssequence as 'Payor Order', p2.payshortname as 'Paid_As', 
	pv.payresponsibilitysequence as 'Paid Payor Order', bds.billdetailstatusid, b.adjudicated,
	b.BillEnum, Min(adm.AgeAtAdmission) as 'Age', c.ClientNum
from tClient c
	left join Treat t on c.ClientNum = t.ClientNum
	left join YesNo yn on t.Billed = yn.ID
	left join billdet bd on t.treatid = bd.treatid
	left join V_Payment pv on bd.BillDetID = pv.BillDetID  
	left join bill b on bd.billid = b.billid
	left join tpayor p2 on pv.payorid = p2.payorid
	inner join ProvTreatCode ptc on t.TreatCodeID = ptc.TreatmentID
	inner join TreatCode tc on ptc.TreatCodeID = tc.TreatCodeID
	inner join TreatPayment tp on t.TreatID = tp.TreatID
	inner join Payor p on tp.PayID = p.PayID
	inner join tDemog dg on c.ClientNum = dg.ClientNum
	left join BillDetailStat bds on bd.BillDetailStat = bds.BillDetailStatID
	inner join tDemogAddr dga on dg.ClientNum = dga.ClientNum
	inner join tAdmit adm on c.ClientNum = adm.ClientNum
where t.TreatDate between '10/1/2010' and '3/23/2011'
	and bd.billdetailstat = 2  
	and b.adjud = 1    
	and tp.PayResponsSeq = pv.PayResponsSeq
group by c.ClientID,t.TreattDate,tc.TreatCode,t.TreatUnits,yn.Val,bds.Status,pv.TotPayPdAmt,
	p.PayShortName,tp.payresponsseq,p2.payshortname,pv.payresponsseq,bds.billdetstatid,
	b.adjud,b.BillEnum,adm.AgeAtAdmit,pv.TotPatpdAmt,dg.LastName,dg.FirstName,c.ClientNum
)a
		--Where a.Age < 18               -- Adolescents
		--Where a.Age between 18 and 25  -- Young Adults
		--Where a.Age between 26 and 50  -- Adults
		--Where a.Age > 50               -- Older Adults
		-- No age stipulation = overall
		)b
Group by b.ClientID,b.Client,b.Age
)c
GROUP BY c.ClientID, c.Client, c.TotalPd, c.Age, c.TotalUnits
ORDER BY c.TotalPd desc

tProvider is usually joined to tClient on tProvider.AgencyNum = tClient.AgencyNum