Case statement sql help

Transact-SQL
1

Hi

I need help with the SQL query.. requirement is that wherever flag is null and room_cd is single for room then it should be labelled as Any_empty and if room_cd is multiple for room then it should be labelled as M_empty or F_empty depends on the majority of flag in room. like in room 4, room_cd are multiple and out of 3 filled cd 2 are f and 1 is m so it should be flagged as F_Empty.

If flag m and f are equal in room then make it m_empty.

  create table #temp(id int, room int, room_cd varchar(1), flag varchar(10))   
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,1,'4','M')
    insert into #temp values(106,1,'5','M')
    insert into #temp values(106,1,'6','F')
    insert into #temp values(106,2,'1', 'M')
    insert into #temp values(106,3,'1','NULL')
    insert into #temp values(106,4,'1','F')
    insert into #temp values(106,4,'2', 'NULL')
    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_cd varchar(1), flag varchar(10))

    insert into #TEMP_RESULT values(106,1,'1','M')
    insert into #TEMP_RESULT values(106,1,'2','M')
    insert into #TEMP_RESULT values(106,1,'3','F')
    insert into #TEMP_RESULT values(106,1,'4','M')
    insert into #TEMP_RESULT values(106,1,'5','M')
    insert into #TEMP_RESULT values(106,1,'6','F')
    insert into #TEMP_RESULT values(106,2,'1', 'M')
    insert into #TEMP_RESULT values(106,3,'1','ANY_EMPTY')
    insert into #TEMP_RESULT values(106,4,'1','F')
    insert into #TEMP_RESULT values(106,4,'2', 'F_EMPTY')
    insert into #TEMP_RESULT values(106,4,'3','F')
    insert into #TEMP_RESULT values(106,4,'4','M')
    insert into #TEMP_RESULT values(106,5,'1', 'M')
    insert into #TEMP_RESULT values(106,5,'1','M')

    select * from #TEMP_RESULT
2 
;WITH cte_check_rooms AS (
    SELECT room, 
        SUM(CASE WHEN room_cd <> '1' THEN 1 ELSE 0 END) AS room_cd_not_1_count,
        SUM(CASE WHEN flag = 'M' THEN 1 ELSE 0 END) AS male_count,
        SUM(CASE WHEN flag = 'F' THEN 1 ELSE 0 END) AS female_count
    FROM #temp
    GROUP BY room
    HAVING SUM(CASE WHEN flag IS NULL THEN 1 ELSE 0 END) >= 1
)
UPDATE t
SET flag = CASE WHEN room_cd_not_1_count = 0 THEN 'ANY_EMPTY'
                WHEN ccr.male_count = ccr.female_count THEN 'ANY_EMPTY' 
                WHEN ccr.female_count > ccr.male_count THEN 'F_EMPTY'
                ELSE 'M_EMPTY' END
FROM #temp t
INNER JOIN cte_check_rooms ccr ON ccr.room = t.room AND t.flag IS NULL
1 Like