i want countra Head , Create table #Tbl_Customer (C_ID int,C_Name varchar(50)) Create table #tbl_Ledger (ID int,E_Date date,Debit_Head_ID int,Credit_Head_ID int,T_Amount Decimal(10,2),Countra_ID int ) insert into #Tbl_Customer values (1001,'Akhter') insert into #Tbl_Customer values (1002,'Noman') insert into #Tbl_Customer values (1003,'Bank') insert into #Tbl_Customer values (1004,'Akram') Insert into #tbl_Ledger values (1,'2023-09-01',1001,Null,4000,1003) Insert into #tbl_Ledger values (1,'2023-09-01',Null,1003,4000,Null) Insert into #tbl_Ledger values (1,'2023-09-01',1002,Null,1000,1003) Insert into #tbl_Ledger values (1,'2023-09-01',1004,Null,1000,1003) if i pass parameter 1003 ,then below output must display if i pass parameter 1001 ,then below output must display

Expected output required

Accepted answer

Viorel 112.8K

Do you need something like this?

select E_Date, string_agg( C_Name, CHAR(13) + CHAR(10)) as Countra_Head
from #Tbl_Customer c
inner join
(
    select E_Date, Countra_ID
    from #tbl_Ledger where Debit_Head_ID = @Parameter
    union
    select E_Date, Debit_Head_ID
    from #tbl_Ledger where Countra_ID = @Parameter
) t on t.Countra_ID = c.C_ID
group by E_Date

Analyst_SQL 3,551

@Viorel

i have another ledger query,i am getting confuse,that how can i use above code with below,i tried but not getting expected output.


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


; WITH 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 Trans_ID,Trans_Date , 
     --Head = coaDCA.Level_Five_Name,
   Concat((CoaD.Level_Five_Name), CHAR(13) + CHAR(10)) as 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_Account_L_Five  coaDCA On coaDCA.Level_Five_ID = t.Level_Five_ID_CA
		select E_Date, Level_Five_ID_CA
    from tbl_transection_five 
	where Level_Five_ID_D = @Level_Five_ID
    union
    select E_Date, Level_Five_ID_D
    from tbl_transection_five
	 where Level_Five_ID_CA = @Level_Five_ID
	)
	t1 on t1.Level_Five_ID_CA = coaD.Level_Five_ID 


          left Join tbl_trans_type ty On ty.trans_type_ID = t.Trans_Type_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,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 Trans_Date<@startDate
	Order by Trans_Date desc,Trans_ID desc

)
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, Trans_Date,  Trans_Remarks,HEAD, Debit, Credit, Balance
FROM   runsum
WHERE  Trans_Date BETWEEN @startDate AND @EndDate
ORDER BY TransDate asc

Viorel 112.8K Reputation points

2023-11-10T11:30:42.68+00:00

Probably it is better to start a new question, giving the expected output and sample data, because the problem seems to require more tables.
Analyst_SQL 3,551 Reputation points

2023-11-10T11:53:44.6133333+00:00

@Viorel

if i post that question ,it will be remove.
Analyst_SQL 3,551 Reputation points

2023-11-15T05:28:57.24+00:00

@Viorel

You still did not review below post https://learn.microsoft.com/en-us/answers/questions/1421913/how-to-inline-head-in-union-all-in-row-wise

Share via

Expected output required

0 additional answers