1. Home
  2. Point-Of-Sale Software
  3. Problems with customer totalSales etc.

Problems with customer totalSales etc.

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

Reply to
Wolfgang
Loading thread data ...

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

Reply to
cptsoft

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.