The Smart Search stored procedure pattern attempts to mimic a "Google-like" search in TSQL. For example, searching for Loans for "BofA MA Defaulted" should find all Loans being serviced by BofA in the state of MA and a status of Defaulted.
To achieve this result, we break the query down into three distinct sections:
- Create a list of terms from the search string
- Build a result set based on facts (and, in some cases, dimensions)
- Adjust weight of each result in the the result set by matching dimensions
Creating a List of Terms
The user-defined function dbo.SmartSearchTerms() will accept a search string, and return a table of "Terms". Basically, each word is broken up, check for data type, and assessed to see if it's a common lookup table data point. Thus, "BofA MA Defaulted" would:
- Be broken into three rows: BofA, MA, and Defaulted
- Each row would be checked against the Organization table, and if found, flagged as an Organization with an ID.
- Each row would be checked against the State table, and if found, flagged as a State.
- Each row would be checked against the ObjectStatus table, and if found flagged as a Status.
Thus, dbo.SmartSearchTerms() will return something like this:
| Row | Term | Attribute | Value | ID | SearchDate |
| 1 | BofA | Organization | BofA | 73 | {NULL} |
| 2 | MA | State | MA | {NULL} | {NULL} |
| 3 | Defaulted | Status | Defaulted | {NULL} | {NULL} |
In some cases, we cannot correct identify a term based on the string alone. In those cases, you are welcome to use a bit of JSON: {attribute}:{term}. For example, assume that you have an Organization called MA. Passing in "BofA state:MA Defaulted" would ensure that MA is interpreted as a state. Moreover, if you wanted only Loans where BofA is an investor, you can do this: "investor:BofA state:MA Defaulted".
For addresses and some other terms, the term may comprise multiple words. In that case, the term should be quoted. To find Wells Fargo files, you can use '"Wells Fargo" MA Defaulted". To search for an address, you can use '"123 Main%" status:Foreclosure'
Build a Result Set
Given a set of terms, we next build a result table (in a table variable) based on the terms. For a Valuation smart search, it would include statements along these lines:
-- Match Valuation.Valuation to a term
INSERT INTO @SortTable (IndexWeight, IndexMatch, TermRowNumber, ValuationID, ClientID, ...)
SELECT 10, 'Valuation.Valuation', Terms.RowNumber, Valuation.ValuationID, Valuation.ClientID, ...
FROM @Terms AS Terms
INNER JOIN Valuation
ON Valuation.Valuation LIKE Terms.Value + @Wildcard
LEFT OUTER JOIN Property
ON Property.PropertyID = Valuation.PropertyID
WHERE
ISNULL(Terms.Attribute, 'Valuation') = 'Valuation'
AND LEN(Terms.Value) >= 4
Note that matching a term on Valuation.Valuation is ranked with an Index Weight of 10. This is very likely to be what the user is looking for. Later in the stored procedure, we have less weighty terms:
-- Match Valuation.OpenedDate to a term
INSERT INTO @SortTable (IndexWeight, IndexMatch, ValuationID, ClientID, ...)
SELECT 4, 'OpenedDate: ' + CONVERT(nvarchar, @StartDate, 101) + ' - ' + CONVERT(nvarchar, @EndDate, 101), Valuation.ValuationID, Valuation.ClientID...
FROM Valuation
LEFT OUTER JOIN Property
ON Property.PropertyID = Valuation.PropertyID
WHERE
Valuation.OpenedDate BETWEEN @StartDate AND @EndDate
You may add as many search expressions to the Smart Search stored procedure as you see fit. Typically the expressions you search for in building the result set should be facts, or reasonably narrowing dimensions. Facts include things that you would never "group by", such as Valuation.Valuation, Contact.USSSN, Loan.Loan, Property.Address, etc. Dimensions are things you might group by, such as State, Status, Client, Servicer, etc. If a given group comprises 50% or more of the records in the table, it's a bad idea to make that particular column part of building a result set; make it part of adjusting the weight of a result set instead. For example, if ABC BPO company is the Property.AssignedOrganizationID for every Property in the system, adding Property.AssignedOrganizationID as a filtering term offers no advantage.
Adjusting a Result Set with Dimensions
Once a basic result set has been built, the Index Weight can be adjusted based on Dimensions. For example, the following will "tweak" the result set order by increasing the relevance of results that match an Type attribute:
UPDATE @SortTable
SET IndexWeight = IndexWeight + 1
FROM @SortTable
WHERE
ValuationType IN
(
SELECT Value
FROM @Terms
WHERE Attribute = 'Type'
)
Advanced Searches with Smart Search
The ability to do a real "Google-like" search from a text box is tricky for complex situations. However, an "advanced search" UI can be created to allow a user to build complicated search string without learning the attribute / quoting patterns required for complex queries. In short, you can present form elements for fields such as:
- Servicer,
- Address,
- State,
- Status, and
- ValuationType
and use javascript to build the search string like this: 'ServicerID:127 Address:"123 Main%" State:% Status:"Awaiting Assignment" ValuationType:%'.