Query for Carrier, Patients and Provider

[drtmz]

I need a query for the total number of patients seen over a time period by carrier and provider.
I'm trying to see how many capitation patients each provider treats per month.
I did use the one that gives me CarrierName and Total Patients but I need to add Provider to the mix.
We are using 7.5.25
Any help would be appreciated.

drtmz

[drtmz]

I found the following code:

SET @Start='2011-01-01' , @Before='2011-01-31';
/*Adjust above Dates as needed*/
SELECT carrier.CarrierName, COUNT(DISTINCT claimproc.PatNum) AS 'Patients'
FROM carrier
INNER JOIN insplan ON carrier.CarrierNum=insplan.CarrierNum
INNER JOIN claim ON insplan.PlanNum=claim.PlanNum
INNER JOIN claimproc ON claim.ClaimNum=claimproc.ClaimNum
INNER JOIN procedurelog ON claimproc.ProcNum=procedurelog.ProcNum
INNER JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum
AND procedurelog.ProcDate >= @Start
AND procedurelog.ProcDate < @Before
AND ProcStatus=2
GROUP BY CarrierName
ORDER BY CarrierName;

This gives me CarierName and total Patients.
What I need is to break this down by provider so the result would be:

CarrierName...Doc1...Doc2...Doc3...Hyg1...Hyg2...Patients(total)

This type of report would seem to be useful in most practices using OpenDental.
In our case we need to track the Capitation visits by each provider.

Also I tried to play around with the ClaimsProc table. The necessary information is there
but I wasn't able to break it down properly to the needed columns.

drtmz