Hello,
since we updated all local customers to global we have the problem that sometimes totalSale, totalVisits an totalSavings is negativ for a customer.
With the SQL Profiler I found the following statement:
exec sp_executesql N'DECLARE @Factor Float SELECT @Factor = @P1 SELECT CustomerID = Customer.ID, Sales = SUM(TransactionEntry.Quantity * TransactionEntry.Price), Savings = SUM(TransactionEntry.Quantity * (TransactionEntry.FullPrice - TransactionEntry.Price)), TransactionCount = COUNT(DISTINCT [Transaction].TransactionNumber), LastVisit = CASE WHEN MAX([Transaction].Time) > Customer.LastVisit THEN MAX([Transaction].Time) ELSE Customer.LastVisit END INTO #Temp FROM Customer WITH(UPDLOCK) JOIN [Transaction] WITH(UPDLOCK) ON Customer.ID = [Transaction].CustomerID JOIN TransactionEntry WITH(UPDLOCK) ON TransactionEntry.TransactionNumber [Transaction].TransactionNumber AND TransactionEntry.StoreID = [Transaction].StoreID WHERE [Transaction].StoreID = @P2 AND [Transaction].TransactionNumber >= @P3 AND Customer.GlobalCustomer = 1 GROUP BY Customer.ID, Customer.LastVisit UPDATE Customer SET Customer.TotalSales = ISNULL(Customer.TotalSales, 0.0) + (@Factor * #Temp.Sales), Customer.TotalSavings = ISNULL(Customer.TotalSavings, 0.0) + (@Factor * #Temp.Savings), Customer.TotalVisits = ISNULL(Customer.TotalVisits, 0.0) + (@Factor * #Temp.TransactionCount), Customer.LastVisit = #Temp.LastVisit, Customer.LastUpdated = GETDATE() FROM Customer WITH(UPDLOCK), #Temp WHERE Customer.ID = #Temp.CustomerID
SELECT @@ROWCOUNT AS RecordsAffected', N'@P1 float,@P2 int,@P3 int',
-1.000000000000000e+000, 18, 8108
As you can see, there is a factor and it is -1. Why is there such a strange factor and where does it come from? I have really no clue.
Best regards
Wolfgang