How to remove null rows in when using union all in multiple tables

Question

How to remove null rows in when using union all in multiple tables

declare @FromDate date='2018-05-01';
    declare @ToDate date='2018-05-10';

    select ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS [SNO],t.MNO,MNAME ,isnull(sum(v1),0.00) as Balance,isnull(sum(v2),0.00) as CurrentPurchase,isnull(sum(v3),0.00) as Deduction
    from (select MNO, PendingDeduc as v1, NULL as v2, NULL as v3
          from tblProductPurchaseBalance where EntryDate between @FromDate and @ToDate union all
          select MNO, NULL as v1, TotalAmount, NULL as v3
          from tblMnoProductPurchase where PurchaseDate between @FromDate and @ToDate union all
          select MemNo as MNO, NULL as v1, NULL as v2, AAVIN
          from tblDeduction where EntryDate between @FromDate and @ToDate       
         ) t inner join TBLMEMBERS on t.MNO=TBLMEMBERS.MNO
    group by t.MNO,MNAME
    order by t.MNO

Sample Data And Result I need

Answer 1

You can't use t.Balance > 0 in your where clause, as Balance is just a alias name to your column in select.

Instead you can write HAVING isnull(sum(v1),0.00) > 0 after GROUP BY.

You final query should look like following.

SELECT ..., isnull(sum(v1),0.00) Balance
FROM
(
 --Your internal query here
) T
GROUP BY t.MNO,MNAME
HAVING isnull(sum(v1),0.00) > 0

Another approach is to again wrap you entire query inside a table and put the condition. Like following query.

 select * from
 ( 
 select ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS [SNO],t.MNO,MNAME ,isnull(sum(v1),0.00) as Balance,isnull(sum(v2),0.00) as CurrentPurchase,isnull(sum(v3),0.00) as Deduction
    from (select MNO, PendingDeduc as v1, NULL as v2, NULL as v3
          from tblProductPurchaseBalance where EntryDate between @FromDate and @ToDate union all
          select MNO, NULL as v1, TotalAmount, NULL as v3
          from tblMnoProductPurchase where PurchaseDate between @FromDate and @ToDate union all
          select MemNo as MNO, NULL as v1, NULL as v2, AAVIN
          from tblDeduction where EntryDate between @FromDate and @ToDate       
         ) t inner join TBLMEMBERS on t.MNO=TBLMEMBERS.MNO
    group by t.MNO,MNAME
   )t
   where T.Balance > 0 OR T.CurrentPurchase > 0 OR T.Deduction > 0
   order by t.MNO