I have a requirement such as like below:
There are two tables table1 and table2 and I have to do Union all these two tables but while doing I need to check some columns if Sum(col1) from tbl1 is > Sum(col1) from tbl2 then consider the values of col1 from tbl1 and if it is Sum(col1) from tbl2 is > Sum(col1) from tbl1 then consider the values of col1 from tbl2 and finally then col1 should be returned from tbl1 or tbl2
I'm guessing when you write "col1" you mean "M_A", "M_B" and "M_C", and when you write "sum(col1) from tbl1 > sum(col1) from tbl2" you mean "sum(col1) from tbl1 >= sum(col1) from tbl2"?
If so, you might consider this:
select a.mp,a.ap,a.du,a.fa
,case when sum(a.m_a) over(order by (select null))>=b.m_a then a.m_a else 0 end as m_a
,case when sum(a.m_b) over(order by (select null))>=b.m_b then a.m_b else 0 end as m_b
,case when sum(a.m_c) over(order by (select null))>=b.m_c then a.m_c else 0 end as m_c
from tbl1 as a
cross apply (select sum(m_a) as m_a
,sum(m_b) as m_b
,sum(m_c) as m_c
from tbl2
) as b
union all
select a.mp,a.ap,a.du,a.fa
,case when sum(a.m_a) over(order by (select null))>b.m_a then a.m_a else 0 end as m_a
,case when sum(a.m_b) over(order by (select null))>b.m_b then a.m_b else 0 end as m_b
,case when sum(a.m_c) over(order by (select null))>b.m_c then a.m_c else 0 end as m_c
from tbl2 as a
cross apply (select sum(m_a) as m_a
,sum(m_b) as m_b
,sum(m_c) as m_c
from tbl1
) as b