查询以获取员工佣金的总和

问题描述：

查询以获取员工的总佣金，并更新员工表中的 totalCommission 列.

Query to get total commissions for an employee, and update their totalCommission column in the employee table.

此查询每隔几天运行一次(批处理).

This query is run every few days (batch).

规则:1. 员工每天最多只能获得 100 美元的佣金，如果他们获得的佣金超过 100 美元，则只会设置为 100 美元.

The rules: 1. an employee can only get a maximum of $100/day of commision, if they get more than $100 it just gets set to $100.

表格:

Employee 
  (employeeID INT PK, totalCommissions INT, username, ...)

Sale 
  (saleID INT PK, employeeID INT FK, saleTotal, commission, created DATETIME)

使用 SQL Server 2005.

因此，此查询必须按我假设的日期进行分组，如果当天的总和 > 100，则使用 case 语句将每日佣金设置为 100 美元，然后将所有天的总 SUM 设置为 Employee.总佣金列.

So this query will have to group by day I presume, and use a case statement to set the daily commision to $100 if the sum is > 100 for that day, and then set the total SUM for all days to the Employee.TotalCommission column.

答

假设您使用somedate-goes-here"的值限制某处的日期:

assuming you are limiting the dates somewhere using value of "somedate-goes-here":

update employee set totalcommissions = totalc
from
(
-------------------------------------
-- sum capped commissions by employee
-------------------------------------
select employeeID, sum(sum_commissions) as totalc from
      (
      ---------------------------------------
      -- make sure sum is capped if necessary
      ---------------------------------------
              select employeeID
              , case when sum_of_c > 100 then 100 else sum_of_c as sum_commisions
              from 
              (
              -----------------------------------------------
              -- get sum of  commissions per day per employee
              -----------------------------------------------
              select employeeID, sum(commission) as sum_of_c from sale
              where created > "somedate-goes-here"
              group by employeeID, day(created)
              ) as x
      ) as c
  group by employeeID
) y 
inner join employee on employee.employeeID = y.employeeID

查询以获取员工佣金的总和

相关推荐