How do I change it so the pat_enc.contact_date is a date? i've tried to_date('pat_enc.contact_date','mm/dd/yyyy')
SELECT PAT_ENC.PAT_ID,MAX(PAT_ENC.CONTACT_DATE)AS LAST_SEEN
,MAX(CASE WHEN PAT_ENC.CONTACT_DATE BETWEEN TO_DATE('06/01/2014','MM/DD/YYYY') AND TO_DATE('06/01/2015','MM/DD/YYYY') THEN pat_enc.contact_date ELSE 'N' END) AS SEEN_LAST_YEAR
FROM PAT_ENC
WHERE PAT_ENC.CONTACT_DATE BETWEEN TO_DATE('06/01/2013','MM/DD/YYYY') AND TO_DATE('06/01/2015','MM/DD/YYYY')
AND PAT_ENC.ENC_TYPE_C IN ('101','200','227','280','300','334','335','369','5376','701','702','703','706','708')
AND PAT_ENC.DEPARTMENT_ID = 10170107
GROUP BY PAT_ENC.PAT_ID
ORDER BY 1,2
When using T-SQL use the CAST or CONVERT command. In the example SQL should handle the conversion for you. PAT_ENC.CONTACT_DATE BETWEEN '06/01/2014' AND '06/01/2015' should work
When I try this it says "not enough arguments for function"
,MAX(CASE WHEN PAT_ENC.CONTACT_DATE BETWEEN TO_DATE('06/01/2014','MM/DD/YYYY') AND TO_DATE('06/01/2015','MM/DD/YYYY') THEN convert(to_date('pat_enc.contact_date','mm/dd/yyyy')) ELSE 'N' END) AS SEEN_LAST_YEAR
T-Sql (Microsoft's version of SQL) does not have the function TO_DATE. So I am guessing you are using another RDBMS. This forum is for Microsoft SQL Sever, so there may not be many people who can help you with Oracle or other systems. Also, in SQL Server, when you invoke a function without sufficient number of arguments, the error message gives the name of the function to make it easier for you to identify it.
You might get faster and better answers at a forum specific to the RDBMS you are using.