Concatenate the Records where the Date is Updated (Unique records) - Help?

[dmohanty]

Table A
DOC_NUM	DOC_ITEM_NUM	DATE
100	10	10-Feb
100	20	16-Feb
101	30	18-Feb
102	20	21-Feb
102	50	28-Feb
Table B
DOC_NUM	DOC_ITEM_NUM	DATE
100	30	12-Mar
100	20	19-Mar
103	30	22-Mar
102	50	22-Mar

Hi All,

I have 2 Set of Tables as above. I want to concatenate in Such a way that if there are DOC_NUM and DOC_NUM_ITEM in both tables, then pick the one having latest DATE.

So the Output will look like this below:

DOC_NUM	DOC_ITEM_NUM	DATE
100	10	10-Feb
100	20	19-Mar
100	30	12-Mar
101	30	18-Feb
102	20	21-Feb
102	50	22-Mar
103	30	22-Mar

[Anonymous]

Lot of ways to do this.  But the one attached keeps all the dates and creates a second table that contains the max date.  This way you have the other date in case you need it for some other calculation.