locked
Query RRS feed

  • 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

    Wednesday, June 24, 2020 6:26 AM

All replies

  • 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

    Wednesday, June 24, 2020 9:07 AM
  • 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

    Wednesday, June 24, 2020 11:40 AM