Is it possible to use CTE for the data exist in one table, not 2?

Question

Imagine a factory for producing credit/debit cards, where the card will go through different stages of production. For example, a card will go through a molding stage, then will pass through a printing stage. Sometimes if there's problem the card will be sent back to the previous stages.

I want to write a query to find all cards that went through printing but is sent back molding (since molding is prior to printing).

What would be the way to "link" the cards data that exist both stages (they are in the same table):

select distinct barcode
from [Audit]..Stages (nolock)
where clientID = 'AmericanExpress'
    and stagename = 'printing'
    and Description <> 'Success'

select distinct barcode
from [Audit]..Stages (nolock)
where clientID = 'AmericanExpress'
    and stagename = 'Molding'
   and Description = 'Success'

I'm confused on the right way to "filter" since all the data exist in 1 table, not 2.

;
with failure as (
    select distinct barcode
    from [Audit]..Stages (nolock)
    where clientID = 'AmericanExpress'
        and stagename = 'printing'
        and Description <> 'Success'
)

success as (select distinct jt.jtpsid, jt.jtbarcode
from failure
with failure as (
    select distinct barcode
    from [Audit]..Stages (nolock)
    where clientID = 'AmericanExpress'
    and stagename = 'Molding'
    and Description = 'Success')

Answer 1

You want to select all cards that are currently in the molding stage, and also have a printing stage where the date for the stage did not succeed. To do this you can JOIN the table to itself:

SELECT distinct barcode
FROM [Audit]..Stages s0
INNER JOIN [Audit]..Stages s1 ON s1.barcode = s0.barcode 
    AND s1.stagename = 'printing'
    AND s1.Description <> 'Success'
WHERE s0.stagename = 'molding'
    AND s0.Description = = 'success'

This would be even better if you can know the date of the s1 record is older than for the s0 record, since I expect there's probably also some kind of in-process stage that is also not a success.

Also, get rid of those (nolock) hints. They probably are not accomplishing what you've been led to believe... at least, not in a safe way. (nolock) can make a database faster by ignoring certain locks. This comes at the risk of reading stale data. The thing is, the performance is only improved in those situations when the risk of reading stale data is much higher. If there's no stale data, then there wouldn't be a lock to ignore.