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
4 
Hi 

Hope this helps 


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