SQLTeam.com | Weblogs | Forums

Help required in Building Sql Select statement with CASE clause


#1

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


#2

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