T-SQL help

Question

T-SQL help

Old Hand

Points: 343

October 27, 2021 at 5:03 pm

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')

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,'FeMale')
insert into #temp_result values(106,5, 'Male')

select * from #temp_result

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

To me this sounds pretty easy to do and I expect there are multiple solutions, but my approach would be to break it up into multiple steps for the sake of readability. I would have a CTE which would do a count in the table for the number of M's and the number of F's per room. Then in the final select you'd use a CASE statement for when M is 0 then female, when F is 0 then male, when M>F then male mix else female mix.

That being said, shouldn't room 4 in the final result be "FemaleMix" not "Female" as room 4, room_cd 4 is m?

Does the above make sense?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

ratbak Ten Centuries Points: 1358 More actions · Answer 2

ratbak

Ten Centuries

Points: 1358

October 27, 2021 at 6:12 pm

#3945194

What should room_type be if it's a tie (50/50)?

ScottPletcher SSC Guru Points: 101093 More actions · Answer 3

You didn't say what the result should be if it is tied, for example, 2F and 2M? In that case, I went with 'FemaleMix', adjust that if you need to.

SELECT
    id, room, 
    CASE WHEN SUM(CASE WHEN flag IN ('F', 'f') THEN 1 ELSE 0 END) > 0
         THEN CASE WHEN SUM(CASE WHEN flag IN ('M', 'm') THEN 1 ELSE 0 END) > 0
                   THEN CASE WHEN SUM(CASE WHEN flag IN ('M', 'm') THEN 1 ELSE 0 END) > 
                                  SUM(CASE WHEN flag IN ('F', 'F') THEN 1 ELSE 0 END)
                             THEN 'MaleMix' ELSE 'FemaleMix' END
              ELSE 'Female' END
         ELSE CASE WHEN SUM(CASE WHEN flag IN ('M', 'm') THEN 1 ELSE 0 END) > 0 
                   THEN 'Male' ELSE 'Any' END
         END
FROM #temp
GROUP BY id, room

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

ScottPletcher wrote:

You didn't say what the result should be if it is tied, for example, 2F and 2M? In that case, I went with 'FemaleMix', adjust that if you need to.

SELECT
    id, room, 
    CASE WHEN SUM(CASE WHEN flag IN ('F', 'f') THEN 1 ELSE 0 END) > 0
         THEN CASE WHEN SUM(CASE WHEN flag IN ('M', 'm') THEN 1 ELSE 0 END) > 0
                   THEN CASE WHEN SUM(CASE WHEN flag IN ('M', 'm') THEN 1 ELSE 0 END) > 
                                  SUM(CASE WHEN flag IN ('F', 'F') THEN 1 ELSE 0 END)
                             THEN 'MaleMix' ELSE 'FemaleMix' END
              ELSE 'Female' END
         ELSE CASE WHEN SUM(CASE WHEN flag IN ('M', 'm') THEN 1 ELSE 0 END) > 0 
                   THEN 'Male' ELSE 'Any' END
         END
FROM #temp
GROUP BY id, room

It should be labeled as "Bob & Ted & Carol and Alice". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)