selecting rows depending on column value

Question

I have a table like below, 

Here actually only 7 questions are there with diff answers in diff rows. For e.g. 

question id = 32568 has 3 rows but in department column value is medical or nursing and allied health

so actually i want to split into to parts, medical or nursing (here choose only 1 ans since or condition),  and allied health.

So for this q i want only 2 rows , 

32568 Not met Patients are screened for nutritional status... Medical or Nursing and Allied Health

32568 met         Patients are screened for nutritional status ... Medical or Nursing and Allied Health

------------------------

So similarly,

for question id = 32571 has 2 rows and in department column value is medical and nursing. Since and is condition both should be met or not met. But here 1 is met and 1 is not met. So final is not met. So for this Q i want only one row

32571 Not met Patients are screened for pain Medical and Nursing

---------

so depending on department column and how many rows are there for particular Q I have get final rows for answers.

medical and nursing and allied health then take only 1 row

medical or nursing or allied health then take only 1 row

medical and (nursing or allied health)  then take only 2 rows. One for medical and other for (nursing or allied health) - in nursing or allied health also if both not met, ans = not met, both met, ans = met and if one met and one not met ans = met since or condition. 

so have diff combinations of "and" and "or" with 3 department values.

and and, or or, and or, or and, and, or , only one department.  

How can we achieve this? any clues or ideas?

---

h2007

Answer 1

Please post CREATE TABLE statements for your table and the sample data as INSERT statements.We cannot copy and paste from screenshots. It would also help to see a strict listing of the desired result. Finally, don't forget to tell which version of SQL Server you are using.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Erland makes a good point. I'll give it a go based on your notes.

select Distinct ScheduleQuestionId, ans, QuestionText, department

from MyTable where CHARINDEX(' or ',Department)>0

union all

select Distinct ScheduleQuestionId, ans, QuestionText, department

from MyTable a

where CHARINDEX(' or ',Department)=0

and ans='Met'

and not exists (select * from MyTable b where a.ScheduleQuestionId=b.ScheduleQuestionId and a.QuestionText=b.QuestionText and a.department=b.department and b.ans='Not Met')

Hope that helps,

---

Richard

Answer 3

Hi h2007,

How about the following code ?

create table Met_Question
(
 ScheduleQuestionID int,
 ans varchar(20),
 QuestionText varchar(max),
 department varchar(64)
 )

 insert into Met_Question values
 (32568,'Not met','Patients are screened for nutritional status and r..','Medical or Nursing and Allied Health'),
 (32568,'Not met','Patients are screened for nutritional status and r..','Medical or Nursing and Allied Health'),
 (32568,'Met','Patients are screened for nutritional status and r..','Medical or Nursing and Allied Health'),
 (32569,'Met','Patients are screened for functional needs and r..','Medical or Nursing and Allied Health'),
 (32569,'Not met','Patients are screened for functional needs and r..','Medical or Nursing and Allied Health'),
 (32569,'Met','Patients are screened for functional needs and r..','Medical or Nursing and Allied Health'),
 (32570,'Met','Need for discharge planning is assessed','0'),
 (32571,'Not met','Patients are screened for pain','Medical and Nursing'),
 (32571,'Met','Patients are screened for pain','Medical and Nursing'),
 (32572,'Met','When pain is identified, further assessment inclu..','Medical and Nursing'),
 (32572,'Not met','When pain is identified, further assessment inclu..','Medical and Nursing'),
  (32573,'Met','b.Mention of the location of the pain','Medical and Nursing'),
 (32573,'Not met','b.Mention of the location of the pain','Medical and Nursing'),
  (32574,'Met','c.Define the character of the pain','Medical and Nursing'),
 (32574,'Not met','c.Define the character of the pain','Medical and Nursing')

 ;with cte as
 (
  select
  ScheduleQuestionID,
  ans,
  QuestionText,
  department,
  count(ans) over (partition by ScheduleQuestionID) as r_count,
  FIRST_VALUE(ans) over (partition by ScheduleQuestionID order by ans) as f_value,
  FIRST_VALUE(ans) over (partition by ScheduleQuestionID order by ans desc) as l_value
  from Met_Question
 )
 select 
 distinct
 ScheduleQuestionID,
 case when r_count>1 then 
                          case when CHARINDEX('and',department)=0 then 
						       case when f_value='Not Met' and l_value='Not Met' then 'Not Met' else 'Met' end
			  when CHARINDEX('or',department)=0 then 
						       case when f_value='Met' and l_value='Met' then 'Met' else 'Not Met' end
			  else
							ans
			  end
		else ans 
end as ans,
 QuestionText,
 department
 from cte

Best Regards,

Will

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].