Create table #tbl_Account_L_Four (Level_Four_ID int,Level_Four_Name varchar(50),Opening_Value decimal(10,2),Opening_Date date)
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,sell_ID int)
INSERT INTO #tbl_Account_L_Four VALUES(1231,'Abdul Rauf',null,null)
INSERT INTO #tbl_Account_L_Four VALUES(1222,'Cheque In Hand',5000,'2021-01-18')
INSERT INTO #tbl_Account_L_Four VALUES(1215,'MBL 833968',null,null)
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,50000,'2021-01-18',2,null)
INSERT INTO #tbl_transection VALUES(2,1231,1222,50000,'2021-01-18',2,null)
INSERT INTO #tbl_transection VALUES(3,1222,1215,44444,'2021-01-18',3,null)
INSERT INTO #tbl_transection VALUES(4,1215,1222,44444,'2021-01-18',5,null)
INSERT INTO #tbl_transection VALUES(5,1222,1231,44444,'2021-01-19',2,null)
INSERT INTO #tbl_transection VALUES(6,1231,1222,500,'2021-01-20',2,null)
INSERT INTO #tbl_transection VALUES(7,1231,1222,500,'2021-01-22',2,null)
Declare @startDate date='2021-01-19'
Declare @EndDate date='2021-01-22'
Declare @Level_Four_ID int =1222
;With initaltransactions
As(
Select Trans_ID = 0,
Trans_Type = Null
, TransDate = Null,
Trans_Remarks = 'Opening'
, Code = Null, Head = Null
, Debit = iif(coa.Opening_value > 0, coa.Opening_value, 0.00)
, Credit = iif(coa.Opening_value < 0, -coa.Opening_value, 0.00)
From #tbl_Account_L_Four coa
Where coa.Level_Four_ID = @Level_Four_ID and coa.Opening_Date>@StartDate
Union All
Select t.Trans_ID, Trans_Type = ty.trans_type_name
, TransDate = convert(char(10), t.Trans_Date, 101)
, Trans_Remarks = --(CONCAT( T.Cheque_No ,' ', T.Cheque_Bank ,' ' ,'Cheque',' Date', ' ' ,T.Cheque_Date, ' ' ,'Branch',' ' ,T.Cheque_Branch , ' ','Rs.',CONVERT(varchar(50), CAST(T.Trans_Amount AS money),1),' ','Received From ' ,COAc.Level_Four_Name )) as Trans_Remarks
Case when (t.trans_type_ID=2 ) then concat('Rs.',CONVERT(Varchar(50), Cast(T.trans_Amount as money),1), coac.Level_Four_Name)
when
(t.Trans_Type_ID=7 and coac.Level_Four_ID=410101) then concat('Cash Received From',' ','Walking',',',' Invoice No' ,' ',T.Sell_ID ,',Amount=',T.Trans_Amount)
when
(t.Trans_Type_ID=7 and coac.Level_Four_ID!=410101) then concat('Cash Received From',' ',coac.Level_Four_Name,',',' Invoice No' ,' ',T.Sell_ID ,',Amount=',T.Trans_Amount)
when
(t.Trans_Type_ID=8 ) then concat('Sales To',' ',coa.Level_Four_Name,',',' Invoice No' ,' ',T.Sell_ID ,',Amount=',T.Trans_Amount)
When
(t.Trans_Type_ID=2) then concat(COA.Level_Four_Name ,'
From',' ',Coac.Level_Four_Name, '',T.Trans_Amount)end
, Code = iif(t.Level_Four_ID_C = @Level_Four_ID, t.Level_Four_ID_D, t.Level_Four_ID_C)
, Head = iif(t.Level_Four_ID_C = @Level_Four_ID, coa.Level_Four_Name, coac.Level_Four_Name)
, Debit = iif(t.Level_Four_ID_D = @Level_Four_ID, t.Trans_Amount, 0.00)
, Credit = iif(t.Level_Four_ID_C = @Level_Four_ID, 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_Account_L_Four coa On coa.Level_Four_ID = t.Level_Four_ID_D
Left Join #tbl_Account_L_Four coac On coac.Level_Four_ID = t.Level_Four_ID_C
Where @Level_Four_ID In (t.Level_Four_ID_C, t.Level_Four_ID_D) and t.Trans_Date <= @EndDate
)
, transactions as (
-- Get opening balance
Select Trans_ID = 0, Trans_Type = Null, TransDate = Null
, Trans_Remarks = 'Opening', Code = Null, Head = Null
, Debit = iif(sum(tn.Debit - tn.Credit)>0, sum(tn.Debit - tn.Credit), 0.00)
, Credit = iif(sum(tn.Debit - tn.Credit)<0, -sum(tn.Debit - tn.Credit), 0.00)
From initaltransactions tn
WHERE tn.TransDate < @startDate OR tn.Trans_ID = 0
UNION ALL
Select tn.Trans_ID, tn.Trans_Type, tn.TransDate
, tn.Trans_Remarks , tn.Code, tn.Head
, tn.Debit, tn.Credit
From initaltransactions tn
WHERE tn.TransDate BETWEEN @startDate AND @EndDate
)
,cte2
as(Select tn.Trans_ID, tn.Trans_Type, tn.TransDate
, tn.Trans_Remarks, tn.Code, tn.Head
, CASE WHEN Trans_ID = 0 THEN NULL ELSE tn.Debit END as Debit
, CASE WHEN Trans_ID = 0 THEN NULL ELSE tn.Credit END AS 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)
select * from cte2
order by case when Trans_ID =0 then '01/01/1900'
when Trans_ID =9999 then '12/31/9999'
else TransDate end,Trans_ID;
DROP TABLE #tbl_Account_L_Four;
DROP TABLE #tbl_trans_type;
DROP TABLE #tbl_transection;
