Dear Friends
Please help me to modify this query for the remove this kind of records from the out put
DECLARE @cust_id INT;
DECLARE @Amount DECIMAL(18,2);
DECLARE @ReferenceId VARCHAR(100);
DECLARE @Balance DECIMAL(18,2);
DECLARE @BonusBalance DECIMAL(18,2);
DECLARE @desc VARCHAR(MAX);
DROP TABLE IF EXISTS #duplicates;
-- Using ROW_NUMBER() instead of COUNT(1) for better performance
WITH Duplicates AS (
SELECT
ReferenceId,
CeatedDate,
ROW_NUMBER() OVER (PARTITION BY ReferenceId ORDER BY CeatedDate DESC) AS rn
FROM [Transaction]
WHERE CeatedDate > DATEADD(DAY, -2, GETDATE())
AND transactionTypeID = 2
)
SELECT ReferenceId, CeatedDate
INTO #duplicates
FROM Duplicates
WHERE rn > 1;
-- Main query with improved string parsing
SELECT DISTINCT
cus.FirstName,
cus.LastName,
cus.ContactNumber,
cus.EmailAddress,
t.[Description],
t.Amount,
b.SportName,
t.CustomerId,
d.ReferenceId,
b.BetSlipHeaderId
FROM [Transaction] t
INNER JOIN Customer cus ON cus.id = t.CustomerId
INNER JOIN #duplicates d ON d.ReferenceId = t.ReferenceId
AND d.CeatedDate = t.CeatedDate
LEFT JOIN BetSlipItem b ON b.BetSlipHeaderId =
CASE
WHEN t.[Description] LIKE '%-%-%'
THEN TRY_CAST(PARSENAME(REPLACE(t.[Description], '-', '.'), 2) AS INT)
ELSE NULL
END
WHERE b.source in(1,2,3,4)-- b.source<>6--(b.SportName NOT IN ('casino') OR b.SportName IS NULL)
ORDER BY t.Amount;
