SQL - 2个月之间的薪酬差异（差异）

Question

有2个单独的SQL查询返回NetPay金额MonthWise。 I 想要合并它们意味着我想要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

您可以使用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 2

如果你不想在同一个查询中加入你的更好的可视性，使用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 

Answer 3

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 [email protected] 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 [email protected] GROUP BY EPS.Emp_Id)b 
on a.Emp_Id=b.Emp_Id 

您可以动态提供月份。希望这个作品