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