这是一个很长的帖子,我希望这是有意义的,我感谢任何帮助!
我需要建立一个Access数据库,用来计算销售佣金。佣金%在服务第一年期间按月变化,此后一直保持不变。
我一直在努力编写一个查询/子查询来处理这个问题。
按月支付的佣金率需要能够随着时间的推移而变化,以反映业务需求,而且由于数据库保存了历史数据,因此需要对每一次销售适用正确的费率。
我有以下表格:
目标表包含用于MonthsOfService、CommissionRate和StartDate的下列内容
我想要做的是创建一个查询来计算销售的员工服务月数,然后根据SalesDate选择与服务月份匹配的所有费率,并选择最大日期小于SalesDate,从而选择适当的佣金率。如果员工的服务月>上个月的录入,则需要使用上个月的录入率--例如,如果定义了12个月,则使用12个月,如果定义了15个月,则使用15个月。
谢谢
发布于 2015-09-03 18:03:06
首先,用Access编写SQL并不是我的强项。所以我使用了三个访问查询来完成这个任务。虽然这可能不是处理这一问题的最有效方法,但希望使用三个单独的查询可以解释我所做的工作背后的方法,而不是将其混合到一个查询中。
查询1:确定每个销售的月数,并将其加入到佣金表中。我们做了我们的第一个过滤器,排除在销售日期后的任何佣金变动。这将创建比我们需要的更多的记录,但我们稍后会过滤它们。
SELECT Employees.StartDate AS Emp_StartDate, Targets.StartDate AS Tar_StartDate, sales.SalesDate, Employees.EmployeeID, Targets.MonthsOfService
FROM Targets, sales INNER JOIN Employees ON sales.EmployeeID = Employees.EmployeeID
WHERE (((Targets.StartDate)<=[sales].[salesDate]) AND ((Targets.MonthsOfService)=IIf(DateDiff("m",[Employees].[StartDate],[sales].[SalesDate])>12,12,DateDiff("m",[Employees].[StartDate],[sales].[SalesDate]))))查询2:现在我们过滤掉记录。仅检索每个销售的最大日期和销售日期之前的佣金记录。
SELECT Query1.Emp_StartDate, Max(Query1.Tar_StartDate) AS MaxOfTar_StartDate, Query1.SalesDate, Query1.EmployeeID, Query1.MonthsOfService
FROM Query1
GROUP BY Query1.Emp_StartDate, Query1.SalesDate, Query1.EmployeeID, Query1.MonthsOfService;查询3:现在我们将结果与原始表结合起来,给出员工的销售数据和最适用的佣金数据。
SELECT Employees.FirstName, Employees.LastName, Employees.StartDate, sales.SalesDate, sales.amount, Targets.CommissionRate
FROM ((Query2INNER JOIN Employees ON Query2.EmployeeID = Employees.EmployeeID) INNER JOIN sales ON (Query2.SalesDate = sales.SalesDate) AND (Employees.EmployeeID = sales.EmployeeID)) INNER JOIN Targets ON (Query2.MonthsOfService = Targets.MonthsOfService) AND (Query2.MaxOfTar_StartDate = Targets.StartDate);如果要用SQL编写它,它将类似于下面的查询。这在Access中不起作用,但我只想了解一下,如果在SQL中执行一个查询,它会是什么样子?
SELECT Employees.FirstName,
Employees.LastName,
Employees.StartDate,
Sales.SalesDate,
Sales.Amount,
Targets.CommissionRate
FROM Employees, Targets
INNER JOIN Sales
ON Sales.EmployeeID = Employees.EmployeeID
INNER JOIN (SELECT CommissionQuery.Emp_StartDate,
Max(CommissionQuery.Tar_StartDate) MaxOfTar_StartDate,
CommissionQuery.SalesDate,
CommissionQuery.EmployeeID,
CommissionQuery.MonthsOfService
FROM (SELECT Employees.StartDate Emp_StartDate,
Targets.StartDate Tar_StartDate,
sales.SalesDate,
Employees.EmployeeID,
Targets.MonthsOfService
FROM Targets, sales
INNER JOIN Employees
ON sales.EmployeeID = Employees.EmployeeID
WHERE Targets.StartDate <= [sales].[salesDate]
AND Targets.MonthsOfService)=IIf(DateDiff("m",[Employees].[StartDate],[sales].[SalesDate])>12,12,DateDiff("m",[Employees].[StartDate],[sales].[SalesDate]
) CommissionQuery
GROUP BY CommissionQuery.Emp_StartDate, CommissionQuery.SalesDate, CommissionQuery.EmployeeID, CommissionQuery.MonthsOfService;
) MaxCommission
ON MaxCommission.SalesDate = Sales.SalesDate
AND MaxCommission.MaxofTar_StartDate = Targets.StartDate https://stackoverflow.com/questions/32379400
复制相似问题