The unique thing about this query is that it creates a grid with the months across the top. For each
Referral Source/Year there is a single row.
SELECT
b.refname as "Ref’d By",
YEAR(c.trandate) as "Year",
COUNT (DISTINCT (IF MONTH(c.trandate) = 1 THEN c.ptnum ENDIF)) as "Jan Count",
SUM(IF MONTH(c.trandate) = 1 THEN c.amount ELSE 0 ENDIF) as "Jan Chgs",
COUNT (DISTINCT (IF MONTH(c.trandate) = 2 THEN c.ptnum ENDIF)) as "Feb Count",
SUM(IF MONTH(c.trandate) = 2 THEN c.amount ELSE 0 ENDIF) as "Feb Chgs",
COUNT (DISTINCT (IF MONTH(c.trandate) = 3 THEN c.ptnum ENDIF)) as "Mar Count",
SUM(IF MONTH(c.trandate) = 3 THEN c.amount ELSE 0 ENDIF) as "Mar Chgs",
COUNT (DISTINCT (IF MONTH(c.trandate) = 4 THEN c.ptnum ENDIF)) as "Apr Count",
SUM(IF MONTH(c.trandate) = 4 THEN c.amount ELSE 0 ENDIF) as "Apr Chgs",
COUNT (DISTINCT (IF MONTH(c.trandate) = 5 THEN c.ptnum ENDIF)) as "May Count",
SUM(IF MONTH(c.trandate) = 5 THEN c.amount ELSE 0 ENDIF) as "May Chgs",
COUNT (DISTINCT (IF MONTH(c.trandate) = 6 THEN c.ptnum ENDIF)) as "Jun Count",
SUM(IF MONTH(c.trandate) = 6 THEN c.amount ELSE 0 ENDIF) as "Jun Chgs",
COUNT (DISTINCT (IF MONTH(c.trandate) = 7 THEN c.ptnum ENDIF)) as "Jul Count",
SUM(IF MONTH(c.trandate) = 7 THEN c.amount ELSE 0 ENDIF) as "Jul Chgs",
COUNT (DISTINCT (IF MONTH(c.trandate) = 8 THEN c.ptnum ENDIF)) as "Aug Count",
SUM(IF MONTH(c.trandate) = 8 THEN c.amount ELSE 0 ENDIF) as "Aug Chgs",
COUNT (DISTINCT (IF MONTH(c.trandate) = 9 THEN c.ptnum ENDIF)) as "Sep Count",
SUM(IF MONTH(c.trandate) = 9 THEN c.amount ELSE 0 ENDIF) as "Sep Chgs",
COUNT (DISTINCT (IF MONTH(c.trandate) = 10 THEN c.ptnum ENDIF)) as "Oct Count",
SUM(IF MONTH(c.trandate) = 10 THEN c.amount ELSE 0 ENDIF) as "Oct Chgs",
COUNT (DISTINCT (IF MONTH(c.trandate) = 11 THEN c.ptnum ENDIF)) as "Nov Count",
SUM(IF MONTH(c.trandate) = 11 THEN c.amount ELSE 0 ENDIF) as "Nov Chgs",
COUNT (DISTINCT (IF MONTH(c.trandate) = 12 THEN c.ptnum ENDIF)) as "Dec Count",
SUM(IF MONTH(c.trandate) = 12 THEN c.amount ELSE 0 ENDIF) as "Dec Chgs",
COUNT(DISTINCT a.ptnum) as "# Count", SUM(c.amount) as "TotChgs"
FROM
sos.patients a
JOIN sos.refsrcs b on a.refsrcnum = b.refsrcnum
JOIN sos.journal c on a.ptnum = c.ptnum
WHERE
c.trantype = 'S'
AND c.trandate > '2000-12-31'
GROUP BY
b.refname,
YEAR(c.trandate)
ORDER BY
b.refname,
YEAR(c.trandate)