Declare @NameList varchar(1000)

Set @NameList = ',''CAMPBELL, JULIE'',''JIA, YUXIA'',''Lee, Sangmin'',''LEE, YOOMI'',''LIM, EMERSON'',''Oberstein, Paul'',''Pan, Beiqing'',
''SCHECTER, JORDAN'',''SHOHARA, RYO'',''WU, DAVID'',''Xu, Xunhai'',''Zikria, Jennifer Marie'','

Select
....,
CASE when @NameList like '%,'''+u.displayname+''',%' THEN 'FELLOWS'
	when  @NameList like '%,'''+u2.displayname+''',%' THEN 'FELLOWS'
	when ....

quote:

---

Originally posted by robvolk

Or how about adding a Physicians table and add each name and type of physician? Then you can just JOIN to that table and display the type without the long CASE expression. And you don't have to change the query if you get a new doctor, just add them to the table.

---

quote:

---

Originally posted by denis_the_thief

quote:

---

Originally posted by robvolk

Or how about adding a Physicians table and add each name and type of physician? Then you can just JOIN to that table and display the type without the long CASE expression. And you don't have to change the query if you get a new doctor, just add them to the table.

---

I really agree.

---

CREATE TABLE #docs (Name VARCHAR(60) NOT NULL, TYPE VARCHAR(12) NOT NULL)

INSERT #docs(name, TYPE) SELECT 'CAMPBELL, JULIE', 'FELLOW'
UNION ALL SELECT 'JIA, YUXIA', 'FELLOW'
UNION ALL SELECT 'Lee, Sangmin', 'FELLOW'
UNION ALL SELECT 'LEE, YOOMI', 'FELLOW'
UNION ALL SELECT 'LIM, EMERSON', 'FELLOW'
UNION ALL SELECT 'Oberstein, Paul', 'FELLOW'
UNION ALL SELECT 'Pan, Beiqing', 'FELLOW'
UNION ALL SELECT 'SCHECTER, JORDAN', 'FELLOW'
UNION ALL SELECT 'SHOHARA, RYO', 'FELLOW'
UNION ALL SELECT 'WU, DAVID', 'FELLOW'
UNION ALL SELECT 'Xu, Xunhai', 'FELLOW'
UNION ALL SELECT 'Zikria, Jennifer Marie', 'FELLOW'

INSERT #docs(name, TYPE) 
SELECT 'Crew, Katherine', 'ATTENDING'
UNION ALL SELECT 'Diuguid, David L', 'ATTENDING'
UNION ALL SELECT 'EISENBERGER, ANDREW', 'ATTENDING'
UNION ALL SELECT 'Fine, Robert L', 'ATTENDING'
UNION ALL SELECT 'Flamm, Michael J', 'ATTENDING'
UNION ALL SELECT 'Gelmann, Edward', 'ATTENDING'
UNION ALL SELECT 'Halmos, Balazs', 'ATTENDING'
UNION ALL SELECT 'Hershman, Dawn L', 'ATTENDING'
UNION ALL SELECT 'Jia, Yuxia', 'ATTENDING'
UNION ALL SELECT 'Kalinsky, Kevin', 'ATTENDING'
UNION ALL SELECT 'Matushansky, Igor', 'ATTENDING'
UNION ALL SELECT 'Maurer, Mathew', 'ATTENDING'
UNION ALL SELECT 'Mukherjee, Siddhartha', 'ATTENDING'
UNION ALL SELECT 'Neugut, Alfred I', 'ATTENDING'
UNION ALL SELECT 'OBERSTEIN, PAUL', 'ATTENDING'
UNION ALL SELECT 'Petrylak, Daniel P', 'ATTENDING'
UNION ALL SELECT 'Phillips, Adrienne', 'ATTENDING'
UNION ALL SELECT 'Raza, Azra', 'ATTENDING'
UNION ALL SELECT 'SAIF, MOHAMMED', 'ATTENDING'
UNION ALL SELECT 'Saif, Muhammad Wasif', 'ATTENDING'
UNION ALL SELECT 'SAVAGE, DAVID', 'ATTENDING'
UNION ALL SELECT 'Savage, David G', 'ATTENDING'
UNION ALL SELECT 'Shohara, Ryo', 'ATTENDING'
UNION ALL SELECT 'SIEGAL, A', 'ATTENDING'


SELECT cv.ClientDisplayName	AS Patient, 
cv.IDCode	 AS MRN, 
cv.VisitIDCode	 AS VISIT_ID, 
cv.guid	 AS ClientVisitGUID,
cd.GUID	 AS ClientDocGUID, 
d1.Type AS PHYSICIAN_TYPE, 
cd.DocumentName	 AS NOTE_NAME, 
cd.AuthoredDtm	 AS SERVICE_DATE, 
u2.displayname	 AS AUTHORED_BY,
u.DisplayName	 AS EnteredBy,
dbo.CV3HealthIssueDeclaration.text	 AS ADMISSION_DIAGNOSIS 
FROM dbo.CV3ClientDocument AS cd WITH (NOLOCK) 
INNER JOIN dbo.CV3ClientVisit AS cv WITH (NOLOCK) ON cd.ClientVisitGUID = cv.GUID AND cv.TypeCode = 'Inpatient' 
INNER JOIN dbo.CV3User AS u WITH (NOLOCK) ON cd.UserGUID = u.GUID 
LEFT JOIN #docs d1 ON u.displayname=d1.Name
LEFT JOIN dbo.CV3User u2 ON u.GUID=u2.GUID AND d1.Name=u2.Displayname
INNER JOIN dbo.CV3Location AS loc WITH (NOLOCK) ON cv.CurrentLocationGUID = loc.GUID AND ParentGUID = '4000001145061001'
INNER JOIN dbo.CV3HealthIssueDeclaration WITH (NOLOCK) ON cv.GUID = dbo.CV3HealthIssueDeclaration.ClientVisitGUID AND 
cv.ClientGUID = dbo.CV3HealthIssueDeclaration.ClientGUID AND dbo.CV3HealthIssueDeclaration.TypeCode = 'Admitting Dx' 
AND dbo.CV3HealthIssueDeclaration.status='active'
INNER JOIN dbo.CV3User u2 WITH (NOLOCK) ON cd.AuthoredProviderGUID = u2.GUID 
WHERE cd.AuthoredDtm BETWEEN @start_date AND @end_date

quote:

---

Originally posted by sharona

Let me be blunt; what do you think you did right! I do not see anything. i didnt design the database and name the fields. the id codes are actual field names.

thank you those who are actually trying to help and not critize. i could create a temp table, but not so sure how to join that back the the query. ill give it a shot. thanks again for the help.

---

quote:

---

Originally posted by denis_the_thief

quote:

---

Originally posted by sharona

Let me be blunt; what do you think you did right! I do not see anything. i didnt design the database and name the fields. the id codes are actual field names.

thank you those who are actually trying to help and not critize. i could create a temp table, but not so sure how to join that back the the query. ill give it a shot. thanks again for the help.

---

You're taking it all wrong My Sharona!

This is a forum, it is within our rights to suggest better ways to do things. I looked at your original issue and because of the lack of indentation in your posting I found it too dificult to answer. So that is another suggestion, when you post, try to use indentation. Again, not intended to offend you, this is a good suggestion. You need indent your code then to use the '#'.

---