In this blog, we learn how to calculate credit debit Transactions like banking report using SQL Sever.
Step 1: Create Table valued function for calculate credit, debit amount with total balance
USE [SqlBank]
CREATE FUNCTION [dbo].[FNGetTransaction](@CID BIGINT)
RETURNS @Tab_TRansaction TABLE (id BIGINT ,CreditAmt MONEY,DebitAmt MONEY,Tr_Type VARCHAR(250),
TranDate DATETIME ,AC_ID BIGINT ,Balance DECIMAL(18,2),TType VARCHAR(200)
, CustName varchar(max),AC_NO bigint,Address varchar(max),Mobile varchar(max),
Email varchar(max),AC_OpDate datetime,Remarks varchar(max) , IsmailSend int)
AS
BEGIN
DECLARE @TempAC_ID BIGINT;
SET @TempAC_ID = (SELECT TOP 1 A.AC_ID FROM Tbl_Account A join Tbl_Cust
C ON A.CID=C.CID WHERE c.CID=@CID)
DECLARE @Tbl_Tran Table
(id BIGINT,
CreditAmt MONEY,DebitAmt MONEY,Tr_Type VARCHAR(250),
TranDate DATETIME ,AC_ID BIGINT ,Balance DECIMAL(18,2),TType VARCHAR(200),
CustName varchar(max),AC_NO bigint,Address varchar(max),Mobile varchar(max),
Email varchar(max),AC_OpDate datetime ,
Remarks varchar(max) , IsmailSend int
)
INSERT INTO @Tbl_Tran(id,CreditAmt,DebitAmt,Tr_Type,TranDate,AC_ID,Balance,TType,CustName ,AC_NO ,Address
,Mobile,Email ,AC_OpDate,Remarks,IsmailSend)
SELECT TR.TR_ID, CASE WHEN tr.TR_CrDrType ='Cr' THEN tr.TR_Amt ELSE 0 END CreditAmt,
CASE WHEN tr.TR_CrDrType ='Dr' THEN tr.TR_Amt ELSE 0 END DebitAmt ,Tr.TR_Type,tr.TR_Date,Tr.AC_ID ,
CASE WHEN tr.TR_CrDrType ='Cr' THEN tr.TR_Amt ELSE 0 END - CASE WHEN tr.TR_CrDrType ='Dr'
THEN tr.TR_Amt ELSE 0 END Balance,
Tr.TR_CrDrType ,C.CName ,Acc.AC_NO ,C.CAddress ,C.CMObile,C.CEmail ,Acc.AC_OpDate ,
Tr.Remarks , Tr.IsmailSend
FROM Tbl_Transaction Tr with(nolock) join Tbl_Account Acc with(nolock) ON acc.AC_ID=Tr.AC_ID
join Tbl_Cust C with(nolock) ON C.CID=Acc.CID
WHERE Acc.CID=@CID;
WITH Tbl_CTE_Tran
as
(
SELECT T2.id,T2.CreditAmt,T2.DebitAmt,SUM(T1.CreditAmt-T1.DebitAmt) Balance,
T2.Tr_Type,T2.TranDate,T2.AC_ID
,T2.TType,T2.CustName ,T2.AC_NO ,T2.Address
,T2.Mobile,T2.Email ,T2.AC_OpDate,t2.Remarks,t2.IsmailSend FROM @Tbl_Tran T1
join @Tbl_Tran T2 on T1.id<=T2.id WHERE T2.AC_ID=@TempAC_ID
GROUP BY T2.id,T2.CreditAmt,T2.DebitAmt,T2.Tr_Type,T2.TranDate,T2.AC_ID,T2.TType,
T2.CustName ,T2.AC_NO ,T2.Address
,T2.Mobile,T2.Email ,T2.AC_OpDate ,t2.Remarks ,t2.IsmailSend
)
INSERT INTO @Tab_TRansaction (id,CreditAmt,DebitAmt,Tr_Type,TranDate,AC_ID,Balance,TType,CustName ,AC_NO ,Address
,Mobile,Email ,AC_OpDate ,Remarks ,IsmailSend
)
SELECT id,CreditAmt,DebitAmt,Tr_Type,TranDate,AC_ID,Balance,TType ,CustName ,AC_NO ,Address
,Mobile,Email ,AC_OpDate ,Remarks,IsmailSend
FROM Tbl_CTE_Tran with(nolock)
WHERE AC_ID=@TempAC_ID
RETURN
END
Step 2: Create Procedure & Call above function in Procedure
USE [SqlBank]
CREATE PROC [dbo].[PROC_TRansaction]
(
@TR_ID int=null output,
@CID bigint=null,
@TR_Amt decimal(18,2)=null,
@AC_ID bigint =null,
@Flag varchar(100)=null,
@AC_No bigint=null,
@Remarks varchar(max)=null,
@MTR_ID int=null output,
@Balance decimal(18,2)=null output
)
AS
BEGIN
DECLARE @TempTRAmount decimal(18,2)
DECLARE @Temp_ACID bigint
DECLARE @Tran_ScopID bigint;
DECLARE @Tran_ID bigint;
DECLARE @MMTR_ID bigint;
BEGIN TRAN Tbl_Transaction_Tran
BEGIN TRY
IF(@Flag = 'Tran')
BEGIN
IF EXISTS(SELECT 1 FROM Tbl_Transaction Tr with(nolock) join Tbl_Account Acc
with(nolock) ON acc.AC_ID=Tr.AC_ID WHERE Acc.CID=@CID)
BEGIN
SELECT a.id id ,a.DebitAmt,a.CreditAmt,a.Balance
,a.Tr_Type, isnull(Format(a.TranDate,'dd-MMM-yyyy HH:mm'),'') TranDate, NCHAR(8377) Rupees ,a.TType,a.Remarks
FROM dbo.FNGetTransaction(@CID) a
-- JOIN dbo.FNGetTransaction(@CID) b ON b.id<=a.id
--GROUP BY a.id,a.DebitAmt,a.CreditAmt,a.Tr_Type,a.TranDate,a.AC_ID,a.TType
END
ELSE
BEGIN
Select 'No Transaction summary found...?' OpMsg
END
END
ELSE IF(@Flag = 'IN')
BEGIN
SET @Temp_ACID = (SELECT Top 1 A.AC_ID FROM Tbl_Account A with(nolock)
Join Tbl_Cust C with(nolock) ON A.CID=C.CID WHERE A.AC_No=@AC_No)
DECLARE @SenderName varchar(max)
SET @SenderName = (SELECT Top 1 c.CName FROM Tbl_Account A with(nolock)
Join Tbl_Cust C with(nolock) ON A.CID=C.CID WHERE c.CID=@CID)
DECLARE @ReciverName varchar(max)
SET @ReciverName = (SELECT Top 1 c.CName FROM Tbl_Account A with(nolock)
Join Tbl_Cust C with(nolock) ON A.CID=C.CID
WHERE A.AC_No=@AC_No)
SET @TempTRAmount = (
SELECT TOP 1 ISNULL(SUM(b.balance),0) Balance
FROM dbo.FNGetTransaction(@CID) a
JOIN dbo.FNGetTransaction(@CID) b ON b.id<=a.id
GROUP BY a.id,a.DebitAmt,a.CreditAmt,a.Tr_Type,a.TranDate,a.AC_ID,a.TType ORDER BY a.id desc)
if(@TR_Amt > @TempTRAmount)
BEGIN
Select 'Insuffitient Balance' as msg
END
ELSE
BEGIN
Declare @FixScratchAmt decimal(18,2)=500;
--if not exists (select 1 from Tbl_Transaction Where TR_Date=CURRENT_TIMESTAMP and Ref_TranACC=@AC_ID)
--begin
Insert INTO Tbl_Transaction (TR_Type,TR_Amt,TR_Date,AC_ID,TR_CrDrType,Ref_TranACC,isdelete,IsTranType,IsMailSend,Remarks)
Values ('Online - Transfer To - '+ @ReciverName + ' '+Cast(@Ac_NO as varchar(max))+' ',
ISNULL(@TR_Amt,0),CURRENT_TIMESTAMP,@AC_ID,'Dr','Tran-' +CAST(@AC_ID as varchar(max)),0,'S',0,@Remarks)
set @Tran_ID = @@IDENTITY;
set @TR_ID= @Tran_ID;
set @Tran_ScopID= SCOPE_IDENTITY();
Set @Balance = (SELECT TOP 1 BALANCE FROM dbo.FNGetTransaction(@CID) order by id desc)
if(@TR_Amt >= @FixScratchAmt)
begin
Insert INTO Tbl_Transaction (TR_Type,TR_Amt,TR_Date,AC_ID,TR_CrDrType,Ref_TranACC,isdelete,IsTranType,IsMailSend,Remarks)
Values ('Cash Back From S Bank7 ',10,CURRENT_TIMESTAMP,@AC_ID,'Cr',0,1,'R',0,'Cash back from Sbank7. Pay & win more cash back ')
END
Insert INTO Tbl_Transaction (TR_Type,TR_Amt,TR_Date,AC_ID,TR_CrDrType,Ref_TranACC,isdelete,IsTranType,IsMailSend,Remarks)
Values ('Recived From ' + @SenderName + ' Tran - '+Cast(@Tran_ScopID as varchar(max))+'-'+
CAST(@AC_ID as varchar(max)),ISNULL(@TR_Amt,0),CURRENT_TIMESTAMP,@Temp_ACID,'Cr','Tran-'
+Cast(@Tran_ScopID as varchar(max))+'-'+ CAST(@AC_ID as varchar(max)),0,'R',0,@Remarks)
set @MMTR_ID = @@IDENTITY;
set @MTR_ID = @MMTR_ID;
END
END
IF(@@TRANCOUNT > 0)
BEGIN
COmmit tran Tbl_Transaction_Tran
END
END TRY
BEGIN CATCH
IF(@@TRANCOUNT > 0)
BEGIN
ROLLBACK TRAN Tbl_Transaction_Tran
END
DECLARE @USERID varchar(max),@ERRORLINE varchar(max)
,@ERRORMESSAGE varchar(max),@ERRORPROCEDURE varchar(500),@ERRORSEVERITY varchar(max)
,@ERRORSTATE varchar(max), @ErroFrm varchar(max)
SELECT @USERID = SUSER_SNAME(),@ERRORLINE=ERROR_LINE(),@ERRORMESSAGE=ERROR_MESSAGE(),
@ERRORPROCEDURE=ERROR_PROCEDURE(),@ERRORSEVERITY=ERROR_SEVERITY(),
@ERRORSTATE= ERROR_STATE() ,@ErroFrm = 'Backend'
EXEC Proc_ERRORLOG @USERID,@ERRORLINE,@ERRORMESSAGE,@ERRORPROCEDURE,@ERRORSEVERITY,@ERRORSTATE,0,@ErroFrm
END CATCH
END
Step 3: Execute Procedure to Check Report
exec [dbo].[PROC_TRansaction]
@CID =2,@Flag='Tran'
I hope it works!
HostForLIFEASP.NET SQL Server 2019 Hosting
