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.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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?
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.
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.