Hi @Analyst_SQL ,
JeffreyWilliams-3310's proposal is good, but it is not feasible to replace the final code with the code he provided. Please refer to the code below:
Declare @startDate date='2021-01-19'
Declare @EndDate date='2021-01-21'
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 > 0, coa.Opening, 0.00)
, Credit = iif(coa.Opening < 0, -coa.Opening, 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;
Output:
Regards
Echo
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.