2 Replies Latest reply on Oct 28, 2013 8:55 AM by dawadawa

    Self join

    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!