Akhter HUssain

Akhter HUssain

  • 685
  • 1.3k
  • 95.2k

Need Ledger query modify with opening

Oct 3 2023 1:10 PM

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;


Answers (4)