How to identify if I have a voided transaction in T-SQL

Villa 231 Reputation points
2023-04-20T21:20:15.04+00:00

I have a scenario where I identified a transaction with certain strings, sample '%Bond Redemption%' in the COMMENT field. This transaction has a VOIDCODE of 0 (zero). It means 'No Void' and it also has a SEQUENCENUMBER associated with this transaction. What I would like to do is identifying when there is a VOIDCODE of 0, do I have VOIDECODE of 1 (VOIDED) transactions? User's image

Sample query below to get above sample data and my query to answer my objective.


;WITH CTE_Bond AS 
(
	SELECT
		PARENTACCOUNT
		,PARENTID
		,SEQUENCENUMBER
		,SEQUENCENUMBER + 1 AS NextSequenceNumber

	FROM dbo.SAVINGSTRANSACTION 
	WHERE POSTDATE  > '2022-01-01'
	AND COMMENT like '%Bond Redemption%' 
	AND VOIDCODE = 0 --No Void

) 


SELECT

	*

FROM dbo.SAVINGSTRANSACTION as s

INNER JOIN CTE_Bond as b
	ON b.PARENTACCOUNT = s.PARENTACCOUNT
	AND b.PARENTID = s.PARENTID
	AND b.NextSequenceNumber = s.SEQUENCENUMBER 
	AND VOIDCODE = 1 --VOID

The way I currently identified if there is a VOID transaction is; I am assuming that the next SEQUENCENUMBER is going to be the VOIDED transaction by using SEQUENCENUMBER + 1. I do not feel this is the proper way for me to identify if there's a voided transaction by using SEQUENCENUMBER + 1.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,423 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Benjamin Shaw 141 Reputation points
    2023-07-03T17:51:30.1566667+00:00

    If I understand you correctly...

    In your code the final void code will always be 0 since you are limiting the CTE_Bond with VOIDCODE = 0 and then making and INNER JOIN between CTE_Bond and SAVINGSTRANSACTION.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.