help with T-sql script

Question

Hello

I need help to write the T-SQL with scenario that if room got multiple room_cd and flagged with M and F then room_type will be flagged with majority of M or F like
in 106, with room 1 got 1 to 3 room_cd and flagged with both M and F , but there are 2 M's and 1 F then it should be flagged as MaleMix where as
In room 4 got 1 to 4 room _cd and flagged with 3 F's and 1 M then it should be flagged as FemaleMix,
In room 2 is individual and flagged as M then it should be flagged as Male.
In room 6, flag is null then flagged as 'Any'

`

create table #temp(id int, room int, room_cd varchar(1), flag varchar(1))

insert into #temp values(106,1,'1','M')
insert into #temp values(106,1,'2','M')
insert into #temp values(106,1,'3','F')

insert into #temp values(106,2,'1', 'M')

insert into #temp values(106,3,'1','f')

insert into #temp values(106,4,'1','F')
insert into #temp values(106,4,'2', 'f')
insert into #temp values(106,4,'3','f')
insert into #temp values(106,4,'4','m')

insert into #temp values(106,5,'1', 'M')
insert into #temp values(106,5,'1','M')
insert into #temp values(106,6,'1','')
select * from #temp

create table #temp_result(id int, room int, room_type varchar(15))

insert into #temp_result values(106,1,'MaleMix')
insert into #temp_result values(106,2, 'Male')
insert into #temp_result values(106,3,'FeMale')
insert into #temp_result values(106,4,'FeMaleMix')
insert into #temp_result values(106,5, 'Male')
insert into #temp_result values(106,6, 'Any')
select * from #temp_result`

Answer 1

Try an approach:

;
with Q as
(
    select id, room,
        count(case flag when 'M' then flag end) as mc,
        count(case flag when 'F' then flag end) as fc
    from #temp
    group by id, room
)
select id, room, 
    case 
        when mc = 0 and fc = 0 then 'Any'
        when fc = 0 then 'Male'
        when mc = 0 then 'Female'
        when mc > fc then 'Male Mix'
        when mc < fc then 'Female Mix'
        else 'Mix'
    end room_type
from Q
order by id, room

Answer 2

Hi @leo dec ,

Please also have a try with below:

;with cte as (  
select id, room,max(room_cd) max,  
sum(case when flag='M' then 1 else 0 end) as mc,  
sum(case when flag='F' then 1 else 0 end) as fc  
from (select distinct * from #temp) a  
group by id, room)  
select id, room,  
case when max=1 then   
case when mc=1 then 'Male' when fc=1 then  'FeMale' else 'Any' end   
when max>1 then   
case when mc>fc then 'MaleMix' when mc<fc then 'FeMaleMix' else 'Max' end   
end room_type  
from cte  

Output:

id	room	room_type  
106	1	MaleMix  
106	2	Male  
106	3	FeMale  
106	4	FeMaleMix  
106	5	Male  
106	6	Any  

Best regards,
Melissa

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.