Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision 		Previous revision
sms_query_to_rebuild_contacts_from_order_information [2019/01/27 11:31]
127.0.0.1 external edit 		sms_query_to_rebuild_contacts_from_order_information [2021/11/17 17:26] (current)
admin [Explanation of SQL]
Line 1: Line 1:
 ======  ====== ======  ======
- 
- 
  
 ===== Explanation of SQL ===== ===== Explanation of SQL =====
  
- +This SQL will insert any missing contacts in the "Contact Database" using information found in the Order and Estimate tables.
- +
-This SQL will rebuild the "Contact Database" using information found in the Order and Estimate tables.  It replaces the other information that is there. +
  
  
Line 14: Line 9:
  
 **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**. **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**.
 +===== SQL =====
  
 +<code sql>
 +//Insert Missing Contacts from Orders
 +SELECT ContactID, MAX(OrderID) AS OrderID
 +FROM "Order Database"
 +WHERE ContactID IS NOT NULL
 +      AND OrderDate> '3/20/2020' //Remove this filter if you do not have any backup of the contact database to use as your basis
 +      AND ContactID 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,
 +       '' as ModifiedByComputer,
 +       '11/17/2021 4:00 PM' as ModifiedDate
 +FROM "Order Database"
 +WHERE OrderID IN (SELECT OrderID FROM ContactList)
 +ORDER BY ContactID
 +; LatestOrderContacts FALSE
  
-===== SQL =====+INSERT INTO "Contact Database" 
 +SELECT * FROM LatestOrderContacts 
 +;
  
 +//Insert Missing Contacts from Estimates
 +SELECT ContactID, MAX(OrderID) AS OrderID
 +FROM "Estimate Database"
 +WHERE ContactID IS NOT NULL
 +      AND EstimateDate> '3/20/2020'  //Remove this filter if you do not have any backup of the contact database to use as your basis
 +      AND ContactID 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,
 +       '' as ModifiedByComputer,
 +       '11/17/2021 4:00 PM' as ModifiedDate
 +FROM "Estimate Database"
 +WHERE OrderID IN (SELECT OrderID FROM ContactList)
 +ORDER BY ContactID
 +; LatestOrderContacts FALSE
  
-<code sql> +INSERT INTO "Contact Database" 
-select ContactID, max(OrderID) as OrderID +SELECT * FROM LatestOrderContacts
-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"+
 </code> </code>
- 
- 
  
 ===== Version Information ===== ===== Version Information =====
-  * Entered : 03/2010 
-  * Version : 8.6+ 
- 
  
 +  * Modified: 11/2021
 +  * Version : 8.91