0 Replies Latest reply: Jan 28, 2013 10:08 AM by Neil Henderson RSS

    Identifiy the last version when meets a certain criteria and grouped by RefNo

      I need to be able to create a Field that flags either yes or no based on a particular set of criteria.  Please see mock table below.

       

      RefNoTypeRefSeqClaim Type StateClaim Type State Done DateClaim Type State Id Changed Flag
      539902AD1Notified08/11/2011 10:40:44No
      539902AD1Closed08/11/2011 10:40:44Yes
      539902PI1Closed08/11/2011 10:40:44No
      539902PI1Notified08/11/2011 10:40:44No
      539902PI4ReOpened13/12/2011 10:10:42No
      539902PI10ReClosed18/04/2012 15:30:51No
      539902PI11ReOpened28/06/2012 09:29:28No
      539902PI12ReClosed29/06/2012 08:43:33Yes
      539902TP1Notified08/11/2011 10:40:44No
      539902TP2Closed08/11/2011 11:06:14No
      539902TP7ReOpened05/01/2012 15:16:36No
      539902TP8ReClosed06/01/2012 13:06:34Yes

       

      I am trying to create the field highlighted with the red writing.

       

      I need to distinguish when the last RefSeq of a particular RefNoType is either Closed or ReClosed when this is true to show 'Yes'

      if it is notified or Reopened or if the there is another RefSeQ after it was CLosed or Reclosed then it should show 'No'

       

      I would appreciate any help.

       

      Thanks in advance

       

      Matt