I am trying to accomplish the following, which seemed really simple, but I cannot get my head around this:
I have a single table:
- each transaction has its UniqueID
- each transaction has a specific type ( Order, Cancellation, Refund, Review)
- Cancellations, Refunds and Reviews have a ReferenceID (which refers to the UniqueID of the Order transaction)
I created a calculated status field which will show the status for each order. I did this with a with a case statement, so the status will show "Cancellation", "Refund" etc.
There are situations, where I have a Cancellation & a Refund for the same Order (there are two transactions with the same ReferenceID). I would like to have the status field to bring up a specific value "Cancellation&Refund". There is also the case of multiple cancellations, but no Refund for the same order(this should not result in "Cancelation&Refund").
I have tried a thousand things as I thought this was related to the wrong calculation(I am a beginner, as you probably guessed up to now ;-). However, it seems there is some conceptual problem, but I am not sure what I am doing wrong here.
Any help is greatly appreciated!