Skip to content
Advertisement

Select the oldest date for a row and create a new column with values based on the oldest date or other

Firstly to explain what the select is doing currently.

The purpose of this data is to be used by another procedure to generate a file.

Hence why I have printed the column headers twice.

My issue is the following:

  • I need to count all the duplicate reference’s from the select
  • I need a new column called ‘SendMethod’
  • From those duplicate references I then need to know what row has the oldest CreateDate
  • Based on that I then need this new column to have initial send if it’s the oldest CreateDate out of those duplicate rows else it needs to have subsequent send

I need this new column to be part of the select.

So that it produces all of the data at once and not through multiple result sets.

Please see below example of how this would look with this new column included just with some dummy data.

Advertisement

Answer

You can use “row_number” to order by createdate, and then use for instance IIF to to test if its the first occurence of reference.

Here is and example

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement