Query

Question

User-797751191 posted

Hi

  I have below code and i want if Balance is > 0 then only that record gets executed

Select a.Code,a.Name,(Select sum(Debit)-Sum(Credit) from Test1 where Code = a.Code) as Balance from Test0 a 

Thanks

Answer 1

User-939850651 posted

Hi jsshivalik,

Based on the query statement you provided, I created an example, I think this may be what you expect.

I made some modifications to the query statement.

Please see:

create table test1(
 int,
[Debit] int,
[Credit] int
)
create table test0(
 int,
[Name] varchar(20)
)
insert into test0 values (1,'first'),(2,'second'),(3,'third'),(4,'fourth'),(5,'fifth')

insert into test1 values (1,12,3),(1,51,4),(1,17,16),(2,31,55),(2,77,9),(2,54,87),
                         (3,21,3),(3,15,23),(3,22,19),(4,46,35),(4,56,18),(4,82,11),
			 (5,10,3),(5,51,44),(5,17,63)
When
 query:
Select a.Code,a.Name,(Select sum(Debit)-Sum(Credit) from Test1 where Code = a.Code) Balance  
			from Test0 a
Its result is:
What you want to query may be like this:
Select b.* from (
	Select a.Code newCode,a.Name newName,(Select sum(Debit)-Sum(Credit) from Test1 where Code = a.Code) Balance  
			from Test0 a) b
			where b.Balance > 0
Its
 result is:
It can be clearly seen that it only queries records with Balance greater than 0.
Hope this can help you.
Best regards,
Xudong Peng

Answer 2

User452040443 posted

Hi,

jsshivalik, you can also try:

Select 
    a.Code,
    a.Name,
    ca.Balance
from Test0 a 
cross apply
(
    Select sum(b.Debit) - Sum(b.Credit) as Balance 
    from Test1 b
    where b.Code = a.Code 
    having sum(b.Debit) - Sum(b.Credit) > 0
) ca

Hope this help