SQL sever: Incorrect values using update table from another table

Transact-SQL
,
1

I have an script that update table but I have given incorrect values and does not match the tnumber. I have 2 tables, @sampleReceipt, this table contains the tnumber with corresponding idno while this table @sampleTrans contains transaction records. I have to update a column tnumber that came from @sampleReceipt, basically, I will find the records from @sampleTrans if match with the records from @sampleReceipt.

Based on my sample. could not figure out how can I much the records of this idno 3123456789012, 08123456789012345678 from @sampleTrans which i'm getting an inccorect maching of records. This incorrect error came from the First Query. any help is very much appreciated. Thank you.

Below is my sample DDL and Desired result.

--sample data:
declare @sampleTrans table
(
idno varchar(50), origidno varchar(50), recoveridno  varchar(50), dt datetime,  op_desc varchar(20), linenumber varchar(10), tnumber varchar(50))

insert @sampleTrans values ('A123456789012392','0012345123456789008','A123456789012392','2019-09-27 12:12:41.000','Good','Line1',NULL)--
insert @sampleTrans values ('1234567890123401','1234567890123401','','2019-10-02 19:32:45.000','Bad','Line2',NULL)--
insert @sampleTrans values ('A123456789012392','A123456789012392','','2019-08-15 13:30:20.000','Good','Line2',NULL)
insert @sampleTrans values ('A123456789012392','A123456789012392','','2019-10-02 18:45:51.000','Good','Line2',NULL)
insert @sampleTrans values ('A123456789012392','0012345123456789033','A123456789012392','2019-08-08 20:33:29.000','Good','Line1',NULL)
insert @sampleTrans values ('1234567890123401','0012345123456789033','1234567890123401','2019-09-27 12:11:45.000','Good','Line1',NULL)
insert @sampleTrans values ('3123456789012','3123456789012','','2019-04-03 12:18:07.000','Good','Line1',NULL)--
insert @sampleTrans values ('3123456789012','08123456789012345678','3123456789012','2019-08-17 02:29:59.000','Good','Line1',NULL)--
insert @sampleTrans values ('3123456789012','3123456789012','','2019-08-19 02:29:59.000','Good','Line2',NULL)
insert @sampleTrans values ('B123456789012345','B123456789012345678','','2018-07-10 14:30:20.000','Good','Line1',NULL)
insert @sampleTrans values ('B123456789012356','B123456789012345678','','2019-10-09 19:45:51.000','Good','Line1',NULL)
insert @sampleTrans values ('B123456789012356','B123456789012345678','B123456789012356','2019-11-09 19:45:51.000','Good','Line2',NULL)




declare  @sampleReceipt table
(
idno varchar(50), dt datetime, opdesc varchar(10), tnumber varchar(50))
insert @sampleReceipt values ('0012345123456789033','2019-08-06 22:29:16.000','Received','2019-08-06 14:29:16.0012345123456789033')
insert @sampleReceipt values ('0012345123456789033','2019-09-26 08:54:03.000','Received','2019-09-26 00:54:33.0012345123456789033')
insert @sampleReceipt values ('0012345123456789008','2019-09-26 08:54:03.000','Received','2019-09-26 00:54:03.0012345123456789008')
insert @sampleReceipt values ('3123456789012','3-31-2019 7:31:44.000','Received','3-31-2019 7:31:44.000.3123456789012')
insert @sampleReceipt values ('08123456789012345678','8-11-2019 8:39.000','Received','8-11-2019 8:39.000.081234567890123456788')
insert @sampleReceipt values ('B123456789012345678','2018-06-06 22:29:16.000','Received','2019-08-06 14:29:16.00B123456789012345678')
insert @sampleReceipt values ('B123456789012345678','2019-09-26 08:54:03.000','Received','2019-09-26 00:54:33.00B123456789012345678')

--First Query
;with cte_sampleReceipt as 
(
select 
idno,
dt,
LEAD(dt,1) over(partition by idno order by dt) as lead_dt,
tnumber  
from @sampleReceipt
)
update T set tnumber=T1.tnumber 
from @sampleTrans T
inner join  cte_sampleReceipt T1 on T.origidno=T1.idno and ((T.dt>=T1.dt and T1.lead_dt is null) OR (T.dt>=T1.dt and T.dt<T1.lead_dt and T1.lead_dt is not null))
where T.tnumber is null



