Records will 0 Balance are also shown

  • Hi

    In below query it is showing records will Balance 0 which i don't want to show

    SELECT T1.Account, Isnull((SELECT SUM(T3.Debit - T3.Credit) FROM OJDT T2

    INNER JOIN JDT1 T3 ON T2.TransId = T3.TransId

    WHERE T3.Account = T1.Account And T2.RefDate <= '2019/03/31' GROUP BY T3.Account),0) 'Balance'

    FROM OJDT T0

    INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

    GROUP BY T1.Account

    Having SUM(T1.Debit - T1.Credit) != 0

    ORDER BY T1.Account

    Thanks

  • select *
    from (
    select t1.Account
    , isnull(
    (select sum(t3.Debit - t3.Credit)
    from OJDT t2

    inner join JDT1 t3
    on t2.TransId = t3.TransId

    where t3.Account = t1.Account
    and t2.RefDate <= '2019/03/31'
    group by t3.Account
    ), 0) 'Balance'

    from OJDT t0
    inner join JDT1 t1
    on t0.TransId = t1.TransId
    group by t1.Account
    having sum(t1.Debit - t1.Credit) != 0 -- this applies to current balance
    ) t
    where Balance <> 0 -- this applies to balance up to the date on the subquery
    order by t1.Account

    you kind of need to see exactly which balance = zero you don't wish to show

     

  • Hi Frederico

    Thnx a lot

    Thanks

    • This reply was modified 4 years, 3 months ago by  jsshivalik.
  • Hi Frederico

    I am getting this error - Column 'OACT.FatherNum' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    select *
    from (
    select T1.Account as Code,Max(T0.AcctName) as Name,(Select AcctName from Oact T where T.AcctCode = T0.FatherNum) as Heading
    , isnull(
    (select sum(t3.Debit - t3.Credit)
    from OJDT t2
    inner join JDT1 t3
    on t2.TransId = t3.TransId
    WHERE T3.Account = T1.Account And T2.RefDate <= '2019/03/31'
    group by t3.Account ), 0) 'Balance'
    FROM OACT T0
    INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account
    GROUP BY T1.Account
    Having SUM(T1.Debit - T1.Credit) != 0
    ) t
    where Balance <> 0

    Thanks

    • This reply was modified 4 years, 3 months ago by  jsshivalik.
    • This reply was modified 4 years, 3 months ago by  jsshivalik.
  • Apologies I should have remove the t1. from the order by when I added the outer select to filter on balance.

     

    Now you have added a further block inside the query. That needs to also be subject to a max/min function and that is why its failing now.

     

  • Be REALLY careful with the code posted so far (all of it... the original post and all coded answers).  It contains a Triangular Join and that's a huge drain on resources and very quickly runs into extreme performance issues.  Please see the following article on why you shouldn't use Triangular Joins to do running totals.

    https://www.sqlservercentral.com/articles/hidden-rbar-triangular-joins

    I'll try to get back to this with a bit of a different take on it.  You could help me help you by reading'n'heeding the first link in my signature line below.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply