SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,651 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Below query is giving me correctly output,but Remarks are not coming in row.
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
Wrong Output
Correct Output