I have two different tables to track location of equipment. The "equipment" table tracks the current location and when it was installed there. If the equipment was previously at a different location, that information is kept in the "locationHistory" table. There is one row per equip_id in the equipment table. There can be 0 or more entries for each equip_id in the locationHistory table.
I want an SQL query that gets the date of the FIRST install_date for each piece of eqipment...
equip_id | current_location | install_date_at_location
123 location1 1/23/2011
equip_id | location | install_date | pickup_date
123 location2 1/1/2011 1/5/2011
123 location3 1/7/2011 1/20/2011
You will want to union the queries that each look at one field, then use a MIN against it. Or you can use the CASE and MIN for the same effect
select e.equip_id, MIN(CASE WHEN h.install_date < e.install_date_at_location THEN h.install_date ELSE e.install_date_at_location END) as first_install_date from equipment e left join locationHistory h on h.equip_id = e.equip_id group by e.equip_id
Well, the critical piece of information is whether the install_at_location_date in equipment can ever be less than what I assume is the historical information in locationHistory. If that's not possible, you can do:
SELECT * FROM locationHistory L INNER JOIN (SELECT equip_id, MIN(install_date) AS firstDate FROM locationHistory) AS firstInstalls F ON L.equip_id = F.equip_id AND L.install_date = F.firstDate
But if you have to worry about both tables, you need to create view that normalizes the tables for you, and then apply the query against the view:
CREATE VIEW normalLocations (equip_id, location, install_date) AS SELECT equip_id, location, install_date_at_location FROM equipment UNION ALL SELECT equip_id, location, install_date FROM equipment; SELECT * FROM normalLocations L INNER JOIN (SELECT equip_id, MIN(install_date) AS firstDate FROM normalLocations) AS firstInstalls F ON L.equip_id = F.equip_id AND L.install_date = F.firstDate
A simple way to do it is:
SELECT U.Equip_ID, MIN(U.Install_Date) FROM (SELECT E.Equip_ID, E.Install_Date_At_Location AS Install_Date FROM Equipment AS E UNION SELECT L.Equip_ID, L.Install_Date FROM LocationHistory AS L ) AS U GROUP BY U.Equip_ID
This could generate a lot of rows from the LocationHistory table, but it isn't clear that it is worth 'optimizing' it by trying to apply a GROUP BY and MIN to the second half of the UNION (because you'd immediately redo the grouping with the result from the information in the equipment table).