This SQL will rebuild the “Contact Database” using information found in the Order and Estimate tables. It replaces the other information that is there.

High. Data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable.

SELECT ContactID, MAX(OrderID) AS OrderID
FROM "Order Database"
WHERE ContactID IS NOT NULL
GROUP BY ContactID
ORDER BY ContactID
; ContactList 
SELECT ContactID, 
       StoreID, 
       CustomerID, 
       CAST(OrdererFirstName AS CHAR(20)) AS FirstName,
       CAST(OrdererLastName AS CHAR(20)) AS LastName,
       CAST(OrdererTitle AS CHAR(30)) AS Title,
       CAST(OrdererPhoneNumber AS CHAR(15)) AS PhoneNumber,
       CAST(OrdererPhoneExtention AS CHAR(10)) AS PhoneExtention,
       CAST(OrdererFaxNumber AS CHAR(15)) AS FaxNumber,
       CAST(OrdererEmailAddress AS CHAR(75)) AS EmailAddress,
       CAST(OrdererPAreaCode AS CHAR(25)) AS PAreaCode,
       CAST(OrdererFAreaCode AS CHAR(25)) AS FAreaCode,
       CAST("" AS CHAR(25)) AS P2AreaCode,
       CAST("" AS CHAR(15)) AS P2PhoneNumber,
       CAST(0 AS INTEGER) AS P2PhoneType,
       CAST("" AS CHAR(25)) AS P3AreaCode,
       CAST("" AS CHAR(15)) AS P3PhoneNumber,
       CAST(0 AS INTEGER) AS P3PhoneType,
       TRUE AS PrimaryContact,
       TRUE AS ShippingContact,
       TRUE AS APContact,
       TRUE AS InvoicingContact,
       "" AS AlternateAddress,
       CAST(0 AS INTEGER) AS SortOrder,
       CAST(1 AS INTEGER) AS BirthMonth,
       CAST(1 AS INTEGER) AS BirthDay,
       TRUE AS IsActive
FROM "Order Database"
WHERE OrderID IN (SELECT OrderID FROM ContactList)
ORDER BY ContactID
; LatestOrderContacts FALSE
DELETE FROM "Contact Database"
WHERE ContactID IN (SELECT ContactID FROM ContactList)
;
INSERT INTO "Contact Database"
SELECT * FROM LatestOrderContacts
;
// Now pull what IS LEFT FROM the Estimates
SELECT ContactID, MAX(OrderID) AS OrderID
FROM "Order Database"
WHERE ContactID IS NOT NULL AND ContactID IS NOT IN (SELECT ContactID FROM "Contact Database")
GROUP BY ContactID
ORDER BY ContactID
; ContactList 
SELECT ContactID, 
       StoreID, 
       CustomerID, 
       CAST(OrdererFirstName AS CHAR(20)) AS FirstName,
       CAST(OrdererLastName AS CHAR(20)) AS LastName,
       CAST(OrdererTitle AS CHAR(30)) AS Title,
       CAST(OrdererPhoneNumber AS CHAR(15)) AS PhoneNumber,
       CAST(OrdererPhoneExtention AS CHAR(10)) AS PhoneExtention,
       CAST(OrdererFaxNumber AS CHAR(15)) AS FaxNumber,
       CAST(OrdererEmailAddress AS CHAR(75)) AS EmailAddress,
       CAST(OrdererPAreaCode AS CHAR(25)) AS PAreaCode,
       CAST(OrdererFAreaCode AS CHAR(25)) AS FAreaCode,
       CAST("" AS CHAR(25)) AS P2AreaCode,
       CAST("" AS CHAR(15)) AS P2PhoneNumber,
       CAST(0 AS INTEGER) AS P2PhoneType,
       CAST("" AS CHAR(25)) AS P3AreaCode,
       CAST("" AS CHAR(15)) AS P3PhoneNumber,
       CAST(0 AS INTEGER) AS P3PhoneType,
       TRUE AS PrimaryContact,
       TRUE AS ShippingContact,
       TRUE AS APContact,
       TRUE AS InvoicingContact,
       "" AS AlternateAddress,
       CAST(0 AS INTEGER) AS SortOrder,
       CAST(1 AS INTEGER) AS BirthMonth,
       CAST(1 AS INTEGER) AS BirthDay,
       TRUE AS IsActive
FROM "Order Database"
WHERE OrderID IN (SELECT OrderID FROM ContactList)
ORDER BY ContactID
; LatestEstimateContacts FALSE
INSERT INTO "Contact Database"
SELECT * FROM LatestEstimateContacts
;
UPDATE "Contact Database"
SET PrimaryContact = FALSE
WHERE ContactID NOT IN (SELECT PrimaryContactID FROM "Customer Database")
;
UPDATE "Contact Database"
SET APContact = FALSE
WHERE ContactID NOT IN (SELECT APContactID FROM "Customer Database")
;
SELECT * FROM "Contact Database"

• Entered : 03/2010
• Version : 8.6+