This article details a SQL report that is used to generate a list of non-house accounts without activity in a certain time period. The salesperson for those accounts can be reassigned to a different employee (or back to the house account) if desired.

This approach uses a SQL report to generate a list of active clients and prospects with no activity on them in a certain number of months. House accounts are excluded from the list. Although this query looks for no activity at all you could modify the query to only look for specific activity types, such as calls, emails, etc. If you are familiar with SQL you can make these changes yourself or consult with our implementation department (hourly fee based) to have them modify it to fit your needs.

The SQL report can be used to preview the changes before they are made.

A parameter allows the user to specify whether the changes are made or not. This allows the changes that would be made to be previewed before they are made.

WARNING: The changes implemented here are NOT reversible. Backup your data before running. Also, since you are modifying the database directly with SQL, it is easy to get something wrong. This should only be used by someone comfortable with SQL Reports.

  1. Create a SQL Report
  2. Copy the SQL below in as the SQL to run.
DECLARE @Months tinyint = ;
DECLARE @TargetEmployeeID INT =  ;
DECLARE @MakeChanges VARCHAR(5) = ;
DECLARE @LastActivityDate datetime = dateadd(MONTH, -@Months, getdate());
DECLARE @NewSalesperson VARCHAR(128) = (SELECT FirstName + ' ' + LastName FROM Employee WHERE ID = @TargetEmployeeID);
SELECT
    A.CompanyName,
    MAX(J.CompletedDateTime) AS LastCompletedDate,
    SP.FirstName + ' '+ SP.LastName AS [OLD Salesperson],
    @NewSalesperson AS [NEW Salesperson],
    A.SalesPersonID1 AS SalespersonID,
    A.ID AS AccountID
FROM Account A WITH (NOLOCK)
JOIN Journal J WITH (NOLOCK) ON A.ID = J.AccountID
LEFT JOIN Employee SP WITH (NOLOCK) ON SP.ID = A.SalesPersonID1
WHERE A.IsActive = 1 AND (A.IsProspect = 1 OR A.IsClient = 1)
AND A.SalesPersonID1 NOT IN (10, @TargetEmployeeID) -- House Account
GROUP BY A.ID, A.CompanyName, SP.FirstName, SP.LastName, A.SalesPersonID1
HAVING MAX(J.CompletedDateTime) < @LastActivityDate
ORDER BY LastCompletedDate DESC
;
IF (@MakeChanges = 'YES')
BEGIN
  UPDATE Account
  SET SeqID = SeqID + 1, SalespersonID1 = @NewSalesperson
  WHERE ID IN (
    SELECT top 2 A.ID
    FROM Account A WITH (NOLOCK)
    JOIN Journal J WITH (NOLOCK) ON A.ID = J.AccountID
    LEFT JOIN Employee SP WITH (NOLOCK) ON SP.ID = A.SalesPersonID1
    WHERE A.IsActive = 1 AND (A.IsProspect = 1 OR A.IsClient = 1)
    AND A.SalesPersonID1 NOT IN (10, @TargetEmployeeID) -- House Account
    GROUP BY A.ID, A.CompanyName, SP.FirstName, SP.LastName, A.SalesPersonID1
    HAVING MAX(J.CompletedDateTime) < @LastActivityDate
  )
END;
  1. Set the Options on the General tab to “Create View and Save”
  2. On the Options Tab, set each of the parameters as follows:
    1. Months of Inactivity
      • Type = Number
      • Default Value = 18
    2. Reassign To
      • Type = Employee
      • Default Value = the employee you want to change these to
    3. YES to make changes
      • Type = String
      • Default Value = NO
  3. Save the report

Contributor: Cyrious Software

Date: 5/2015

Version: Control 5.1+

You could leave a comment if you were logged in.