locked
Records will 0 Balance are also shown RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    User-797751191 posted

    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

    Saturday, December 7, 2019 9:27 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    User452040443 posted

    Hi,

    Try:

    with CTE_Sum as
(
    SELECT T3.Account, SUM(T3.Debit - T3.Credit) Balance
    FROM OJDT T2
    INNER JOIN JDT1 T3 ON T2.TransId = T3.TransId
    WHERE T2.RefDate <= '2019/03/31' 
    GROUP BY T3.Account
)


SELECT 
    T1.Account, 
    C.Balance 
FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId
INNER JOIN CTE_Sum C ON C.Account = T1.Account
WHERE C.Balance <> 0
GROUP BY T1.Account, C.Balance
Having SUM(T1.Debit - T1.Credit) <> 0
ORDER BY T1.Account

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, December 7, 2019 1:11 PM