Records will 0 Balance are also shown

Question

Post reply

Records will 0 Balance are also shown

jsshivalik

SSChasing Mays

Points: 612
More actions
December 7, 2019 at 9:40 am
Go to Answer
#3705639

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

frederico_fonseca

SSCoach

Points: 16154

December 7, 2019 at 10:51 am

Answer

#3705651

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

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

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

jsshivalik SSChasing Mays Points: 612 More actions · Answer 1

Hi Frederico

Thnx a lot

Thanks

This reply was modified 4 years, 11 months ago by jsshivalik.

jsshivalik SSChasing Mays Points: 612 More actions · Answer 2

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, 11 months ago by jsshivalik.
This reply was modified 4 years, 11 months ago by jsshivalik.

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

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.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

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)