SQL - 2 个月之间的工资差异(差异)

Question

有2个单独的SQL查询返回NetPay金额MonthWise。我 想要合并它们意味着我想要 2 个月之间的差异 (第 2 个月金额 - 第 1 个月金额(作为输出。

Query - 1
Select ISNULL(Sum(EPS.Amount),0) as Amount
From Payslip EPS
Where EPS.Emp_Id = 5 and EPS.Month_Id = 5
Query - 2
Select ISNULL(Sum(EPS.Amount),0) as Amount
From Payslip EPS
Where EPS.Emp_Id = 5 and EPS.Month_Id = 6

Answer 1

DECLARE @month1 INT
DECLARE @month2 INT
SET @month1= 5
SET @month2= 6
Select (a.month1Amount-b.month2Amount) AS Variance from
(Select ISNULL(Sum(EPS.Amount),0) as month1Amount,EPS.Emp_Id
From Payslip EPS
Where EPS.Emp_Id = 5 and EPS.Month_Id =@month1 GROUP BY EPS.Emp_Id)a
INNER JOIN 
(Select ISNULL(Sum(EPS.Amount),0) as month2Amount, EPS.Emp_Id
From Payslip EPS
Where EPS.Emp_Id = 5 and EPS.Month_Id =@month2  GROUP BY EPS.Emp_Id)b
on a.Emp_Id=b.Emp_Id

您可以动态提供月份。希望这有效

Answer 2

您可以使用Conditional Aggregation来组合这两个查询

Select Sum(case EPS.Month_Id when 6 then EPS.Amount else -EPS.Amount end) as Variance 
From Payslip EPS
Where EPS.Emp_Id = 5 and EPS.Month_Id in (6,5)

或

Select Sum(case EPS.Month_Id when 6 then EPS.Amount else 0 end) - 
Sum(case EPS.Month_Id when 5 then EPS.Amount else 0 end)
From Payslip EPS
Where EPS.Emp_Id = 5 and EPS.Month_Id in (6,5)

您可以替换where子句中的月份，如果未固定Case语句。

使用月份动态的另一种方法

SELECT Isnull(A.Amount, 0) - Isnull(B.amount, 0)
FROM   (SELECT months,
Amount = Sum(Amount)
FROM   #payslips
GROUP  BY months) a
INNER JOIN (SELECT months,
Amount = Sum(Amount)
FROM   #payslips
GROUP  BY months) b
ON a.months = b.months + 1
WHERE  a.months = 6
AND b.months = 5 

对于较新版本，我们可以使用LAG窗口功能

Answer 3

如果您不想为了获得更好的可见性而加入同一查询，请使用 Union all

Select Amount1-Amount As Variance
from 
(
Select ISNULL(Sum(EPS.Amount),0) as Amount, '0.00' Amount1
From Payslip EPS
Where EPS.Emp_Id = 5 and EPS.Month_Id = 5
UNION ALL 
Select '0.00' Amount, ISNULL(Sum(EPS.Amount),0) as Amount1
From Payslip EPS
Where EPS.Emp_Id = 5 and EPS.Month_Id = 6
)am