locked
selecting rows depending on column value RRS feed

  • 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

    Monday, October 16, 2017 5:47 PM

All replies

  • 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, esquel@sommarskog.se

    Monday, October 16, 2017 10:01 PM
  • 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


    • Edited by RichardLees Monday, October 16, 2017 11:22 PM Distinct
    Monday, October 16, 2017 11:22 PM
  • 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 MSDNFSF@microsoft.com.

    Tuesday, October 17, 2017 4:52 AM