SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Stampings
(
    ID INT,
    Date DATE,
    Hour VARCHAR(5),
    CardCode VARCHAR(3),
    Name VARCHAR(10),
    Surname VARCHAR(20),
    EmployeeFreshman VARCHAR(10)
);
CREATE TABLE Registries
(
    ID INT,
    CardCode VARCHAR(3),
    Name VARCHAR(10),
    Surname VARCHAR(20),
    EmployeeFreshman VARCHAR(10)
);
INSERT INTO Registries VALUES
(1, 'AAA', 'Name 1', 'Surname 1', 'A'),
(2, 'BBB', 'Name 2', 'Surname 2', 'B'),
(3, 'CCC', 'Name 3', 'Surname 3', 'C'),
(4, 'DDD', 'Name 4', 'Surname 4', 'D');
4 rows affected

INSERT INTO Stampings VALUES
(1, '20180101', '02:05', 'AAA', 'AAA Name', 'AA1 ''Surname', 'E'),
(2, '20180101', '05:05', 'AAA', 'AAA Name', 'AA2 ''Surname', 'E'),
(3, '20180101', '12:05', 'AAA', 'AAA Name', 'AA3 ''Surname', 'E'),
(4, '20180102', '12:05', 'BBB', 'BBB Name', 'BB4 Surname', 'F'),
(5, '20180102', '15:05', 'BBB', 'BBB Name', 'BB5 Surname', 'F'),
(6, '20180103', '11:05', 'CCC', 'CCC Name', 'CC6 ''Surname', 'G'),
(7, '20180103', '12:05', 'CCC', 'CCC Name', 'CC7 ''Surname', 'G'),
(8, '20180103', '13:05', 'CCC', 'CCC Name', 'CC8 ''Surname', 'G');
8 rows affected

SELECT Stampings.CardCode, 
       Stampings.Surname, 
       Stampings.Name, 
       Stampings.EmployeeFreshman 
FROM   Stampings 
JOIN   Registries 
ON     Stampings.CardCode = Registries.CardCode 
WHERE  Stampings.Date = (SELECT MAX(Date) 
                         FROM   Stampings 
                         WHERE  Stampings.CardCode = Registries.CardCode) 
AND    Stampings.Hour = (SELECT MAX(Hour) 
                         FROM   Stampings 
                         WHERE  Stampings.CardCode = Registries.CardCode) 
GROUP BY Stampings.CardCode, 
         Stampings.Surname, 
         Stampings.Name, 
         Stampings.EmployeeFreshman 
ORDER BY Stampings.CardCode


CardCode
Surname
Name
EmployeeFreshman


AAA
AA3 'Surname
AAA Name
E

BBB
BB5 Surname
BBB Name
F

CCC
CC8 'Surname
CCC Name
G


SELECT r.CardCode,
       r.Surname,
       r.Name,
       r.EmployeeFreshman,
       s.*
FROM   Registries r
CROSS APPLY (SELECT TOP 1
                    CardCode,
                    Surname,
                    Name,
                    EmployeeFreshman, Date, Hour
             FROM   Stampings
             WHERE  CardCode = r.CardCode
             AND    Surname LIKE '%''%'
             ORDER BY Date DESC, Hour DESC) s;


CardCode
Surname
Name
EmployeeFreshman
CardCode
Surname
Name
EmployeeFreshman
Date
Hour


AAA
Surname 1
Name 1
A
AAA
AA3 'Surname
AAA Name
E
2018-01-01
12:05

CCC
Surname 3
Name 3
C
CCC
CC8 'Surname
CCC Name
G
2018-01-03
13:05


UPDATE r
SET    Surname = REPLACE(s.Surname, '''', ''''''),
       Name = s.Name,
       EmployeeFreshman = s.EmployeeFreshman
FROM   Registries r
CROSS APPLY (SELECT TOP 1
                    CardCode,
                    Surname,
                    Name,
                    EmployeeFreshman, Date, Hour
             FROM   Stampings
             WHERE  CardCode = r.CardCode
             AND    Surname LIKE '%''%'
             ORDER BY Date DESC, Hour DESC) s;
2 rows affected

SELECT * FROM Registries;


ID
CardCode
Name
Surname
EmployeeFreshman


1
AAA
AAA Name
AA3 ''Surname
E

2
BBB
Name 2
Surname 2
B

3
CCC
CCC Name
CC8 ''Surname
G

4
DDD
Name 4
Surname 4
D

CardCode	Surname	Name	EmployeeFreshman
AAA	AA3 'Surname	AAA Name	E
BBB	BB5 Surname	BBB Name	F
CCC	CC8 'Surname	CCC Name	G

CardCode	Surname	Name	EmployeeFreshman	CardCode	Surname	Name	EmployeeFreshman	Date	Hour
AAA	Surname 1	Name 1	A	AAA	AA3 'Surname	AAA Name	E	2018-01-01	12:05
CCC	Surname 3	Name 3	C	CCC	CC8 'Surname	CCC Name	G	2018-01-03	13:05

ID	CardCode	Name	Surname	EmployeeFreshman
1	AAA	AAA Name	AA3 ''Surname	E
2	BBB	Name 2	Surname 2	B
3	CCC	CCC Name	CC8 ''Surname	G
4	DDD	Name 4	Surname 4	D