Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Server 2008 r2 Query help 1

Status
Not open for further replies.

wbodger

Programmer
Apr 23, 2007
769
US
I have two tables that I want to join to get a report. Here is my initial query
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
 
How about using REPLACE() on PPT_CAHX_CASTATUS_CODE to remove the relevant strings and see what's left? Something like

Code:
CASE WHEN REPLACE(REPLACE(PPT_CAHX_CASTATUS_CODE, '99', ''), ',', '') = '' THEN 'X' END

Tamar
 
Hmm... I think that works. There was one change in the report, so I can apply this logic to one case and I think I am good. Thanks!

wb
 
OK, that gives me the report and now they want the column totals. Any ideas how to do this inside the SQL? I can do a loop for each column on the web page, but I would like to figure out how to do it inside the SQL if possible. Thoughts?

wb
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top