How do I get the eariest date per NameKey

Transact-SQL
1

I have a table with the following fields:

FieldName UserInitials DateEntered NameKey
Initiate Request DF 2019-09-23 14:00:00 initiaterequest
Transfer Effective Date DL 2019-01-23 16:00:00 initiaterequestdate
Initiate Request PM 2018-01-23 11:00:00 initiaterequest
Initiate Request Date DF 2018-11-23 11:00:00 transfereffectivedate

I need to be able to select the record that was entered with the oldest date per NameKey. How do I do this? So....

Transfer Effective Date DL 2019-01-23 16:00:00 initiaterequestdate
Initiate Request PM 2018-01-23 11:00:00 initiaterequest
Initiate Request Date DF 2018-11-23 11:00:00 transfereffectivedate

2

hi

i tried to do this
hope it helps :slight_smile: :slight_smile:

please click arrow mark to the left to see "drop create data" 
drop table #data 
go 


create table #data 
(
FieldName varchar(100) ,
UserInitials varchar(100) ,
DateEntered datetime , 
NameKey varchar(100) 
)
go 

insert into #data select 'Initiate Request','DF','2019-09-23 14:00:00','initiaterequest'
insert into #data select 'Transfer Effective Date','DL','2019-01-23 16:00:00','initiaterequestdate'
insert into #data select 'Initiate Request','PM','2018-01-23 11:00:00','initiaterequest'
insert into #data select 'Initiate Request Date','DF','2018-11-23 11:00:00','transfereffectivedate'
go 

select * from #data
go 

drop table #wanted_output 
go 

create table #wanted_output 
(
FieldName varchar(100) ,
UserInitials varchar(100) ,
DateEntered datetime , 
NameKey varchar(100) 
)
go 

insert into #wanted_output select 'Transfer Effective Date','DL','2019-01-23 16:00:00','initiaterequestdate'
insert into #wanted_output select 'Initiate Request','PM','2018-01-23 11:00:00','initiaterequest'
insert into #wanted_output select 'Initiate Request Date','DF','2018-11-23 11:00:00','transfereffectivedate'
go 

select * from #wanted_output
go
please click arrow mark to the left to see "SQL" 
SELECT a.* 
FROM   #data a 
       JOIN (SELECT fieldname, 
                    Min(dateentered) AS mindate 
             FROM   #data 
             GROUP  BY fieldname) b 
         ON a.fieldname = b.fieldname 
            AND a.dateentered = b.mindate 

go

image
image.png1355×674 352 KB

1 Like
3

please provide your data in a consumable fashion

create table #helixpoint(UserInitials varchar(50), DateEntered Datetime, 
NameKey varchar(25)

insert into #helixpoint
select

etc

Cant make heads or tails what is what in your post.

4

AWSOME!!!!!