Balance is not calculating correctly,when date get change in transection.
-
Below is data in which two different date are coming,01/19/2021 and 01/18/2021 ,
Date 01/19/2021 transection amount is not getting minus (Credit ) From Balance ,but 01/18/2021 amount is getting Less from balance.
Secondly,i want ,Order by trans_date asc,
Create table #tbl_COA (Level_Four_ID int,Level_Four_Name varchar(50),Opening decimal(10,2))
Create table #tbl_trans_type (Trans_Type_ID int,trans_type_name varchar(50))
Create table #tbl_transection (Trans_ID int,Level_Four_ID_C int,Level_Four_ID_D int,Trans_Amount Decimal(10,2),Trans_date date,Trans_Type_ID int)
INSERT INTO #tbl_COA VALUES(1231,'Abdul Rauf',60000)
INSERT INTO #tbl_COA VALUES(1222,'Cheque In Hand',45000)
INSERT INTO #tbl_COA VALUES(1215,'MBL 833968',0)
insert into #tbl_trans_type VALUES(1,'Online')
insert into #tbl_trans_type VALUES(2,'Cheque')
insert into #tbl_trans_type VALUES(3,'Deposite')
insert into #tbl_trans_type VALUES(4,'Tranfer')
insert into #tbl_trans_type VALUES(5,'Return')
INSERT INTO #tbl_transection VALUES(1,1231,1222,55000,'2021-01-18',2)
INSERT INTO #tbl_transection VALUES(2,1231,1222,55000,'2021-01-18',2)
INSERT INTO #tbl_transection VALUES(3,1222,1215,44444,'2021-01-18',3)
INSERT INTO #tbl_transection VALUES(4,1215,1222,44444,'2021-01-18',5)
INSERT INTO #tbl_transection VALUES(5,1222,1231,44444,'2021-01-19',2)
;WITH cte
as(
SELECT NULL Trans_ID,NULL Trans_Type,NULL TransDate,'Opening' Trans_Remarks,NULL Code,NULL Head,null Debit,null Credit,isnull(opening,0) Balance
from #tbl_COA
where Level_Four_ID=1222
UNION ALL
SELECT T.Trans_ID,ty.trans_type_name as Trans_Type,Convert(varchar, T.Trans_Date ,101)as TransDate,
(CONCAT(COA.Level_Four_Name ,' ','Online Receipt C/O',' ',COAc.Level_Four_Name,' ', 'Rs. ',T.Trans_Amount)) as Trans_Remarks,
T.Level_Four_ID_C as Code ,COAc.Level_Four_Name as Head, T.Trans_Amount as Debit, CAST(0 AS decimal(18,2)) as Credit,T.Trans_Amount- CAST(0 AS decimal(18,2)) Balance
FROM #tbl_transection T
inner join #tbl_COA COA on COA.Level_Four_ID=T.Level_Four_ID_D
inner join #tbl_COA COAc on COAc.Level_Four_ID=T.Level_Four_ID_C
inner join #tbl_trans_type ty on ty.trans_type_ID=T.Trans_Type_ID
where COA.Level_Four_ID=1222
UNION ALL
SELECT T.Trans_ID,ty.trans_type_name as Trans_Type, Convert(varchar, T.Trans_Date ,101)as TransDate,
(CONCAT(COA.Level_Four_Name ,'Online Receipt C/O',' ',COA.Level_Four_Name, '',T.Trans_Amount)) as Trans_Remarks,
T.Level_Four_ID_D as Code,COA.Level_Four_Name as Head, CAST(0 AS decimal(18,2)) as Debit, Trans_Amount as Credit,CAST(0 AS decimal(18,2))-Trans_Amount Balance
FROM #tbl_transection T
inner join #tbl_COA COA on COA.Level_Four_ID=T.Level_Four_ID_D
inner join #tbl_trans_type ty on ty.trans_type_ID=T.Trans_Type_ID
where T.Level_Four_ID_c=1222
),cte1 as (
SELECT Trans_ID,Trans_Type,TransDate,Trans_Remarks, Code,Head,Debit,Credit,sum(Balance) over (order by (select null) ROWS UNBOUNDED PRECEDING) Balance
FROM cte)
select * from cte1
UNION ALL
SELECT NULL ,NULL ,NULL , null,null, 'Total',sum(Debit),sum(Credit),null
from cte1 order by Head,Trans_ID
- This topic was modified 3 years, 10 months ago by akhterhussain80. Reason: image upload
-
Deleted
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
This is sort of confusing because the ORDER BY of the outermost query includes the 'head' column as well as 'Trans_ID'. The SUM OVER ORDER BY is only by '(select null)'. At least on my test instance and from the picture it appears on the OP's as well the calculated sum is being performed in Trans_ID order by default. When I replace '(select null)' with 'Trans_ID asc' there's no change in the result. The row outlined is the total at the 5th summation. Why should it equal to the total at the 3rd summation?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
This is what I think you want:
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 = 1222
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 = 1222, '', 'Rs. '), t.Trans_Amount)
, Code = iif(t.Level_Four_ID_C = 1222, t.Level_Four_ID_D, t.Level_Four_ID_C)
, Head = iif(t.Level_Four_ID_C = 1222, coa.Level_Four_Name, coac.Level_Four_Name)
, Debit = iif(t.Level_Four_ID_D = 1222, t.Trans_Amount, 0.00)
, Credit = iif(t.Level_Four_ID_C = 1222, 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
)
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;Results:
Adding the opening Trans_ID = 0 and the 'Totals' Trans_ID to 9999 we can now order by just the transaction ID. I combined the 2 queries for debit and credit into a single query - which isn't required but seems to be cleaner.
By placing the value in either the Debit or Credit column for Opening - we can now easily SUM the debit - credit over the set to create the balance column. If the opening is less than zero - it would show up in the Credit column and the opening balance would be negative.
Hopefully this is at least close to what you are expecting.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
'In your query two issue i found.
First (If the opening is less than zero - it would show up in the Credit column and the opening balance would be negative.) Balance is not getting negative ,when Opening balance is in Credit side.
Second. When inserted row ,in which Head 1222 is not inserted(Debit and Credit Side),then that entry should not display in ledger of 1222.But in your query it is displaying with 0.
Create table #tbl_COA (Level_Four_ID int,Level_Four_Name varchar(50),Opening decimal(10,2))
Create table #tbl_trans_type (Trans_Type_ID int,trans_type_name varchar(50))
Create table #tbl_transection (Trans_ID int,Level_Four_ID_C int,Level_Four_ID_D int,Trans_Amount Decimal(10,2),Trans_date date,Trans_Type_ID int)
INSERT INTO #tbl_COA VALUES(1231,'Abdul Rauf',0)
INSERT INTO #tbl_COA VALUES(1222,'Cheque In Hand',-45000)
INSERT INTO #tbl_COA VALUES(1215,'MBL 833968',0)
insert into #tbl_trans_type VALUES(1,'Online')
insert into #tbl_trans_type VALUES(2,'Cheque')
insert into #tbl_trans_type VALUES(3,'Deposite')
insert into #tbl_trans_type VALUES(4,'Tranfer')
insert into #tbl_trans_type VALUES(5,'Return')
INSERT INTO #tbl_transection VALUES(1,1231,1222,55000,'2021-01-18',2)
INSERT INTO #tbl_transection VALUES(2,1231,1222,55000,'2021-01-18',2)
INSERT INTO #tbl_transection VALUES(3,1222,1215,44444,'2021-01-18',3)
INSERT INTO #tbl_transection VALUES(4,1215,1222,44444,'2021-01-18',5)
INSERT INTO #tbl_transection VALUES(5,1222,1231,44444,'2021-01-19',2)
INSERT INTO #tbl_transection VALUES(6,1231,1215,44444,'2021-01-19',5)
;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 = 1222
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 = 1222, '', 'Rs. '), t.Trans_Amount)
, Code = iif(t.Level_Four_ID_C = 1222, t.Level_Four_ID_D, t.Level_Four_ID_C)
, Head = iif(t.Level_Four_ID_C = 1222, coa.Level_Four_Name, coac.Level_Four_Name)
, Debit = iif(t.Level_Four_ID_D = 1222, t.Trans_Amount, 0.00)
, Credit = iif(t.Level_Four_ID_C = 1222, 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
)
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; -
Okay - these are simple issues to correct, assuming what you want is code for a specific ledger then we can do this:
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;The first issue was the 'Credit' amount should not be negative - that is fixed by making this small change:
, Credit = iif(coa.Opening < 0, -coa.Opening, 0.00)
This reverses the negative opening balance to a positive value in the Credit column. The Balance is then calculated as a negative value and all other calculations are based off that starting point.
Then - we just need to filter the transactions so we only include the specified ledger. I added the variable @ledgerID - and then use that variable. We can then filter out transactions that have the following:
Where @ledgerID In (t.Level_Four_ID_C, t.Level_Four_ID_D)
This gets us the following results with your changed data:
This does assume that your Trans_ID is in order of entry - if that is not the case then you would need to adjust the order by in the SUM and the order by in the final query. For example, you could order by TransDate, Trans_ID instead of just Trans_ID.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply