Check My Query and need some suggestion

Transact-SQL
1

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

2 
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?

3

You should provide sample data and expected result for the problem that you mention. We don't have access to your table and data.

Anyway, on your query:

Firstly, don't really understand what you are trying to achieve here with the Running_Balance when you different ORDER BY clause in the expression

  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

Also why all the LEFT JOIN ? Are you expecting you have Debit_Account or Credit_Acount in All_Transitions that does not have a entry in ACCOUNTS table ?

The sub-query a is redundant. You can simply re-write it as one query.

Also note that, using simple table alias makes your query neater and easier to understand

SELECT t.Voucher_No, 
       t.Voucher_Type, 
       t.Narrations, 
       t.Users, 
       t.Date, 
       t.Date_2, 
       t.Year, 
       t.Month,
       dr.ACCOUNT_NAME AS Debit_Account_Name, 
       cr.ACCOUNT_NAME AS Credit_Account_Name, 
       t.Debit_Account, 
       t.Credit_Account, 
       t.Credit_Amount, 
       t.Debit_Amount, 
       SUM(t.Debit_Amount) OVER (PARTITION BY t.Debit_Account 
                                      ORDER BY t.Date_2, t.ID),
       - SUM(t.Credit_Amount) OVER (PARTITION BY t.Debit_Account 
                                      ORDER BY t.Date_2, t.ID)  
       AS Running_Balance,
       ROW_NUMBER() OVER (ORDER BY t.Date_2, t.ID) AS ID2
FROM   All_Transitions AS t
       INNER JOIN ACCOUNTS AS dr ON  t.Debit_Account  = dr.ACCOUNT_NO
       INNER JOIN ACCOUNTS AS cr ON  t.Credit_Account = cr.ACCOUNT_NO
ORDER BY t.Debit_Account, t.Date_2, t.ID