Hi mann,
Please refer below query
SQL
DECLARE @EPayrollDate DATETIME
SET @EPayrollDate = '2016/07/01'
DECLARE @Employeeid INT
SET @Employeeid = 3071
;WITH CTE AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNumber
,EPayrollDate
,Employeeid
,SalaryAmount
,DATEDIFF(DAY
,(SELECT TOP 1 EPayrollDate
FROM #TblEmployeePayroll
WHERE EPayrollDate = @EPayrollDate AND Employeeid = @Employeeid )
,(SELECT TOP 1 EPayrollDate
FROM #TblEmployeePayroll
WHERE Employeeid = @Employeeid
AND EPayrollDate BETWEEN (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @EPayrollDate), 0))
AND
(SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, @EPayrollDate), -1))
ORDER BY EPayrollDate DESC)) AS Days
,(SalaryAmount/DATEDIFF(DAY,@EPayrollDate, DATEADD(MONTH, 1, @EPayrollDate))) AS SalaryPerDay
FROM #TblEmployeePayroll
WHERE EPayrollDate BETWEEN (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @EPayrollDate), 0))
AND
(SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, @EPayrollDate), -1))
AND Employeeid = @Employeeid
),CTE1 AS
(
SELECT RowNumber
,EPayrollDate
,Employeeid
,SalaryAmount
,Days
,SalaryPerDay
FROM CTE
WHERE RowNumber = 1
UNION ALL
SELECT Y.RowNumber
,Y.EPayrollDate
,Y.Employeeid
,Y.SalaryAmount
,DATEDIFF(DAY,(SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @EPayrollDate), 0)),(SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, @EPayrollDate), -1))) - Y.Days
,Y.SalaryPerDay
FROM CTE1
INNER JOIN CTE Y
ON Y.RowNumber = CTE1.RowNumber + 1
)
SELECT Employeeid,SUM([Days]*SalaryPerDay) AS MonthlySalary
FROM CTE1
GROUP BY Employeeid
This query is for only single employee whose payrolldate is more then one in single month
Hope this works for you