Need help to modify query

akhter · November 15, 2023, 9:32am

Below is query

Create table #tbl_Account_L_Five (Level_Five_ID int,Level_Five_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_Five (Trans_ID int,Level_Five_ID_D int,Level_Five_ID_C int,Level_Five_ID_CA int,Trans_Amount Decimal(10,2),Trans_date date,Trans_Type_ID int)
INSERT INTO #tbl_Account_L_Five VALUES(123100001,'Abdul Rauf',0,'2023-10-01')
INSERT INTO #tbl_Account_L_Five VALUES(124200001,'Cheque In Hand',0,'2023-10-01')
INSERT INTO #tbl_Account_L_Five VALUES(121100006,'MBL 833968',0,'2023-10-01')
INSERT INTO #tbl_Account_L_Five VALUES(124200002,'Duties',0,'2023-10-01')
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_Five VALUES(1,null,121100006,NULL,750,'2023-10-04',2)
INSERT INTO #tbl_Transection_Five VALUES(1,123100001,null,121100006,250,'2023-10-04',2)
INSERT INTO #tbl_Transection_Five VALUES(1,124200001,null,121100006,250,'2023-10-04',2) 
INSERT INTO #tbl_Transection_Five VALUES(1,124200002,null,121100006,250,'2023-10-04',2) 

Declare @Level_Five_ID int=121100006
Declare @StartDate date ='2023-10-01'
Declare @EndDate date='2023-11-19'

; WITH CTE_H as(
select  trans_ID,TransDate, Concat( Level_Five_Name, CHAR(13) + CHAR(10)) as  Head
from #tbl_Account_L_Five c
inner join
(
    select  trans_ID,Trans_Date TransDate, Level_Five_ID_CA
    from #tbl_Transection_Five where Level_Five_ID_D = @Level_Five_ID
    union
    select trans_ID,Trans_Date, Level_Five_ID_D
    from #tbl_Transection_Five where Level_Five_ID_CA = @Level_Five_ID
) t on t.Level_Five_ID_CA = c.Level_Five_ID
)

, Unio AS (
  SELECT NULL AS Trans_ID, Opening_Date AS Trans_Date, hEAD=nuLL,
         IIF(Opening_value > 0, Opening_value, 0) AS Debit,
         IIF(Opening_value < 0, Opening_value, 0) AS Credit,
         Opening_value AS Amount,'Opening' AS Trans_Remarks
  FROM   #tbl_Account_L_Five
  WHERE  Level_Five_ID = @Level_Five_ID
  UNION ALL
  SELECT t.Trans_ID,Trans_Date , 
     Head = H.Head,

   
   IIF(Level_Five_ID_D = @Level_Five_ID, Trans_Amount, 0),
                               IIF(Level_Five_ID_C = @Level_Five_ID, Trans_Amount, 0),
         CASE WHEN Level_Five_ID_D = @Level_Five_ID THEN Trans_Amount 
              WHEN Level_Five_ID_C = @Level_Five_ID THEN -1 * Trans_Amount 
         END,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_Five_Name)
          when
          (t.Trans_Type_ID=7 and coac.Level_Five_ID=410101) then concat('Cash Received From',' ','Walking',',',' Invoice No' ,' ',',Amount=',T.Trans_Amount) 
          when
          (t.Trans_Type_ID=7 and coac.Level_Five_ID!=410101) then concat('Cash Received From',' ',coac.Level_Five_Name,',',' Invoice No' ,' ',',Amount=',T.Trans_Amount) 
          when
          (t.Trans_Type_ID=8 ) then concat('Cash Payment To',' ',coaD.Level_Five_Name,',' ,' ',',Amount=',T.Trans_Amount) 
		    when
          (t.Trans_Type_ID=1 ) then concat('Bank Payment To',' ',coaD.Level_Five_Name,',' ,' ',',Amount=',T.Trans_Amount) 
          When 
          (t.Trans_Type_ID=2) then concat(COAD.Level_Five_Name ,'
           From',' ',Coac.Level_Five_Name, '',T.Trans_Amount) End
		
  FROM   #tbl_transection_five t
         
	  Left Join #tbl_Account_L_Five  coaD On coaD.Level_Five_ID = t.Level_Five_ID_D
          Left Join #tbl_Account_L_Five  coaC On coaC.Level_Five_ID = t.Level_Five_ID_C

        

          left Join #tbl_trans_type ty On ty.trans_type_ID = t.Trans_Type_ID
		  left outer join CTE_H H on H.Trans_ID=t.Trans_ID
  WHERE  Trans_Date > (SELECT Opening_Date FROM tbl_Account_L_Five WHERE Level_Five_ID = @Level_Five_ID)  and (Level_Five_ID_D=@Level_Five_ID or Level_Five_ID_C=@Level_Five_ID)
), 
runsum AS (
   SELECT Trans_ID, Trans_Date TransDate ,hEAD, Debit, Credit,
          SUM(Amount) OVER(ORDER BY Trans_Date ,Trans_ID
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
						   ) AS Balance, Trans_Remarks
   FROM  Unio
), 
prevbal AS (

   select   Top(1)BALANCE prevbal from runsum
	where TransDate<@startDate
	Order by TransDate desc,Trans_ID desc

)


,CTE_F as(
SELECT NULL AS Trans_ID, NULL AS TransDate, 'Opening' Trans_Remarks, 
       null aS HEAD,NULL AS Debit, NULL AS Credit, prevbal AS Balance
FROM   prevbal


UNION all
SELECT Trans_ID, TransDate,  Trans_Remarks,HEAD, Debit, Credit, Balance
FROM   runsum
WHERE  TransDate BETWEEN @startDate AND @EndDate
)

