Combining columns from 3 tables and removing duplicates by date

[maydie]

I have 3 tables with the following relevant fields:

ItemsFrmManufacturer
Date......SerialNum.....Description.....Status
01012010..000001..... Item1..... IN
01022010..000002..... Item1..... IN

ItemsShipped
Date......SerialNum.....Description.....Status
06012010..000001..... Item1..... OUT
06012010..000003..... Item2..... OUT
07152010..000001..... Item1..... OUT

ItemsFromCustomerRepair
Date......SerialNum.....Description.....Status
05012010..00004..... Item3..... IN
06302010..00001..... Item1..... IN

I need to make a query or table takes the data from teh three tables and combines them into one table with the most recent record for each serial number.

CurrentStatus
Date......SerialNum.....Description.....Status
07152010..000001..... Item1..... OUT
01022010..000002..... Item1..... IN
06012010..000003..... Item2..... OUT
05012010..000004..... Item3..... IN

Any help with a sql statement to do this would be greatly appreciated.

[NeoPa]

Hi. Welcome to Bytes!

I'm sorry to say that I think your data structure is all wrong (See Normalisation and Table structures). You should have a single table with an extra field which indicates which situation they reflect. That way, none of this is a problem. The data is already there just as you need it.

This is not simply about this problem either. I can guarantee you that you will encounter further troublesome issues if you continue with your current design. Don't say you haven't been warned.

That all said. Your design is fundamentally your concern, and the specific answer to your question would be :

SELECT 'Manufacturer' AS Source
     , *
FROM   [ItemsFrmManufacturer]
 
UNION ALL
SELECT 'Shipped' AS Source
     , *
FROM   [ItemsShipped]
 
UNION ALL
SELECT 'Repair' AS Source
     , *
FROM   [ItemsFromCustomerRepair]