Here is a modified query to find the monthly totals of the more popular procedures in columns. We add an 'AbbrDesc' description column and a yearly total on the right side.
With help from our friends at OpenDental.
Enjoy!
drtmz
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/*839 Count of procedures completed for a specified year by month, separated by procedure code.*/
/*For the following procedure codes: D0120, D0272, D0274, D0330, D1110, D1120, D1351, D2330, D2331, D2332, D2391, D2392, D2393, D2750, D2751, D3310, D3320, D3330, D6057, D6058, D6061, D7210*/
SET @ReportYear='2014';
SELECT A.ProcCode, A.AbbrDesc,
SUM(CASE WHEN MONTH(A.ProcDate)=1 THEN 1 ELSE 0 END) AS 'Jan',
SUM(CASE WHEN MONTH(A.ProcDate)=2 THEN 1 ELSE 0 END) AS 'Feb',
SUM(CASE WHEN MONTH(A.ProcDate)=3 THEN 1 ELSE 0 END) AS 'Mar',
SUM(CASE WHEN MONTH(A.ProcDate)=4 THEN 1 ELSE 0 END) AS 'Apr',
SUM(CASE WHEN MONTH(A.ProcDate)=5 THEN 1 ELSE 0 END) AS 'May',
SUM(CASE WHEN MONTH(A.ProcDate)=6 THEN 1 ELSE 0 END) AS 'Jun',
SUM(CASE WHEN MONTH(A.ProcDate)=7 THEN 1 ELSE 0 END) AS 'Jul',
SUM(CASE WHEN MONTH(A.ProcDate)=8 THEN 1 ELSE 0 END) AS 'Aug',
SUM(CASE WHEN MONTH(A.ProcDate)=9 THEN 1 ELSE 0 END) AS 'Sept',
SUM(CASE WHEN MONTH(A.ProcDate)=10 THEN 1 ELSE 0 END) AS 'Oct',
SUM(CASE WHEN MONTH(A.ProcDate)=11 THEN 1 ELSE 0 END) AS 'Nov',
SUM(CASE WHEN MONTH(A.ProcDate)=12 THEN 1 ELSE 0 END) AS 'Dec',
COUNT(A.ProcCode) AS '=Total'
FROM (
SELECT pc.ProcCode, pl.ProcDate, pc.AbbrDesc
FROM procedurelog pl
INNER JOIN procedurecode pc ON pl.CodeNum=pc.CodeNum
AND pc.ProcCode IN ('D0330', 'D0120', 'D0274', 'D0272', 'D1110', 'D1120', 'D2751', 'D3310', 'D3320', 'D3330', 'D2330', 'D2331', 'D2332', 'D2391', 'D2392', 'D2393', 'D6061',
'D6058', 'D6057', 'D2750', 'D1351', 'D7210')
WHERE pl.Procstatus=2
AND YEAR(pl.ProcDate)=@ReportYear
) A
GROUP BY A.ProcCode