我们的员工每周都会获得佣金,由于奖金结构,我必须计算两个单独的工资周,然后将两者加在一起.
我有以下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
