Update with lastest 1

[h0gg1e]

I'm having a problem trying to update a customer master table with the latest broker to be invoiced.

my table structure is:
customer master (cm_mstr)

cm_nbr cm_name cm_cur_brk
AFFAMA0 some customer <blank>

invoice table
inv_nbr cm_nbr brk_nbr inv_date
1 AFFAMA0 PRO5 10/20/2006
2 AFFAMA0 BGS5 10/01/2006
3 AFFAMA0 ZZZZ 10/31/2006

so basically I want to update my customer master with broker ZZZZ because it was the last broker to be invoiced.

this should be easy, but I'm having a brain failure right now ... thanks for any insight.

 

[AlexCuse]

This feels kind of ugly to me, so someone may have a better method, but I think this would do the trick.

update cm_mstr
set cm_cur_brk = (select brkr_nbr from invoice where inv_date = (select max(inv_date) from invoice))

Hope this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[h0gg1e]

that doesn't seem to work ... because I have a lot of different customers being invoiced on the same day all with different brokers. (if that even makes sense)

 

[AlexCuse]

I got it.

So, if you had two brokers on the invoiced table for 10/31/06, how would you handle that? Is there a certain type of broker number that would be preferable?



A wise man once said
"The only thing normal about database guys is their tables".

 

[h0gg1e]

There will never be two different brokers for the same customer on the same day.

but there will be multiple brokers for different customers on the same day ie:

inv_nbr cm_nbr brk_nbr inv_date
1 cust1 brk1 10/31/2006
2 cust2 brk1 10/31/2006
3 cust3 brk3 10/31/2006
4 cust1 brk9 11/01/2006 (diff day)


so in the end I would want
cust1 brk9
cust2 brk1
cust3 brk3

 

[AlexCuse]

Ah, I think I see now. How about this one (please backup your table first to be safe):

update cm_mstr 
set cm_cur_brk = c.brk_num
from cm_mstr inner join 
(select a.brk_num, b.cm_nbr, a.inv_date from invoice a inner join
(select cm_nbr, max(inv_date) as inv_date from invoice group by cm_nbr) b on 
a.cm_nbr = b.cm_nbr AND a.inv_date = b.inv_date) c
on cm_mstr.cm_nbr = c.cm_nbr

Hope it helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[h0gg1e]

that works ... thanks very much, I just couldn't wrap my head around this one this morning.