For accounting reasons, our parent company needs to know the amount of sales made to employees of our store. I thought I had this figured out with a SQL query (see below) but it turns out the numbers from my query are very different from the numbers returned to the Sales > Top Customers report.
Is there a way to run the Top Customers report, but adding a condition (not in the filter option list) to include only rows where Customer.Employee = 1?
I appreciate any tips.
Here's my SQL, maybe there's something easily seen that I missed...
SELECT c.LastName + ', ' + c.FirstName AS associate, SUM (e.FullPrice) AS regularprice, SUM(e.Price) AS soldprice, SUM (e.FullPrice) - SUM(e.Price) AS discount FROM Customer AS c INNER JOIN [Transaction] AS t ON c.ID = t.CustomerID INNER JOIN TransactionEntry AS e ON t.TransactionNumber e.TransactionNumber WHERE (t.Time BETWEEN '1/1/2009' AND '1/31/2009 23:59:59') AND (c.Employee = 1) GROUP BY c.LastName + ', ' + c.FirstName WITH ROLLUP