I need a query that can be done by date range for charge with a CPT code of 90801 and will print out the ID, Name, Primary Dx, Primary Provider, and Office location (which is taken from Box 32 in the CSU that is attached to the charge).
SELECT
a.id AS "ID",
(a.lastname+', '+a.firstname) AS "Name",
e.dxcode AS "Primary Dx",
f.provcode AS "Primary Provider",
g.shorthand AS "Office Location"
FROM
sos.patients a
JOIN sos.journal b ON a.ptnum = b.ptnum
JOIN sos.jcharges c ON b.jnum = c.jnum
JOIN sos.ptcsu d ON c.ptcsunum = d.ptcsunum
LEFT OUTER JOIN sos.dx e ON d.dx1 = e.dxnum
LEFT OUTER JOIN sos.providers f ON a.providernum = f.providernum
LEFT OUTER JOIN sos.facilities g ON d.facilitynum = g.facilitynum
JOIN sos.services h ON c.servicenum = h.servicenum
WHERE
h.cptcode = '90801'
AND b.trandate BETWEEN '2008-01-01' AND '2008-12-31'
ORDER BY "Name"