AnsweredAssumed Answered

Self join

Question asked by dawadawa on Oct 25, 2013
Latest reply on Oct 28, 2013 by dawadawa

Title

Self join

Post

     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.

     PROBLEM:

     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!

     Thanks!

      

Outcomes