derive a column that points to the MAX of 4 other columns

• Greg J

  SSCarpal Tunnel

  Points: 4288

  More actions

  November 22, 2011 at 10:04 am

  #247180

  My data flow object has these columns in it...

  x

  y

  z

  Date1

  Date2

  Date3

  Date4

  I need to figure out (derive?) which column contains the most recent date, and stash that information as an additional field in the data flow, so that the new columns availed to the flow are...

  x

  y

  z

  Date1

  Date2

  Date3

  Date4

  [whichcol] -- in (1,2,3,4)

  I DON'T NEED THE DATE, I NEED TO KNOW THE COLUMN THAT CONTAINS IT. What's the easiest way to do this? Can I have a derived column? What would my expression be? Could the "aggregate" transform object help me out here?

  Also, the flow is sourced with a SELECT, so I could put this logic in there if needed (CASE?).

  PS, any/all date fields may be null. Thanks!

• Phil Parkin

  SSC Guru

  Points: 246985

  More actions

  November 22, 2011 at 10:30 am

  #1411674

  I had to do something very similar recently. Check this thread for a fast T-SQL solution (and many other interesting suggestions!)

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Greg J

  SSCarpal Tunnel

  Points: 4288

  More actions

  November 22, 2011 at 10:42 am

  #1411687

  Thanks Phil. That's what I needed.