help with case statement

Question

Post reply

help with case statement

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

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

Michael L John One Orange Chip Points: 27079 More actions · Answer 1

What value indicates that room_cd is single? Or multiple?

Can you show what you have tried so far?

Isn't this the same question in this post?

https://www.sqlservercentral.com/forums/topic/t-sql-help-50

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Steve Collins SSCrazy Points: 2238 More actions · Answer 2

Maybe a good way could be an UPDATE statement

update t
set flag = case when v.row_count=1 then 'ANY_EMPTY'
                when v.row_count>1 and (v.m_count>=v.f_count) then 'M_EMPTY'
                else 'F_EMPTY' end            
from #temp t
     cross apply (select count(*), 
                         sum(case when flag='M' then 1 else 0 end),
                         sum(case when flag='F' then 1 else 0 end) 
                  from #temp tt
                  where tt.id=t.id
                        and tt.room=t.room) v(row_count, m_count, f_count)
where t.flag = 'NULL';

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

leo_dec Old Hand Points: 343 More actions · Answer 3

Hi Michael

thanks for looking at query.. yes you are right the other post is also mine but client changed the requirement. now they want to see if it is null then show which flag is empty..

room_cd is multiple of single is because of multiple room id for each room.

hope that will help.

regards

Michael L John One Orange Chip Points: 27079 More actions · Answer 4

leo_dec wrote:

Hi Michael
thanks for looking at query.. yes you are right the other post is also mine but client changed the requirement. now they want to see if it is null then show which flag is empty..
room_cd is multiple of single is because of multiple room id for each room.
hope that will help.
regards

What code have you tried?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/