As posted on SQL Server Central - here is one solution:
Declare @ledgerID int = 1222;
With transactions
As(
Select Trans_ID = 0
, Trans_Type = Null
, TransDate = Null
, Trans_Remarks = 'Opening'
, Code = Null
, Head = Null
, Debit = iif(coa.Opening > 0, coa.Opening, 0.00)
, Credit = iif(coa.Opening < 0, -coa.Opening, 0.00)
From #tbl_COA coa
Where coa.Level_Four_ID = @ledgerID
Union All
Select t.Trans_ID
, Trans_Type = ty.trans_type_name
, TransDate = convert(char(10), t.Trans_Date, 101)
, Trans_Remarks = concat(coa.Level_Four_Name, ' ', 'Online Receipt C/O', ' ', coac.Level_Four_Name, ' ', iif(t.Level_Four_ID_C = @ledgerID, '', 'Rs. '), t.Trans_Amount)
, Code = iif(t.Level_Four_ID_C = @ledgerID, t.Level_Four_ID_D, t.Level_Four_ID_C)
, Head = iif(t.Level_Four_ID_C = @ledgerID, coa.Level_Four_Name, coac.Level_Four_Name)
, Debit = iif(t.Level_Four_ID_D = @ledgerID, t.Trans_Amount, 0.00)
, Credit = iif(t.Level_Four_ID_C = @ledgerID, t.Trans_Amount, 0.00)
From #tbl_transection t
Inner Join #tbl_trans_type ty On ty.trans_type_ID = t.Trans_Type_ID
Left Join #tbl_COA coa On coa.Level_Four_ID = t.Level_Four_ID_D
Left Join #tbl_COA coac On coac.Level_Four_ID = t.Level_Four_ID_C
Where @ledgerID In (t.Level_Four_ID_C, t.Level_Four_ID_D)
)
Select tn.Trans_ID
, tn.Trans_Type
, tn.TransDate
, tn.Trans_Remarks
, tn.Code
, tn.Head
, tn.Debit
, tn.Credit
, Balance = sum(tn.Debit - tn.Credit) over(Order By tn.Trans_Id)
From transactions tn
Union All
Select Trans_ID = 9999
, Trans_Type = Null
, Trans_Date = Null
, Trans_Remarks = 'Total'
, Code = Null
, Head = Null
, Debit = sum(tn.Debit)
, Credit = sum(tn.Credit)
, Balance = sum(tn.Debit) - sum(tn.Credit)
From transactions tn
Order By
Trans_ID;
I would think ordering by the Trans_ID would be appropriate - since it doesn't seem possible to enter a transaction for a prior date at a later date. This appears to be a ledger and you cannot enter transactions for prior dates in a ledger, you enter a new transaction with a new date (and thus a new Trans_ID) for any adjustments.
If you really need to order by date - then you need to account for header/trailer rows and don't convert the dates to strings. One way would be to set a default opening date and a default closing date - for example: 1900-01-01 for opening and 9999-12-31 for closing. Also - instead of converting to date you want the actual date and time of the transaction.
You can always null out the 'default' dates for display.