两个select语句的求和结果

Our employees are paid commission on a weekly basis and because of a bonus structure i have to calculate two separate weeks of pay and then add the two together.

我们的员工每周都会获得佣金，由于奖金结构，我必须计算两个单独的工资周，然后将两者加在一起。

I have the following SQL statement which gets the two separate weeks results

我有以下SQL语句，它获得两个单独的周结果

    SELECT  PerceptionistID, SSNLastFour, CommissionPay,
        PTOPay, HolidayPay, Overtime, TotalPay
    FROM [dbo].fnCalculateCommissionForWeekOf(@MondayOfCurrentWeek)

    UNION

    -- Need to get the following week's data and sum the two together
    SELECT  PerceptionistID, SSNLastFour, CommissionPay,
        PTOPay, HolidayPay, Overtime, TotalPay
    FROM [dbo].fnCalculateCommissionForWeekOf(@MondayOfFollowingWeek)

This gets me the data I need but I would like to combine the two results into one table with the same columns but having some of the columns added together (CommissionPay, PTOPay, HolidayPay, Overtime, TotalPay). What is the best way to do this? I am using SQL Server 2008 R2.

这让我获得了我需要的数据，但我想将两个结果合并到一个具有相同列的表中，但将一些列添加到一起（CommissionPay，PTOPay，HolidayPay，Overtime，TotalPay）。做这个的最好方式是什么？我正在使用SQL Server 2008 R2。

3 个解决方案

#1

Try this

尝试这个

SELECT PerceptionistID, SSNLastFour, SUM(CommissionPay) CommissionPay,
        SUM(PTOPay) PTOPay, SUM(HolidayPay) HolidayPay, SUM(Overtime) Overtime, SUM(TotalPay) TotalPay
FROM
(
    SELECT  PerceptionistID, SSNLastFour, CommissionPay,
        PTOPay, HolidayPay, Overtime, TotalPay
    FROM [dbo].fnCalculateCommissionForWeekOf(@MondayOfCurrentWeek)

    UNION ALL

    -- Need to get the following week's data and sum the two together
    SELECT  PerceptionistID, SSNLastFour, CommissionPay,
        PTOPay, HolidayPay, Overtime, TotalPay
    FROM [dbo].fnCalculateCommissionForWeekOf(@MondayOfFollowingWeek)
) t
GROUP BY PerceptionistID, SSNLastFour

#2

Make your query a subquery, and group at the 'superquery' level:

使您的查询成为子查询，并在“超级查询”级别进行分组：

SELECT PerceptionistID, SSNLastFour, sum(CommissionPay), ...
FROM 
(
SELECT  PerceptionistID, SSNLastFour, CommissionPay,
    PTOPay, HolidayPay, Overtime, TotalPay
FROM [dbo].fnCalculateCommissionForWeekOf(@MondayOfCurrentWeek)

UNION

-- Need to get the following week's data and sum the two together
SELECT  PerceptionistID, SSNLastFour, CommissionPay,
    PTOPay, HolidayPay, Overtime, TotalPay
FROM [dbo].fnCalculateCommissionForWeekOf(@MondayOfFollowingWeek)
) AS X
GROUP BY PerceptionistID, SSNLastFour

#3

How about modifying [dbo].fnCalculateCommissionForWeekOf or creating a new function that takes a start and end date. This way you can use the same function, and same logic, to run against any span of dates, including the past week, 2 weeks, month, year etc.

如何修改[dbo] .fnCalculateCommissionForWeekOf或创建一个采用开始和结束日期的新函数。这样，您可以使用相同的功能和相同的逻辑来运行任何日期，包括过去的一周，2周，月，年等。

#1