我们的员工每周都会获得佣金,由于奖金结构,我必须计算两个单独的工资周,然后将两者加在一起.
我有以下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,TotalPay
FROM [dbo].fnCalculateCommissionForWeekOf(@MondayOfFollowingWeek)
这让我获得了我需要的数据,但我想将两个结果合并到一个具有相同列的表中,但将一些列添加到一起(CommissionPay,TotalPay).做这个的最好方式是什么？我正在使用sql Server 2008 R2.
解决方法
试试这个
SELECT PerceptionistID,SUM(CommissionPay) CommissionPay,SUM(PTOPay) PTOPay,SUM(HolidayPay) HolidayPay,SUM(Overtime) Overtime,SUM(TotalPay) TotalPay
FROM
(
SELECT PerceptionistID,TotalPay
FROM [dbo].fnCalculateCommissionForWeekOf(@MondayOfCurrentWeek)
UNION ALL
-- Need to get the following week's data and sum the two together
SELECT PerceptionistID,TotalPay
FROM [dbo].fnCalculateCommissionForWeekOf(@MondayOfFollowingWeek)
) t
GROUP BY PerceptionistID,SSNLastFour
