Trying to get the latest record

helixpoint · December 13, 2019, 1:44pm

I have the following log table. I need to select the The last initials that changed this record

|152810|7|2019-04-19 12:51:00|WF|
|152810|8|2019-03-18 12:01:00|DL|
|152810|14|2019-12-11 07:28:00|DF|
|152810|8|2019-10-13 11:33:00|JW|
|152810|7|2019-03-18 12:51:00|DL|
|145330|7|2018-01-18 10:51:00|BD|

So I am searching for record ID 152810... I would get:....

|152810|14|2019-12-11 07:28:00|DF|
|152810|8|2019-10-13 11:33:00|JW|
|152810|7|2019-04-19 12:51:00|WF|

Wim_Leys · December 13, 2019, 1:51pm

What do you mean by

The last initials that changed this record

The very last one? The last three? as in your example?

Can you write the code (CREATE TABLE, INSERT INTO ..) scripts to create a table with the test data In?

harishgg1 · December 13, 2019, 2:14pm

hi

is this what you are looking for ?????

please click arrow to the left for drop create data ..

drop table #data 
go 

create table #data 
(
recordid int,
customfieldid int,
changeDate datetime ,
initials varchar(5)
)
go 

 insert into #data select 152810, 7,'2019-04-19 12:51:00','WF' 
 insert into #data select 152810, 8,'2019-03-18 12:01:00','DL' 
 insert into #data select 152810, 14,'2019-12-11 07:28:00','DF' 
 insert into #data select 152810, 8,'2019-10-13 11:33:00','JW' 
 insert into #data select 152810, 7,'2019-03-18 12:51:00','DL' 
 insert into #data select 145330, 7,'2018-01-18 10:51:00','BD' 
 go 
 
 select 'data',* from #data 
 go

please click arrow to the left for SQL Script

 ; with rn_cte as 
 (
 select ROW_NUMBER() OVER(PARTITION BY recordid order by changedate desc) as rn ,* from #data
 )
 select 'Last initials that modified record ',recordid,customfieldid,changeDate,initials  from rn_cte where rn = 1 
 go

helixpoint · December 13, 2019, 2:14pm

The last one to update it acording to changedate

CREATE TABLE [dbo].[tblTrkRecordLog1](
[Id] [int] IDENTITY(1,1) NOT NULL,
[RecordId] [int] NOT NULL,
[changeDate] [smalldatetime] NOT NULL,
[CustomFieldId] [int] NULL,
[initials] varchar NOT NULL,
CONSTRAINT [PK_tblTrkRecordLog1] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO dbo.tblTrkRecordLog1(RecordId, CustomFieldId,changeDate, initials) VALUES (152810,7,'2019-04-19 12:51:00','WF')
INSERT INTO dbo.tblTrkRecordLog1(RecordId, CustomFieldId,changeDate, initials) VALUES (152810,8,'2019-03-18 12:01:00','DL')
INSERT INTO dbo.tblTrkRecordLog1(RecordId, CustomFieldId,changeDate, initials) VALUES (152810,14,'2019-12-11 07:28:00','DF')
INSERT INTO dbo.tblTrkRecordLog1(RecordId, CustomFieldId,changeDate, initials) VALUES (152810,8,'2019-10-13 11:33:00','JW')
INSERT INTO dbo.tblTrkRecordLog1(RecordId, CustomFieldId,changeDate, initials) VALUES (152810,7,'2019-03-18 12:51:00','DL')
INSERT INTO dbo.tblTrkRecordLog1(RecordId, CustomFieldId,changeDate, initials) VALUES (145330,7,'2018-01-18 10:51:00','BD')

harishgg1 · December 13, 2019, 2:15pm

hi is this it

is this what you are looking for ?????

please click arrow to the left for drop create data ..

drop table #data 
go 

create table #data 
(
recordid int,
customfieldid int,
changeDate datetime ,
initials varchar(5)
)
go 

 insert into #data select 152810, 7,'2019-04-19 12:51:00','WF' 
 insert into #data select 152810, 8,'2019-03-18 12:01:00','DL' 
 insert into #data select 152810, 14,'2019-12-11 07:28:00','DF' 
 insert into #data select 152810, 8,'2019-10-13 11:33:00','JW' 
 insert into #data select 152810, 7,'2019-03-18 12:51:00','DL' 
 insert into #data select 145330, 7,'2018-01-18 10:51:00','BD' 
 go 
 
 select 'data',* from #data 
 go

please click arrow to the left for SQL Script

 ; with rn_cte as 
 (
 select ROW_NUMBER() OVER(PARTITION BY recordid order by changedate desc) as rn ,* from #data
 )
 select 'Last initials that modified record ',recordid,customfieldid,changeDate,initials  from rn_cte where rn = 1 
 go

helixpoint · December 13, 2019, 2:27pm

Why dosent 8 show up?

harishgg1 · December 13, 2019, 2:45pm

my earlier solution was for only 1

ok assuming you want last 3

 ; with rn_cte as 
 (
 select ROW_NUMBER() OVER(PARTITION BY recordid order by changedate desc) as rn ,* from #data
 )
 select 'Last 3 initials that modified record ',recordid,customfieldid,changeDate,initials  from rn_cte where rn in ( 1 ,2,3)
 go

yosiasz · December 13, 2019, 3:17pm

when searching 152810 you would get

|152810|7|2019-04-19 12:51:00|WF|
|152810|8|2019-03-18 12:01:00|DL|
|152810|14|2019-12-11 07:28:00|DF|
|152810|8|2019-10-13 11:33:00|JW|
|152810|7|2019-03-18 12:51:00|DL|