Differences

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

Link to this comparison view

control_sql_-_change_prospect_to_client [2019/01/27 11:28] (current)
Line 1: Line 1:
 +======  ======
 +
 +
 +
 +===== Explanation of SQL =====
 +
 +
 +
 +When customers import contacts from another program that have not placed an order in Control, they are considered a "Prospect" in the system. There is no mechanism in Control to change these manually to be "Clients".
 +
 +
 +
 +This SQL will set **ALL** of your prospects imported on a specific date to a client status.  You will need to adjust the date to correspond to the date of the import.
 +
 +
 +
 +To update those records so they have a "Client" status, you can run the below query. 
 +
 +
 +
 +===== Risk of Data Corruption if Run Improperly =====
 +
 +**High**. This query modifies the Account table.  Running it incorrectly may result in prospects being miscoded as customers. All data modifications done through direct SQL **are permanent and non-reversable**.
 +
 +
 +
 +===== SQL =====
 +
 +
 +
 +<code sql>
 +Declare @ImportDate date;
 +-- adjust this to the specific import date (m/d/yyyy)
 +Set @ImportDate = '8/1/2006';
 +UPDATE Account
 +SET ISClient=1, IsProspect=0
 +WHERE id > 1000 -- eliminate system accounts
 +  and DateCreated = @ImportDate
 +</code>
 +
 +
 +
 +===== Version Information =====
 +  * Entered : 8/15/2009
 +  * Version : 2.5+
 +
 +
 +