We are designing a solution that will interrogate transactional data in order to return matching based on the first set of rules that returns a result, where each select is wider than the previous. The goal is to get the narrowest search that returns a result.

Here is an illustration. Consider a scenario with vehicle data where we have been given the following three values:

1. VIN of a vehicle
2. Registration No of a vehicle
3. City where the vehicle is in

We then want to interrogate transactional data to return matches against that value. An important distinction is that it can be a one to many match since the transactional data has additional relationships which mean that duplicate vehicles may reside in the transactional data. In a one to many match, we require all the matched IDs from the matching process

The order that we want to interrogate the data is as follows (as an illustration):

From the transactional data:

• Return all vehicles that have a matching VIN and City

If none exist:

• Return all vehicles that have a matching VIN

If none exist:

• Return all vehicles that have a matching Rego and City

This example has only three widening conditions, however, in our application it will actually be around 8.

This could be done in a sequential manner, as illustrated below, however that doesn't feel correct, there is probably a better set based operation to do it, but I can't think how to create it.

Here is the way it could be done sequentially:

-- assume these values are passed in
declare @vin varchar(50) = 'abcdefg'
declare @rego varchar(50) = '123456'
declare @city = 'Sydney'

-- create a table to hold the results
declare @ids table(id int)

-- try the narrowest search: matching VIN and City
insert into @ids(id)
select id from TransactionalData where Vin = @vin and City = @city

if ((select count(*) from @ids ) = 0)
begin
  -- try the next narrowest search: matching VIN
  insert into @ids(id)
  select id from TransactionalData where Vin = @vin

  if ((select count(*) from @ids ) = 0)
  begin
    -- try the next narrowest search: matching Rego and City
    insert into @ids(id)
    select id from trasnactionaldata where Rego = @rego and City = @city
  end
end

-- at this point, @ids will contain the ids from the narrowest search

It would work, but it doesn't seem like the correct approach. Can anyone suggest how to do it in a single set based opperation?

Many thanks and best regards

CodePudding user response：

Maybe something like this. You specify the required condition in the WHERE clause and then do a match count on the result and rank it

select *,
       match_cnt = case when td.vin  = @vin then 1 else 0 end
                   case when td.rego = @rego then 1 else 0 end
                   case when td.city = @city then 1 else 0 end
from   TransactionalData td
where  td.vin  = @vin
or     td.rego = @rego
or     td.city = @city
order by match_cnt desc

CodePudding user response：

If I understand correctly, we can use a column to represent priority which means you expect insert order by CASE WHEN expression

;WITH CTE AS (
    SELECT id,priority
    FROM (
        SELECT id, CASE WHEN Vin = @vin and City = @city THEN 3 
                        WHEN Vin = @vin THEN 2 ELSE -1 END priority 
        FROM TransactionalData where (Vin = @vin and City = @city) OR (Vin = @vin)
        UNION ALL
        SELECT id,1 FROM trasnactionaldata WHERE Rego = @rego and City = @city
    ) t1 
    WHERE priority > 0
)
INSERT INTO @ids(id)
SELECT id
FROM CTE c 
WHERE priority = (SELECT MAX(priority) FROM CTE)