SQL update from one table to another table

Transact-SQL
,
1

Hi,

I have two table @sample_receipts and @sample_building. I will match the idno of @sample_building
to @sample_receipts table using the latest idno or recoverid if exist and update the column build. My query gives me wrong result. I need only the latest idno or recovered to be updated. Thank you in advance.

below are the sample DDL and expected result.

This is the table @sample_building where column build fields need to be extract for a particular idno.


declare @sample_building table
(
idno varchar(50),  build_dt datetime,  build int)
insert @sample_building values ('ABSBC9240887403282','2018-09-19 15:34:47.000',1)
insert @sample_building values ('ABCDEFGHIJK0000001','2018-01-24 09:57:55.000',1)
insert @sample_building values ('ABCDEFGHIJK0000444','2018-10-14 07:18:56.000',1)
insert @sample_building values ('FGHIJK0000002','2018-10-10 08:19:56.000',1)

and this is the table @sample_receipts where build column will be updated coming from the table @sample_building 

declare @sample_receipts table
(
idno varchar(50), recoverid varchar(50),  r_dt datetime, build int NULL)
insert @sample_receipts values ('ABSBC9240887403282','ABSBC92408874','2018-06-22 08:01:31.000',NULL)
insert @sample_receipts values ('ABSBC9240887403282','ABSBC92408874','2018-01-22 08:01:31.000',NULL)
insert @sample_receipts values ('T000C9240887403282','ABCDEFGHIJK0000001','2016-09-01 03:56:00.000',NULL)
insert @sample_receipts values ('TABDC0887403282000','ABCDEFGHIJK0000001','2017-12-16 13:41:00.000',NULL)
insert @sample_receipts values ('ABCDEFGHIJK0000444','NULL','2018-10-14 15:18:56.000',NULL)
insert @sample_receipts values ('ABCDEFGHIZP0000000','FGHIJK0000002','2018-09-14 14:18:56.000',NULL)




--use recoverid
select  t1.idno, t1.recoverid, t1.r_dt, t1.build, t2.build as inbuilding
from @sample_receipts t1
inner join @sample_building t2 on t1.recoverid = t2.idno and (t2.build_dt >= t1.r_dt)

--use idno
select  t1.idno, t1.recoverid, t1.r_dt, t1.build, t2.build as inbuilding
from @sample_receipts t1
inner join @sample_building t2 on t1.idno = t2.idno and t2.build_dt >= t1.r_dt




Below is the expected result:

idno-----------------recover-------------r_dt---------------------build
ABSBC9240887403282---ABSBC92408874-------2018-06-22 08:01:31.000----1
TABDC0887403282000---ABCDEFGHIJK0000001--2017-12-16 13:41:00.000----1
ABCDEFGHIJK0000444---NULL----------------2018-10-14 15:18:56.000----1
ABCDEFGHIZP0000000---FGHIJK0000002-------2018-09-14 14:18:56.000----1


Result after updating the table   @sample_receipts
idno-----------------recoverid-----------r_dt--------------------build
ABSBC9240887403282---ABSBC92408874-------2018-06-22 08:01:31.000--1
ABSBC9240887403282---ABSBC92408874-------2018-01-22 08:01:31.000--NULL
T000C9240887403282---ABCDEFGHIJK0000001--2016-09-01 03:56:00.000--NULL
TABDC0887403282000---ABCDEFGHIJK0000001--2017-12-16 13:41:00.000--1
ABCDEFGHIJK0000444---NULL----------------2018-10-14 15:18:56.000--1
ABCDEFGHIZP0000000---FGHIJK0000002-------2018-09-14 14:18:56.000--1
2

Give this a try. The built flag in my solution differs from yours.
I use a Common Table Expression to find the most recent Receipt by giving the records belonging to one idno a Rownum that starts from 1 (the most recent one) and up. You only want to work with he records with RowNum = 1.

;WITH latestReceipt AS ( -- latest receipt for one idno is the one with RowNum = 1
SELECT idno, recoverid, r_dt, build, ROW_NUMBER() OVER (PARTITION BY idno ORDER BY r_dt DESC) as RowNum
FROM @sample_receipts
)
SELECT latestReceipt.idno, latestReceipt.recoverid, latestReceipt.r_dt, SB.build
FROM latestReceipt
	LEFT OUTER JOIN @sample_building as SB ON 
		latestReceipt.idno = SB.idno 
		AND latestReceipt.r_dt < SB.build_dt
WHERE latestReceipt.RowNum = 1