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!

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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)
    Intro to Tally Tables and Functions

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

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