December 14, 2021 at 5:17 pm
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
December 14, 2021 at 6:05 pm
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/
December 14, 2021 at 6:44 pm
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
December 14, 2021 at 8:01 pm
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
December 14, 2021 at 8:06 pm
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/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply