CODEWITHSUNDEEP
sqlsql-server
Teknas
Teknas

Reputation: 559

SQL: Getting previous record from other table

I want to get the previous record of each record in Table A from Table B.

enter image description here

for easy, below is the table sample data:

drop table if exists #A
drop table if exists #B


CREATE TABLE #A(Name varchar(10), time datetime, value int)
insert into #A values
('A', '2020-03-31 18:00:00', 56),
('A', '2020-03-31 19:00:00', 3),
('B', '2020-03-31 14:00:00', 14),
('C', '2020-03-31 15:00:00', 26)


CREATE TABLE #B(Name varchar(10), time datetime, value int)
insert into #A values
('A', '2020-03-31 21:00:00', 79),
('A', '2020-03-31 17:00:00', 44),
('A', '2020-03-31 14:00:00', 76),
('B', '2020-03-31 18:00:00', 89),
('C', '2020-03-31 11:00:00', 29),
('C', '2020-03-31 08:00:00', 6)

EDIT: It should include only last previous record from TableB. Sorry for the confusion. Changed image and sample data also.

Upvotes: 2

Views: 74

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I think you want:

select a.name, a.time, a.value
from #a a
union all
select b.name, b.time, b.value
from (select b.*, row_number() over (order by time desc) as seqnum
      from #b b
      where b.time < (select min(a.time)
                      from #a a
                      where a.name = b.name
                     )
     ) b
where seqnum = 1
order by name, time;

Here is a db<>fiddle.

EDIT:

If b could have multiple "previous" records, then:

select a.name, a.time, a.value
from #a a
union all
select b.name, b.time, b.value
from (select b.*, 
             row_number() over (partition by b.name order by b.time desc) as seqnum
      from #b b
      where b.time < (select min(a.time)
                      from #a a
                      where a.name = b.name
                     )
     ) b
where seqnum = 1
order by name, time;

Here is a db<>fiddle for this version.

Upvotes: 1

Related Questions