首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >按月查询calc佣金

问按月查询calc佣金
EN

Stack Overflow用户
提问于 2015-09-03 15:10:17
回答 1查看 1.2K关注 0票数 0

这是一个很长的帖子,我希望这是有意义的,我感谢任何帮助!

我需要建立一个Access数据库,用来计算销售佣金。佣金%在服务第一年期间按月变化,此后一直保持不变。

我一直在努力编写一个查询/子查询来处理这个问题。

按月支付的佣金率需要能够随着时间的推移而变化,以反映业务需求,而且由于数据库保存了历史数据,因此需要对每一次销售适用正确的费率。

我有以下表格:

  • 雇员- EmployeeID,名字,姓,StartDate
  • 销售- EmployeeID,SalesDate,金额
  • 目标- MonthsOfService、CommissionRate、StartDate (即引入速率时)

目标表包含用于MonthsOfService、CommissionRate和StartDate的下列内容

  • 1,0%,2014年1月1日
  • 2,2.5%,2014年1月1日
  • 3,5%,2014年1月1日
  • 4.
  • 5...
  • 12,15%,01/01/2014 (=这一比率适用于随后的所有月份)
  • 1.1%,01/01/2015 (=从这一日期起适用的第一个月的新费率)
  • 2.3%,01/01/2015

我想要做的是创建一个查询来计算销售的员工服务月数,然后根据SalesDate选择与服务月份匹配的所有费率,并选择最大日期小于SalesDate,从而选择适当的佣金率。如果员工的服务月>上个月的录入,则需要使用上个月的录入率--例如,如果定义了12个月,则使用12个月,如果定义了15个月,则使用15个月。

谢谢

EN

回答 1

Stack Overflow用户

发布于 2015-09-03 18:03:06

首先,用Access编写SQL并不是我的强项。所以我使用了三个访问查询来完成这个任务。虽然这可能不是处理这一问题的最有效方法,但希望使用三个单独的查询可以解释我所做的工作背后的方法,而不是将其混合到一个查询中。

查询1:确定每个销售的月数,并将其加入到佣金表中。我们做了我们的第一个过滤器,排除在销售日期后的任何佣金变动。这将创建比我们需要的更多的记录,但我们稍后会过滤它们。

代码语言:javascript
复制
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:现在我们过滤掉记录。仅检索每个销售的最大日期和销售日期之前的佣金记录。

代码语言:javascript
复制
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:现在我们将结果与原始表结合起来,给出员工的销售数据和最适用的佣金数据。

代码语言:javascript
复制
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中执行一个查询,它会是什么样子?

代码语言:javascript
复制
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 
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/32379400

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档