SELECT TOP(1) *
FROM (
SELECT 1 AS TableNum, ItemID, EffDt FROM Table1 UNION ALL
SELECT 2 AS TableNum, ItemID, EffDt FROM Table2 UNION ALL
SELECT 3 AS TableNum, OrigItemId as [ItemID], EffDt FROM Table2 UNION ALL
SELECT 4 AS TableNum, ItemID, EffDt FROM Table3
SELECT 5 AS TableNum, OrigItemId as [ItemID], EffDt FROM Table3
) AS d
ORDER BY TableNum, EffDt DESC

quote:

---

Originally posted by midavis

I have a function that returns whether an item is the latest one in the system. I will use CheckItemId as the item id we want to check on.

Table1 stores all items.
Table2 stores a relationship between the revision of an item and when it was done. This table will include the ItemId for the revision that ties back to Table1 as well as the original ItemId (As OrigItemId) this was based from.
Table3 is similar to Table3 but only occurs for certain types of items and stores different types of data.

The logic is this.
1. Does this ItemId exist in Table2 as either the ItemId or OrigItemId? If not look in Table3
2. Does this ItemId exist in Table3 as either the ItemId or OrigItemId? If not then the ItemId is the original Item
3. If the item exists in either Table2 or Table3 we need to grab all occurrences from each table and order them by date desc. We will then grab the latest ItemId from this result. If this ItemId matches the CheckItemId then this is the latest item.

Below is the solution I have currently, but would like to try and use a set based approach. Currently when I do a query I have to call this function for each ItemId to grab the latest Item and a query for 70,000+ rows takes 23 seconds. No more query optimizations need to be done.

As a side note, it would be great to be able to put this in a view and index it because a lot of reports will use this information. Also, I know I can update this query a bit to get rid of the table variable, but I want to focus on how to get this as a set based solution.

Thanks in advance.



DECLARE @CheckItemId int

-- Declare the return variable here
DECLARE @Result bit
DECLARE @OrigItemId int
DECLARE @ItemId int

SET @Result = 0 -- Set the default return value to 0

-- try to find the orig ItemId from Table2
Select @OrigItemId = OrigItemId FROM Table2 Where ItemId = @CheckItemId or OrigItemId = @CheckItemId

-- this does not exist in Table2 so try Table3
if @OrigItemId is null
Select @OrigItemId = OrigItemId FROM Table3 Where ItemId = @CheckItemId or OrigItemId = @CheckItemId

-- this does not exist in either table so this is the original item and the current version
if @OrigItemId IS NULL
set @Result = 1
else
BEGIN

-- create a table to store the items from each table
DECLARE @tmpData TABLE (ItemId int , Dt datetime)

Insert Into @tmpData
SELECT ItemId, EffDt
FROM Table2
WHERE OrigItemId = @OrigItemId

Insert Into @tmpData
SELECT ItemId, EffDt
FROM Table3
WHERE OrigItemId = @OrigItemId

-- grab the latest item
SELECT TOP 1 @ItemId = ItemId From @tmpData order by Dt DESC

if @ItemId = @CheckItemId
set @Result = 1
END

print @Result

---