tomek tomek (10/16/2013)
Well....I kind of have a solution, but it's messy (especially that I acutally have more tables like these to merge, and obviuosly there come joins with other tables).
Maybe someone could suggest something better?
;WITH cte as(
select DISTINCT t.[Object], t.date FROM table1 t
union
select DISTINCT t.[Object], t.date FROM table2 t
)
select * FROM cte
outer APPLY (SELECT TOP 1 status1 FROM table1 tx
WHERE tx.date<=cte.date AND tx.[Object]=cte.[OBJECT]
ORDER BY tx.date DESC) t1
outer APPLY (SELECT TOP 1 status2 FROM table2 tx
WHERE tx.date<=cte.date and tx.[Object]=cte.[OBJECT]
ORDER BY tx.date DESC) t2
First, I think this is an excellent solution and, particularly, an excellent use of OUTER APPLY. Here are my suggestions:
1) You can lose the DISTINCT operators in your cte; it causes an extra distinct sort in your query plan which adds cost but does not change your result set. You are using the UNION set operator which will guarantee unique values.
2) I would avoid using reserved SQL words such as 'date' and 'object' in your column names.
3) If you can add an clustered index to that table you would see notable performance improvements, especially if you will be doing a lot of joins.
-- Itzik Ben-Gan 2001