T-SQL help

  • 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
  • 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.

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

     

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

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

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

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