Get the latest person initial last entered by date....REVISED

helixpoint · December 13, 2019, 3:36pm

So I want to get the 1record by customerfieldid by the latest date for the specific record id 152810. Here are the tables

CREATE TABLE [dbo].[tblTrkRecordLog1](
[Id] [int] IDENTITY(1,1) NOT NULL,
[RecordId] [int] NOT NULL,
[changeDate] [smalldatetime] NOT NULL,
[CustomFieldId] [int] NULL,
[EmployeeId] [int] 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, EmployeeId) VALUES (152810,7,'2019-04-19 12:51:00',1)
INSERT INTO dbo.tblTrkRecordLog1(RecordId, CustomFieldId,changeDate, EmployeeId) VALUES (152810,8,'2019-03-18 12:01:00',2)
INSERT INTO dbo.tblTrkRecordLog1(RecordId, CustomFieldId,changeDate, EmployeeId) VALUES (152810,14,'2019-12-11 07:28:00',2)
INSERT INTO dbo.tblTrkRecordLog1(RecordId, CustomFieldId,changeDate, EmployeeId) VALUES (152810,8,'2019-10-13 11:33:00',3)
INSERT INTO dbo.tblTrkRecordLog1(RecordId, CustomFieldId,changeDate, EmployeeId) VALUES (152810,7,'2019-03-18 12:51:00',2)
INSERT INTO dbo.tblTrkRecordLog1(RecordId, CustomFieldId,changeDate, EmployeeId) VALUES (145330,7,'2018-01-18 10:51:00',3)

CREATE TABLE [dbo].[tblEmployee1](
[Id] [int] NOT NULL,
[initials] varchar NOT NULL

CONSTRAINT [PK_tblEmployee1] 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.tblEmployee1(id, initials) VALUES (1,'df')
INSERT INTO dbo.tblEmployee1(id, initials) VALUES (2,'mh')
INSERT INTO dbo.tblEmployee1(id, initials) VALUES (3,'jk')

SO I WANT TO BRING BACK THE FOLLOWING-------------------------------

1 152810 2019-04-19 12:51:00 7 1 df
3 152810 2019-12-11 07:28:00 14 2 mh
4 152810 2019-10-13 11:33:00 8 3 jk

yosiasz · December 13, 2019, 4:08pm

script errors out. please fix

yosiasz · December 13, 2019, 4:23pm

;with juju
as
(
select RANK() OVER(PARTITION BY  initials ORDER BY changeDate desc) AS RowNumberRank, 
t.Id trackingId, RecordId, changeDate, e.Id employeeId, initials
  From tblEmployee1 e
  join [tblTrkRecordLog1] t on e.Id = t.EmployeeId
  where RecordId = 152810
)
select  trackingId, RecordId, changeDate, employeeId, initials
from juju 
where RowNumberRank = 1
order by 1

harishgg1 · December 13, 2019, 4:28pm

Script Error
varchar should be varchar(10)

please click arrow to the left for DROP CREATE Data ..

drop table [dbo].[tblTrkRecordLog1]
go 

CREATE TABLE [dbo].[tblTrkRecordLog1](
[Id] [int] IDENTITY(1,1) NOT NULL,
[RecordId] [int] NOT NULL,
[changeDate] [smalldatetime] NOT NULL,
[CustomFieldId] [int] NULL,
[EmployeeId] [int] 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, EmployeeId) VALUES (152810,7,'2019-04-19 12:51:00',1)
INSERT INTO dbo.tblTrkRecordLog1(RecordId, CustomFieldId,changeDate, EmployeeId) VALUES (152810,8,'2019-03-18 12:01:00',2)
INSERT INTO dbo.tblTrkRecordLog1(RecordId, CustomFieldId,changeDate, EmployeeId) VALUES (152810,14,'2019-12-11 07:28:00',2)
INSERT INTO dbo.tblTrkRecordLog1(RecordId, CustomFieldId,changeDate, EmployeeId) VALUES (152810,8,'2019-10-13 11:33:00',3)
INSERT INTO dbo.tblTrkRecordLog1(RecordId, CustomFieldId,changeDate, EmployeeId) VALUES (152810,7,'2019-03-18 12:51:00',2)
INSERT INTO dbo.tblTrkRecordLog1(RecordId, CustomFieldId,changeDate, EmployeeId) VALUES (145330,7,'2018-01-18 10:51:00',3)

drop table [dbo].[tblEmployee1]
go 

CREATE TABLE [dbo].[tblEmployee1](
[Id] [int] NOT NULL,
[initials] varchar(100) NOT NULL

CONSTRAINT [PK_tblEmployee1] 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.tblEmployee1(id, initials) VALUES (1,'df')
INSERT INTO dbo.tblEmployee1(id, initials) VALUES (2,'mh')
INSERT INTO dbo.tblEmployee1(id, initials) VALUES (3,'jk')

select * from [dbo].[tblTrkRecordLog1]
select * from [dbo].[tblEmployee1]
go

harishgg1 · December 13, 2019, 4:37pm

; WITH cte 
     AS (SELECT a.*, 
                b.[initials] 
         FROM   [dbo].[tbltrkrecordlog1] a 
                JOIN [dbo].[tblemployee1] b 
                  ON a.employeeid = b.id), 
     cte_max 
     AS (SELECT employeeid, 
                Max([changedate]) AS maxchangedate, 
                initials 
         FROM   cte 
         GROUP  BY employeeid, 
                   initials) 
SELECT 'SQL Output', 
       * 
FROM   cte_max 

go