The PHYSICAL Table
If you’re trying to find patients who have particular items in their well visit templates, here’s a quick visual data dictionary to finding where data is in the PHYSICAL table.
PHYSICAL.GENAPP
PHYSICAL.DIET
PHYSICAL.RECOMMEND
PHYSICAL.PAT_INSTRUCTIONS
PHYSICAL.DWP
So to find patients born before 1/1/2003, who had well visit notes on or after 1/1/2016, where the word “counseling” or “Counseling” appeared anywhere in the Plan tab of their note, you would use the SQL:
SELECT PHYSICAL_1.PATNO, PHYSICAL_1.DATE1, PHYSICAL_1.GENAPP, PHYSICAL_1.DWP
FROM PHYSICAL PHYSICAL_1
INNER JOIN REGISTER REGISTER_1 ON
(REGISTER_1.PATNO = PHYSICAL_1.PATNO)
WHERE
( PHYSICAL_1.DATE1 >= ’01/01/2016 00:00:00′ )
AND ( REGISTER_1.BIRTHDAT <= ’01/01/2003 00:00:00′ )
AND (( PHYSICAL_1.DWP LIKE ‘%counseling%’ )
OR ( PHYSICAL_1.DWP LIKE ‘%Counseling%’ ))
ORDER BY PHYSICAL_1.PATNO
Using the visual Query Designer, the Search tab would look like: