Hi everyone,
having difficulty writing a case statement that removes any PATIENT CLASS that changed from 'CC' to 'HC'
My code is pulling the correct values but sometimes picks up Patient class that was previously CC and changed to HC
ex; its picking up a record that was CC and changed to HC on '2013-09-14' and it is accounting for the HC range I am trying to query
What I am trying to accomplish to get the number of records that are HC and not CC on '2013-07-01' but the pts.status_end_date must stay >= '2013-07-01'
SELECT DISTINCT
PB.PATIENT_CODE
FROM PT_STATUS AS PTS INNER JOIN
PT_ADMISSION AS PTA ON PTS.ADMISSION_ID = PTA.ADMISSION_ID FULL OUTER JOIN
A_PATIENT_STATUS AS APS ON PTS.ADMIN_SET_ID = APS.ADMIN_SET_ID AND PTS.STATUS_CODE = APS.STATUS_CODE INNER JOIN
A_PATIENT_CLASS AS APC ON PTS.ADMIN_SET_ID = APC.ADMIN_SET_ID AND PTS.PATIENT_CLASS = APC.PATIENT_CLASS_CODE INNER JOIN
PT_BASIC AS PB ON PTS.PATIENT_ID = PB.PATIENT_ID AND PTA.PATIENT_ID = PB.PATIENT_ID INNER JOIN
O_DATASET AS OD ON PB.DATASET_ID = OD.DATASET_ID
WHERE (OD.DATASET_NAME = 'Business Name') AND (OD.DATASET_ID = '3')
AND PTA.ADMIT_DATE <= '2013-07-01' AND PTA.TERMINATION_DATE >= '2013-07-01'
AND (PTS.STATUS_END_DATE >= '2013-07-01')
AND (PTS.PATIENT_CLASS IN ('HO'))
Sorry I know this seems confusing
Its confusing to me as well... 
I will try to elaborate more on any questions asked
Thanks again,
And sorry about my newbie question
--
Matt