Joining table on itsself to find new orders across periods

[#1]

Hi everyone, I need some help please.

I have a snapshot table that I merge all of the orders in progress every week into.  It looks like this -

ORDER NUMBER, STATUS, SNAPSHOT_DATE

1,   Held,           5/10/2014

2,   In Progress, 5/10/2014

3,   Held            5/10/2014

1,   Held            12/10/2014

2,   Held            12/10/2014

4,   Held            12/10/2014

What I need to do is look across each snapshot date and find held orders that were new that week.

In the above example I would expect to see order number 4 as new in 12/10/14 and also order 2 because it wasnt held status the week before.

So basically I need to join the table on itsself where order number <> order number OR order number = order number and status <> status but one of the statuses is held.

Could someone please help me with this!!

Thanks in advance!!!

[Gysbert_Wassenaar]

See attached qvw for a possible solution.

[#3]

Thank you!! This is really close but for some reason it's producing duplicates in the table?

[Gysbert_Wassenaar]

Where? I don't see any duplicates.