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

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

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Thanks Phil. That's what I needed.

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

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