Thanks!  The SUM with a CASE statement solve my problem for now and the links to the dynamic process will help long term once I can consume and understand them!

• This reply was modified 3 years, 10 months ago by  jeff.born.

Both the remote and local databases are Azure Remote Instances with the same version.  One of the benefits of using a Managed Instance is that it is supposed to be...

Changing ci to cip doesn't help.

Version is: Microsoft SQL Azure (RTM) - 12.0.2000.8 May 15 2020 00:47:08 Copyright (C) 2019 Microsoft Corporation

XML Query plan is as follows:

<?xml...

Lowell,

I'd also be interested in seeing your solution. My original thought was reduce the calls to a CASS company by not submitting obvious duplicates because every CASS request will...

The Street Suffix list is from USPS, The address list I'm working with, well I'd rather keep the source of that to myself 🙂 Very interested in all options,...

Drew,

I'm not sure how to write the ISNULL version.. This syntactically incorrect version I came up with is:

SELECT a.Address_pk, a.[Address],

STUFF(a.[Address], ss.search_idx, LEN(ss.search_val), ss.PostalServiceStandardSuffixAbbreviation) AS AddressAbbreviated

FROM [Address] a

OUTER APPLY (

SELECT TOP...

Yes there are, they are:

N, S, E, W, NE, NW, SE, SW

Sergiy, The rule is that only the last token should be replaced. So

100 Lake Drive should be 100 Lake DR

100 Lake Shore Drive Road should be 100 Lake...

This works great for the addresses that have a street suffix in them, however I have many addresses like this:

INSERT INTO [dbo].[Address] ([Address]) VALUES ('48 Martin Luther King');

There is no...

Sometimes I just can't see the solutions even when it's right in front of me. Never thought of using a union. or a compound operator.

One question is the output...

That was exactly what I was looking for, and bonus for the explanation, good reading material for this weekend!

Wayne,

Unfortunately the fact that you can currently partition by first and name last is a fluck. Usually the names will be spelt differently which is why the contacts were...

WayneS (8/12/2015)

---

Ordering question: since MatchScore was added, it's going to get the lowest MatchScore. Do you want the highest, or lowest?

For instance:

510471637412394716374977274246TIPTONGOLIAS

51047163747806257379866227750Tipton Golias

510471637412394716374931652750TIPTONGOLIAS

510471637414106357292966227850TIPTON LGOLIAS

Should we get one with 50, or...

Wayne,

Thanks for your help so far, it seems close, but found another way to break it, and haven't been able to tweek it to remove this latest duplicate. I've...

Wayne,

I've updated it a bit to use MatchScore as a first tie breaker, but also noticed that Mohammed Ziauddin is listed twice in the final result which would still leave...