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.

Select reference, status, Valid, createDate, createdBy 
FROM 
(

select 1 as value, 'reference' reference, 'status' status, 'Valid' Valid, 'createDate' createDate, 'createdBy' createdBy

UNION ALL

SELECT 2 as value,

l.reference,

case l.number 
when 0 then 'not sent'
else 'sent'
end status,

l.Valid,
l.CreateDate,
l.CreatedBy
Convert(varchar 10), l.CreateDate, 111)CreateDate
From dbo.log l
where l.CreateDate > DATEADD(HOUR, -24, GETDATE())
) x 
ORDER BY x.CreateDate DESC

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.

Reference   Status   Valid  CreateDate       CreatedBy  SendMethod
Reference   Status   Valid  CreateDate       CreatedBy  SendMethod
1           Not sent    0   21/01/2020 12:00    John    initial send
2           Not sent    0   21/01/2020 12:03    Bob     initial send
3           Not sent    0   21/01/2020 12:05    Bob     initial send
4             Sent      1   19/01/2020 12:00    Jane    initial send
4             Sent      1   20/01/2020 12:00    Jane    subsequent send
4             Sent      1   21/01/2020 12:00    Jane    subsequent send

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

WITH [log]
AS
(SELECT
        Reference
       ,Number
       ,Valid
       ,CAST(CreateDate AS DATETIME) CreateDate
       ,CreatedBy
    FROM (VALUES(1, 0, 0, '20200121 12:00', 'John')
    , (2, 0, 0, '20200121 12:03', 'Bob')
    , (3, 0, 0, '20200121 12:05', 'Bob')
    , (4, 1, 1, '20200119 12:00', 'Jane')
    , (4, 1, 1, '20200120 12:00', 'Jane')
    , (4, 1, 1, '20200121 12:00', 'Jane')

    ) a (Reference, Number, Valid, CreateDate, CreatedBy))

SELECT
    x.reference
   ,x.status
   ,x.Valid
   ,x.createDate
   ,x.createdBy
   ,x.SendMethod
FROM (SELECT
        1 AS value
       ,'reference' reference
       ,'status' status
       ,'Valid' Valid
       ,'createDate' createDate
       ,'createdBy' createdBy
       ,'SendMethod' SendMethod
    UNION ALL
    SELECT
        2 AS value
       ,STR(l.reference) reference
       ,CASE l.number
            WHEN 0 THEN 'not sent'
            ELSE 'sent'
        END status
       ,STR(l.Valid) Valid
       ,CONVERT(VARCHAR(10), l.CreateDate, 111) CreateDate
       ,l.CreatedBy
       /* This is the magic line */
       /* If the first instance of reference is found it will be given row_number 1 */
       ,IIF(ROW_NUMBER() OVER (PARTITION BY l.reference ORDER BY l.CreateDate) = 1, 'initial send', 'subsequent send') SendMethod

    FROM log l
where l.CreateDate > DATEADD(HOUR, -24, GETDATE())
) x
ORDER BY [value], X.CreateDate DESC