I need a list of patients with certain diagnoses (309.0, 309.21, 309.24, 309.28, 309.3, or 309.4) who were seen for treatment this year.
SELECT
/*columns to include in list */
lastname,
firstname,
dob,
socsec
FROM
sos.patients a JOIN sos.ptcsudx b ON a.ptcsunum = b.ptcsunum
WHERE
/* at least one charge in ledger during the current year */
(SELECT count(*) FROM sos.journal
WHERE trantype = 'S' AND amount > 0
AND ptnum = a.ptnum
AND trandate BETWEEN '2008-01-01' AND '2008-12-31') > 0
/* one of patient's dx's must be in the specified list of codes */
AND
(DXCODE1 IN ('309.0','309.21','309.24','309.28','309.3','309.4') OR
DXCODE2 IN ('309.0','309.21','309.24','309.28','309.3','309.4') OR
DXCODE3 IN ('309.0','309.21','309.24','309.28','309.3','309.4') OR
DXCODE4 IN ('309.0','309.21','309.24','309.28','309.3','309.4') )
ORDER BY
lastname, firstname, dob