I have two tables that I want to join to get a report. Here is my initial query
And here is the output
Now, for what is wrong. For the No Cancer and the Unknown, they should only have an X if they are ALL zero or ALL 99. The caveat is that the number of records for each participant can be from 0 to x. I had thought that doing the stuff and for xml path() was the way to go and it works great to see if they are all null or if there is a 1 anywhere, but how do I check if each and every value is 0 or each and every value is 99?
Thanks,
wb
Code:
with cte as
(
select fg.STUDY_PARTICIPANT_ID,
STUFF(( SELECT ', ' + convert(varchar(2),fu.PPT_CAHX_CASTATUS_CODE )
FROM tblFollowUp AS fu
WHERE fg.study_participant_id = fu.STUDY_PARTICIPANT_ID and DELETEFLAG=0 and ENTRYFLAG=1
FOR XML PATH('')
), 1, 2, '') as PPT_CAHX_CASTATUS_CODE
FROM tblFinalG as fg
where DELETEFLAG=0
)
SELECT STUDY_PARTICIPANT_ID,
PPT_CAHX_CASTATUS_CODE,
case
when PPT_CAHX_CASTATUS_CODE IS null then 'x'
end as 'No Follow Up',
case
when PPT_CAHX_CASTATUS_CODE like '%0%' then 'x' -- not correct, needs to be ALL 0.
end as 'No Cancer',
case
when PPT_CAHX_CASTATUS_CODE like '%1%' then 'x'
end as 'Cancer',
case
when PPT_CAHX_CASTATUS_CODE like '%99%' then 'x' -- not correct, needs to be all 99.
end as 'Unknown'
FROM cte
And here is the output
Code:
STUDY_PARTICIPANT_ID PPT_CAHX_CASTATUS_CODE No Follow Up No Cancer Cancer Unknown
29235400037 0, 0 NULL x NULL NULL
29135400237 99, 0 NULL x NULL x
29135400245 NULL x NULL NULL NULL
29135400252 NULL x NULL NULL NULL
29135400260 0, 1 NULL x x NULL
29135400369 99, 99 NULL NULL NULL x
Now, for what is wrong. For the No Cancer and the Unknown, they should only have an X if they are ALL zero or ALL 99. The caveat is that the number of records for each participant can be from 0 to x. I had thought that doing the stuff and for xml path() was the way to go and it works great to see if they are all null or if there is a 1 anywhere, but how do I check if each and every value is 0 or each and every value is 99?
Thanks,
wb