By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE history_table (
Id INT,
Tnum VARCHAR(10),
Snum VARCHAR(10),
Rnum INT
)
CREATE TABLE new_data_table (
Id INT,
Tnum VARCHAR(10),
Snum VARCHAR(10),
Rnum INT
)
INSERT INTO history_table
VALUES
(1, 'A1234', 'F1', 0),
(2, 'A1234', 'N1', 0),
(3, 'B1234', 'SP', 2),
(4, 'B1234', 'FW', 2),
(5, 'A1234', 'F1', 1),
(6, 'A1234', 'N1', 1),
(7, 'C1234', 'I1', 0),
(8, 'C1234', 'I2', 0),
(9, 'A1234', 'F1', 2),
(10, 'A1234', 'N1', 2)
INSERT INTO new_data_table
VALUES
(1, 'A1234', 'F1', 3),
(2, 'B1234', 'FW', 3),
(3, 'C1234', 'I2', 1)
13 rows affected
with pairs as (
select v.*
from (
values
('F1', 'N1'),
('N1', 'F1'),
('SP', 'FW'),
('FW', 'SP'),
('I1', 'I2'),
('I2', 'I1')
) v(First, Second)
)
select h1.*
from new_data_table n
join pairs p on p.First = n.Snum
cross apply (
select top 1 h.*
from history_table h
where h.Tnum = n.Tnum
and h.Snum = p.Second
order by h.Rnum desc
) h1
| Id | Tnum | Snum | Rnum |
|---|---|---|---|
| 10 | A1234 | N1 | 2 |
| 3 | B1234 | SP | 2 |
| 7 | C1234 | I1 | 0 |
with pairs as (
select distinct h1.Snum AS First, h2.Snum AS Second
from history_table h1
join history_table h2
on h2.Tnum = h1.Tnum
and h2.Snum <> h1.Snum
)
select * from pairs
| First | Second |
|---|---|
| F1 | N1 |
| FW | SP |
| I1 | I2 |
| I2 | I1 |
| N1 | F1 |
| SP | FW |
with pairs as (
select distinct h1.Snum AS First, h2.Snum AS Second
from history_table h1
join history_table h2
on h2.Tnum = h1.Tnum
and h2.Snum <> h1.Snum
)
select h1.*
from new_data_table n
join pairs p on p.First = n.Snum
cross apply (
select top 1 h.*
from history_table h
where h.Tnum = n.Tnum
and h.Snum = p.Second
order by h.Rnum desc
) h1
| Id | Tnum | Snum | Rnum |
|---|---|---|---|
| 10 | A1234 | N1 | 2 |
| 3 | B1234 | SP | 2 |
| 7 | C1234 | I1 | 0 |