Pixi API documentation

Unofficial · by koempf24.de

Postman collection
Customers Write

pixiAddressesFindDuplicates

Finds probable duplicate customers by fuzzy address matching in batches of 2500 addresses and stores scored customer pairs in Addresses_Duplicates.

Batch job that detects duplicate customers by comparing their addresses. It returns no result set. It writes its findings to the table Addresses_Duplicates.

Time window

The procedure only works between 21:00 and 06:59 server time. If the current hour is between 7 and 20, it prints a message and ends without doing anything. The only exception is a session whose CONTEXT_INFO is 0x00999, which is used for unit tests.

Steps

  1. Creates the helper tables tmp.Addresses_Process and tmp.Addresses_Simple if they do not exist yet.

  2. If ReCreateAll = 1, empties Addresses_Duplicates, tmp.Addresses_Simple and tmp.Addresses_Process with TRUNCATE. All existing matches are deleted.

  3. Selects up to 2500 customer main addresses (Customers.AddrRef) as candidates, newest address key first. A candidate must meet all of these conditions:

    • It is not yet in tmp.Addresses_Simple.
    • Its key is above the highest or below the lowest key already processed.
    • The customer has no order with an order header attribute of type 3 (archived orders).

  4. Removes titles from the names: Frau, Herr, Prof., Dipl., Ing., Mag., med., Dr.. If the last name is empty, the field Name is split at the first space into first name and last name.

  5. Stores a simplified copy of each address in tmp.Addresses_Simple:

    • the first 3 characters of SOUNDEX for street, city, last name, first name and CareOf (company);
    • the cross sum of the ZIP code (fCrossSum);
    • address, customer and shop ID.

  6. Marks up to 2500 of these addresses as processed in tmp.Addresses_Process, newest first.

  7. Compares the newly processed addresses with all simplified addresses. A pair counts as a match if all of these conditions hold:

    • different customer;
    • same shop;
    • same soundex code for city, street and CareOf;
    • same ZIP cross sum;
    • same soundex codes for first and last name, also when first and last name are swapped.

    A customer is skipped if it already appears as CustRef1 or CustRef2 in Addresses_Duplicates.

  8. Gives each pair a score (MatchLevel). The score is the sum of these parts:

    • name: 40 full, 30 swapped, 20 soundex match, 10 weak match or different first name;
    • street: 20 full, 10 partial, 5 weak;
    • city: 20 full, 10 partial, 5 weak;
    • ZIP: 20 full, 10 same cross sum;
    • company (CareOf): 0 full, -10 partial, -15 weak.

    MatchText lists the non-full match flags, for example STREET_PARTIAL_MATCH;, cut to 100 characters.

  9. Inserts each new pair once. CustRef1/AddrRef1 is the newly processed customer and CustRef2/AddrRef2 is the matched customer. Pairs that already exist in either direction are skipped.

  10. If tmp.Addresses_Simple still contains addresses that are not in tmp.Addresses_Process, the procedure queues EXEC pipiAddressesFindDuplicates in ExecQueries. In this way the run continues in the next batch.

Notes

  • No result set: the call returns no rows. Informational output comes only via PRINT, for example RUN BEFORE 07:00 or AFTER 20:00 during the day. These messages may appear as SqlMessage entries, depending on the client. A daytime call therefore looks successful but does nothing.
  • ReCreateAll in the daytime: the truncation for ReCreateAll = 1 happens only after the time check. During the day the parameter has no effect.
  • Continuation: the follow-up run is queued in ExecQueries. How and when that queue is executed is not part of this code. A comment speaks of "1 minute" and of runs until 7 AM. The batch size is 2500 addresses, although a comment mentions 100.
  • Permanent helper tables: the procedure creates and fills the permanent tables tmp.Addresses_Simple and tmp.Addresses_Process to remember its progress between runs.
  • No transaction: all reads use NOLOCK and there is no explicit transaction.
  • Pitfalls visible in the code:
    • The partial checks for street and city compare an address with itself. Whenever street or city do not match exactly, the result is therefore always STREET_PARTIAL_MATCH; or CITY_PARTIAL_MATCH;. The _LOW variants never occur.
    • Pairs are numbered per sum of the two customer IDs (s.CustRef + c.CustRef). Different pairs whose IDs add up to the same value can therefore be dropped, because only the first one is kept.
    • If the names match only in swapped soundex form but not exactly swapped, the name category is empty. MatchLevel and MatchText then become NULL.
  • Runtime: matching is CPU-intensive. A code comment warns not to increase the batch size.

Tables used

Customers · Read Addresses · Read OrderHeader · Read OrderHeaderAttribute · Read Addresses_Duplicates · Write Addresses_Simple · Write Addresses_Process · Write ExecQueries · Write
SQL object:
dbo.pipiAddressesFindDuplicates
Updated at 2026-10-30