--Second Query
;with cte as
(
select distinct T.idno,T1.origidno as new_origidno,T2.dt,T.tnumber
from @sampleTrans T
inner join @sampleTrans T1 on T.idno=T1.idno and T.recoveridno=T1.origidno
inner join @sampleReceipt T2 on T.origidno=T2.idno
),
cte_sampleReceipt as 
(
select 
idno,
new_origidno,
dt,
LEAD(dt,1) over(partition by idno order by dt) as lead_dt,
tnumber  
from cte
)
update T set tnumber=T1.tnumber 
from @sampleTrans T
inner join  cte_sampleReceipt T1 on T.idno=T1.idno and T.origidno=T1.new_origidno and ((T.dt>=T1.dt and T1.lead_dt is null) OR (T.dt>=T1.dt and T.dt<T1.lead_dt and T1.lead_dt is not null))
where T.tnumber is null

select t1.* 
from @sampleTrans t1
order by dt


Sample Expected Result:
/*
idno				origidno				recoveridno			dt						op_desc	linenumber	tnumber
============================================================================================================================================
B123456789012345	B123456789012345678							2018-07-10 14:30:20.000	Good	Line1		2019-08-06 14:29:16.00B123456789012345678
3123456789012		3123456789012								2019-04-03 12:18:07.000	Good	Line1		3-31-2019 7:31:44.000.3123456789012
A123456789012392	0012345123456789033		A123456789012392	2019-08-08 20:33:29.000	Good	Line1		2019-08-06 14:29:16.0012345123456789033
A123456789012392	A123456789012392							2019-08-15 13:30:20.000	Good	Line2		2019-08-06 14:29:16.0012345123456789033
3123456789012		08123456789012345678	3123456789012		2019-08-17 02:29:59.000	Good	Line1		8-11-2019 8:39.000.081234567890123456788
3123456789012		3123456789012								2019-08-19 02:29:59.000	Good	Line2		3-31-2019 7:31:44.000.3123456789012 -- incorrect tnumber, this should be 8-11-2019 8:39.000.081234567890123456788 
1234567890123401	0012345123456789033		1234567890123401	2019-09-27 12:11:45.000	Good	Line1		2019-09-26 00:54:33.0012345123456789033
A123456789012392	0012345123456789008		A123456789012392	2019-09-27 12:12:41.000	Good	Line1		2019-09-26 00:54:03.0012345123456789008
A123456789012392	A123456789012392							2019-10-02 18:45:51.000	Good	Line2		2019-09-26 00:54:03.0012345123456789008
1234567890123401	1234567890123401							2019-10-02 19:32:45.000	Bad		Line2		2019-09-26 00:54:33.0012345123456789033
B123456789012356	B123456789012345678							2019-10-09 19:45:51.000	Good	Line1		2019-09-26 00:54:33.00B123456789012345678
B123456789012356	B123456789012345678		B123456789012356	2019-11-09 19:45:51.000	Good	Line2		2019-09-26 00:54:33.00B123456789012345678
*/
2

hi

based on my analysis
3123456789012 = this is where incorrect tnumber is coming

SELECT idno,
dt,
Lead(dt, 1)
OVER(
partition BY idno
ORDER BY dt) AS lead_dt,
tnumber
FROM #samplereceipt
WHERE idno = '3123456789012'

is giving

image
image1536×157 3.89 KB

SELECT *
FROM #sampletrans
WHERE origidno = '3123456789012'

is giving

image
image1788×227 6.83 KB

in this 2nd row gives what you want !!!
how you pick 2nd row !!!! ??? logic .. upto you

Hope this helps :slight_smile: :slight_smile:

3

The second row is not nearest from the receipt records. The matching tnumber for 2nd row is coming from this idno 08123456789012345678. I'm thinking this is incorrect trasanction. the problem the records is polluted by this issue. its very hard to modify the script everytime I encounter different scenario.

4

hi Villanuev

Same experience for me ..... And Lot of people I know

Data issues ... anamolies .. with data subsets of data that dont follow the rules
These headaches there for everybody ..

The logic has to be re written is one way
Others have other ways of dealing with the issue

They pull the bad data aside into another table ...
And deal with it on a case by case basis

:slight_smile: :slight_smile:
You are not the only one !!!!

5

Thanks. :slightly_smiling_face: