help with T-sql script

leo dec 41 Reputation points
2021-10-27T17:01:15.27+00:00

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`

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,653 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Viorel 111.8K Reputation points
    2021-10-27T17:26:00.983+00:00

    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
    2 people found this answer helpful.
    0 comments
  2. MelissaMa-MSFT 24,176 Reputation points
    2021-10-28T01:27:50.587+00:00

    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.

    0 comments