question

leodec-4354 avatar image
0 Votes"
leodec-4354 asked MelissaMa-msft commented

help with T-sql script

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-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @leodec-4354,

Could you please validate the answers and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
Viorel-1 avatar image
2 Votes"
Viorel-1 answered Viorel-1 edited

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @leodec-4354,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.