Update column of one table with last calculated value from other table

[CarrahaG]

Hi

I would like to update the column of one table with the last calculated value of two columns from another table.

The tables are defined as follows:

Table #1: IM_ITEM
-----------------
ITEM_NO VARCHAR(20)
LSTFACTOR DECIMAL(15,4)


Table #2: PO_RECVR_HIST_LIN
---------------------------
ITEM_NO VARCHAR(20)
RECVR_NO VARCHAR(10)
COST DECIMAL(15,4)
RECVD_COST DECIMAL(15,4)


I run the following sql statement to display the contents:

select
RECVR_NO,
ITEM_NO,
COST,
RECVD_COST,
COST/RECVD_COST as LSTFACTOR
from PO_RECVR_HIST_LIN
where
(RECVD_COST > 0) and (ITEM_NO = 'P201F056')


RECVR_NO ITEM_NO COST RECVD_COST LSTFACTOR
-------- -------- ------- ---------- ---------
103 P201F056 429.28 199 2.1572
138 P201F056 427.65 189 2.2627
76 P201F056 439.37 199 2.2079
88 P201F056 425.835 199 2.1399


Now I run the following statement to update table #1 with the last calculated value of LSTFACTOR?

update A
set
PROF_NO_5 = B.LSTFACTOR
from
IM_ITEM A inner join

(select
top 100 percent
ITEM_NO,
COST/RECVD_COST as LSTFACTOR
from PO_RECVR_HIST_LIN
where
RECVD_COST > 0
order by CONVERT(INT, RECVR_NO) ASC) B
on A.ITEM_NO = B.ITEM_NO


It expect it to insert 2.2627 in the LSTFACTOR column of table #1 but instead it inserts 2.1572 which is the first calculated value.

What am I doing wrong?

Regards,
Georges

 

[BitZero]

Try this:

update a
set 
prof_no_5 = b.lstfactor
from 
im_item a 
inner join
(
select c.item_no, c.cost/c.recvd_cost as lstfactor
from po_recvr_hist_lin as c
 inner join
(
select item_no, max( convert(int, recvr_no)) as max_recvr
 from po_recvr_hist_lin 
where recvd_cost>0 
group by item_no
) as d on  c.item_no=d.item_no and  convert(int,c.recvr_no)=d.max_recvr
) b
on a.item_no = b.item_no

The innermost sub-query does a group by to find the max recvr_no for a given item_no. Then the next level out finds the lstfactor for that item_no and max recvr_no.

 

[CarrahaG]

Hi BitZero

I'm actually not interested in getting the max recr_no. I only need the last factor which is the factor calculated for the last receiver number.

The reason I use the formula CONVERT(INT, RECVR_NO) is to sort the records by receiver number and, because receiver number is of type varchar, it would place "138" before "76" and "88" which is not what I want.

Also, I had a typo. I wanted to say:

update a
set lstfactor = b.lstfactor
from
im_item a inner join

instead of

update a
set prof_no_5 = b.lstfactor
from
im_item a inner join

Regards,
Georges

 

[BitZero]

Hi CarrahaG

I know you're after the last factor. But to accomplish this, I used a two step process: the innermost query gets the max recvr_no for an Item, and then we do an inner join with an ON clause that matches item with item and recvr_no with max recvr_no, to find the record with the last recvr_no, and that's the record that is used to get the factor.

 

[markros]

Take a look
Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2

PluralSight Learning Library