Recast purchase history (from merged accounts with HQID=0)

Here are the instructions from MS Dynamics support for recalculating purchase history.

Notes:

1. Perform steps below on your HQ db after-hours (stores not open).
2. Make backups of HQ db and store db’s before proceeding.
3. The Select statements list accounts that will be affected so you can preview the changes. The Update statement makes the changes. These queries could result in a large number of customers updating on the next 401.
4. After the scripts below have been processed, run a Worksheet 401 to synchronize HQ with each store.

Run scripts using HQ Administrator, File, Connect, enter db password, then Query, New. Cut and paste each select or update statement into the query box, then F5 or green arrow icon to run. Repeat cut, paste, and run with each statement in the order indicated. 

A. TotalVisits/TotalSales

Select Statement (this will select the customers that will be affected):

SELECT Accountnumber,Lastname,Firstname, Customer.TotalSales as CurrentTotalSales, Customer.TotalVisits as CurrentTotalVisits, A.TotalSales as ComputedTotalSales,A.TotalVisits as ComputedTotalVisits FROM Customer LEFT JOIN (SELECT CustomerID, COUNT([Transaction].TransactionNumber) AS TotalVisits, SUM(Total-Salestax) AS TotalSales FROM [Transaction] GROUP BY CustomerID) A ON Customer.ID = A.CustomerID WHERE Customer.TotalSales <> A.TotalSales or customer.totalvisits <> A.TotalVisits

Update Statement (this will correct the TotalVisits/TotalSales):

Update Customer SET Customer.TotalSales = A.TotalSales, Customer.TotalVisits = A.TotalVisits FROM Customer LEFT JOIN (SELECT CustomerID, COUNT([Transaction].TransactionNumber) AS TotalVisits, SUM(Total-Salestax) AS TotalSales FROM [Transaction] GROUP BY CustomerID) A ON Customer.ID = A.CustomerID WHERE Customer.TotalSales <> A.TotalSales or customer.totalvisits <> A.TotalVisits

B. TotalSavings

Select statement:

SELECT Accountnumber,Lastname,Firstname, Customer.TotalSavings, A.TotalSavings FROM Customer LEFT JOIN (SELECT CustomerID, SUM((FullPrice-Price)*Quantity) AS TotalSavings FROM TransactionEntry LEFT JOIN [Transaction] ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber AND TransactionEntry.StoreID = [Transaction].StoreID GROUP BY CustomerID) A ON Customer.ID = A.CustomerID WHERE Customer.TotalSavings <> A.TotalSavings

Update Statement

UPDATE Customer SET Customer.TotalSavings = A.TotalSavings FROM Customer LEFT JOIN (SELECT CustomerID, SUM((FullPrice-Price)*Quantity) AS TotalSavings FROM TransactionEntry LEFT JOIN [Transaction] ON TransactionEntry.TransactionNumber = [Transaction].TransactionNumber AND TransactionEntry.StoreID = [Transaction].StoreID GROUP BY CustomerID) A ON Customer.ID = A.CustomerID WHERE Customer.TotalSavings <> A.TotalSavings

C. Last Visit

Select statement:

SELECT Accountnumber,Lastname,Firstname, LastVisit as CurrentLastVisit, a.maxtime as ComputedLastVisit FROM Customer LEFT JOIN (select customerid, max([Transaction].Time) as MaxTime FROM [Transaction] GROUP BY CustomerID) a on a.customerid = customer.id where Customer.LastVisit <> a.maxtime

Update statement:

UPDATE Customer SET Customer.LastVisit = a.maxtime FROM Customer LEFT JOIN (select customerid, max([Transaction].Time) as MaxTime FROM [Transaction] GROUP BY CustomerID) a on a.customerid = customer.id where Customer.LastVisit <> a.maxtime 

After the steps above complete, run a Worksheet 401 to synchronize HQ with each store.