Post reply

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

  • 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!

  • 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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • November 22, 2011 at 10:42 am

    #1411687

    Thanks Phil. That's what I needed.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply