SQLTeam.com | Weblogs | Forums

Tricky case statement


#1

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... :frowning:
I will try to elaborate more on any questions asked

Thanks again,
And sorry about my newbie question

--
Matt


#2

Have you tried a LEFT JOIN to find any former CC values?


#3

Yes I've' tried but no luck..


#4

If you can provide some sample data and expected output then it will be easier to help you.


#5

Okay the problem is solved
I have another question but I'll open another topic

Thanks guys!

--
M