select Trans_ID, TransDate,  Trans_Remarks,HEAD, Debit, Credit, Balance  from CTE_F

Drop table #tbl_Account_L_Five
Drop table #tbl_trans_type
Drop table #tbl_Transection_Five

Which is giving me below output ,which is not correct

Screenshot 2023-11-15 142046

I want below output

Trans_ID	TransDate	Trans_Remarks	HEAD	Credit	Balance
NULL	10/1/2023	Opening	NULL	0	0
1	10/4/2023	Abdul Rauf Rs.250.00 Paid From MBL 833968	Abdul Rauf	0
1	10/4/2023	Cheque In Hand Rs.250.00 paid From MBL 833968	Cheque In Hand	0
1	10/4/2023	Duties Rs.250. paid from 00MBL 833968	Duties	750	-750

mike01 · November 15, 2023, 3:22pm

is there any logic as to why? What are the rules?

akhter · November 15, 2023, 5:14pm

@mike01

Rules :
when Debit_Head_ID value exists and Credit_Head_ID is null ,then in Countra_Head Credit value will be display.
Second rule is when Credit_Head_ID value exists and Debit_head_ID is null then Counter_Head Debit value will be display

khtan · November 16, 2023, 2:03am

This looks the same as your previous question https://forums.sqlteam.com/t/require-expected-output/23626/6

akhter · November 16, 2023, 2:55am

yes,but added one column of head ,in which i got stuck

khtan · November 16, 2023, 6:30am

What is the following ? which column are you referring to

Debit_Head_ID
Credit_Head_ID
Countra_Head Credit
Counter_Head Debit

When asking question, you need to understand that we don't have access to your database nor understand your business logic or application, you need to explain clearly so that we can understand what you want and help you.

akhter · November 16, 2023, 7:35am

Below is my transection table

Create table #tbl_Transection_Five (Trans_ID int,Level_Five_ID_D int,Level_Five_ID_C int,Level_Five_ID_CA int,Trans_Amount Decimal(10,2),Trans_date date,Trans_Type_ID int)

Debit_Head_ID =when Debit_Head_ID is not null ,then Trans_Amount will be Debit,
Credit_Head_ID=when Credit_Head_ID is not null,then Trans_Amount will be Credit.

Level_Five_ID_CA = it is countra Head ID column(Head), means that in Head column ,when Debit_head_ID is not null in Debit,then Credit_Head_ID will be display in Countra Head Column or when Credit_Head_ID is not null in Credit column ,then Debit_Head_ID will be display in Countra Head Column.
As you can see below row

INSERT INTO #tbl_Transection_Five VALUES(1    ,null,  121100006,        NULL, 250,'2023-10-04',2)
INSERT INTO #tbl_Transection_Five VALUES(1,123100001,       null,   121100006,250,'2023-10-04',2)

above rows have same Trans_ID ,so it is one transection, secondly in first row Credit_Head_ID is not null and Level_Five_ID_CA column is null ,then we need to display what is being Debit in this transection.

Second row have Debit_Head_ID is not null and Level_Five_ID_CA is not null ,here i am inserting Credit_Head_ID into Level_Five_ID_CA ,which will be Countra Head of Debit .

Summary : In every transection ,there will be two or more rows ,in which debit or credit value will be exists with trans_Amount,so in Contra _Head ,we need to display Debit or Credit head vise versa .

khtan · November 17, 2023, 4:27am

In your expected output, the last row HEAD = Duties with 750 Cr. Why is it under Duties and not "'MBL 833968"?

akhter · November 17, 2023, 6:06am

it is acutely 'MBL 833968" and 750 is not amount of Duties
,but it is displaying in Head ,that what is being debit against credit when i will pass parameter of 'MBL 833968".
as you can see remarks ,in which mentioned that "Duties Rs.250 paid from 'MBL 833968" ,in remarks we get to know ,that Debit and Credit

khtan · November 17, 2023, 6:11am

From your sample data, amount 750 is related to 121100006 MBL 833968

But in your expected result, you are showing 750 under Duties

akhter · November 17, 2023, 6:41am

as you can see above ,i am passing parameter @level_Five_ID of MBL 833968 ,thats means i am displaying ledger of MBL 833968 ,in head i am showing ,what is being debit against Credit in a transection,

if i pass parameter of Duties ,then i will display in Head MBL 833968 ,but it Duties amount is 250

khtan · November 17, 2023, 7:44am

Sorry, I could not understand your logic or rules here.

VALUES(1,null,121100006,NULL,750,'2023-10-04',2)

From line above, I see a Cr 750.00 on 121100006. And there isn't anything about 124200002 in that line. I don't understand how in the required result, it is shown as Duties (124200002)

akhter · November 17, 2023, 9:42am

you could see trans_ID is 1,in below four row

INSERT INTO #tbl_Transection_Five VALUES(1,null,121100006,NULL,750,'2023-10-04',2)
INSERT INTO #tbl_Transection_Five VALUES(1,123100001,null,121100006,250,'2023-10-04',2)
INSERT INTO #tbl_Transection_Five VALUES(1,124200001,null,121100006,250,'2023-10-04',2) 
INSERT INTO #tbl_Transection_Five VALUES(1,124200002,null,121100006,250,'2023-10-04',2)