我们的员工每周获得佣金,由于奖金结构,我必须计算两个单独的周的工资,然后将两者加在一起。
我有以下 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)
这得到了我需要的数据,但我想将两个结果合并到一个具有相同列的表中,但将一些列添加在一起(CommissionPay、PTOPay、HolidayPay、Overtime、TotalPay)。做这个的最好方式是什么?我正在使用 SQL Server 2008 R2。
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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)