Stack Exchange
SQL - Salary difference between 2 months (Variance)
There are 2 separate SQL queries that returning NetPay amount MonthWise. I 
want to combine them means i want Variance between 2 months
(Month 2 Amount - Month 1 Amount) as output.
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


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

You can Provide Month dynamically. Hope this works

Giving Error :
Msg 8120, Level 16, State 1, Line 8
Column 'Employee_PaySlip.Emp_Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

You can use Conditional Aggregation to combine those two queries 
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)

or 
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)

You can replace the months in where clause and Case statement if it is not fixed. 
Another approach for using months dynamic 
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 

For newer versions we can use LAG window function 

you cant fix month id

@Dilip which version of sql server you are using ?

If you do not want to join in same query for your better visibility, use 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

