I placed the query below please check my query and I need some suggestion the query is working some Condition fine, but the problem is when I create bulk voucher at the time its show the Wrong running total i didn't do one by one voucher transaction
SELECT All_Transitions.Voucher_No, All_Transitions.Voucher_Type, All_Transitions.Narrations, All_Transitions.Users, All_Transitions.Date, All_Transitions.Date_2, All_Transitions.Year, All_Transitions.Month,
DebitAccount.ACCOUNT_NAME AS Debit_Account_Name, CreditAccount.ACCOUNT_NAME AS Credit_Account_Name, a.Debit_Account, a.Credit_Account, a.Credit_Amount, a.Debit_Amount, a.Running_Balance, ROW_NUMBER()
OVER (ORDER BY All_Transitions.Date_2, All_Transitions.ID) AS ID2
FROM (SELECT ID, Debit_Account, Credit_Account, Debit_Amount, Credit_Amount, COALESCE (SUM(Debit_Amount) OVER (PARTITION BY Debit_Account
ORDER BY Date_2), 0) - COALESCE (SUM(Credit_Amount) OVER (PARTITION BY Debit_Account
ORDER BY ID), 0) AS Running_Balance
FROM All_Transitions) a LEFT JOIN
ACCOUNTS AS DebitAccount ON a.Debit_Account = DebitAccount.ACCOUNT_NO LEFT JOIN
ACCOUNTS AS CreditAccount ON a.Credit_Account = CreditAccount.ACCOUNT_NO LEFT JOIN
All_Transitions ON a.ID = All_Transitions.ID
SELECT
All_Transitions.Voucher_No,
All_Transitions.Voucher_Type,
All_Transitions.Narrations,
All_Transitions.Users,
All_Transitions.Date,
All_Transitions.Date_2,
All_Transitions.Year,
All_Transitions.Month,
DebitAccount.ACCOUNT_NAME AS Debit_Account_Name,
CreditAccount.ACCOUNT_NAME AS Credit_Account_Name,
a.Debit_Account,
a.Credit_Account,
a.Credit_Amount,
a.Debit_Amount,
a.Running_Balance,
ROW_NUMBER() OVER (ORDER BY All_Transitions.Date_2, All_Transitions.ID) AS ID2
FROM (
SELECT
ID,
Debit_Account,
Credit_Account,
Debit_Amount,
Credit_Amount,
COALESCE (SUM(Debit_Amount) OVER (PARTITION BY Debit_Account ORDER BY Date_2), 0),
COALESCE (SUM(Credit_Amount) OVER (PARTITION BY Debit_Account ORDER BY ID), 0) AS Running_Balance
FROM All_Transitions
) a
LEFT JOIN ACCOUNTS AS DebitAccount
ON a.Debit_Account = DebitAccount.ACCOUNT_NO
LEFT JOIN ACCOUNTS AS CreditAccount ON a.Credit_Account = CreditAccount.ACCOUNT_NO
LEFT JOIN All_Transitions ON a.ID = All_Transitions.ID
I don't know what is wrong with your query. I don't have your data structure with your data. Maybe you can provide a working example with data an the result you are expecting.
When I formatted your data I noticed COALESCE (SUM(Credit_Amount) OVER (PARTITION BY Debit_Account ORDER BY ID), 0) is different then I expected but maybe it's correct. Why is the Credit_amount not PARTITIONED by Credit_account and ordered by Date_2 as Debit_amount?