I want ,when Opening_value and Opening_Date exist in tabel #tbl_Account_L_Four ,then opening_Value will be get from #tbl_Account_L_Four and Opening_Value ,if Opening_Date is null then data will get from #tbl_transection table.
Secondly Opening_Date if exists,then opening_Vaule from #tbl_Account_L_Four and data from #tbl_transection table ,onward Opening_Date will be calculated.
as you can see below image ,in which opening_Value of against Level_Four_ID(1222) in a table #tbl_Account_L_Four exist,but current query is calculating '2021-01-18' data,,which should not
be calculated,
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(5,1231,1222,500,'2021-01-20',2,null) Declare @startDate date='2021-01-12' Declare @EndDate date='2021-01-20' 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;