Completed Appointments by Provider (for date range)

[V Suite]

Based on Query # 142, which gives the total number of appointments in a given range, the following query separates tabulates the appointments by provider.

Code: Select all

SET @FromDate='2009-12-01' , @ToDate='2009-12-31';
SELECT ProvNum, (SELECT Count(*) FROM appointment WHERE aptstatus=2 AND ProvNum=provider.ProvNum AND AptDateTime BETWEEN @FromDate AND @ToDate) AS '#Appointments' FROM provider

Now if only there was a way to make a query to tabulate by month horizontally and provider vertically.

[V Suite]

Sorry, this does not work if FromDate = ToDate. To fix, use

Code: Select all

SET @FromDate='2009-12-01' , @ToDate='2009-12-31';
SELECT ProvNum, (SELECT Count(*) FROM appointment WHERE aptstatus=2 AND ProvNum=provider.ProvNum AND DATE(AptDateTime) BETWEEN @FromDate AND @ToDate) AS '#Appointments' FROM provider

Cheers