USE [dbDoclandCRM]GO
StoredProcedure [dbo].[Select_salary]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Select_salary]@date date=null
As
Begincreate table #temp( Employeeid bigint, Ename varchar(max), ECode int,salary decimal(18,2),noofdays int)
declare @empid int,@salary decimal(15,2);
DECLARE Employee_Cursor
CURSOR FOR SELECT employeeidFROM TblEmployee
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor INTO @empid;
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #tempselect Employeeid,EName,ECode,isnull(((select top(1) SalaryAmount
from TblEmployeePayroll
where Employeeid=@empid and Month(DATEADD(ms,-2,DATEADD(MONTH, DATEDIFF(MONTH,0,EPayrollDate)+1,0))) = Month(DATEADD(ms,-2,DATEADD(MONTH, DATEDIFF(MONTH,0,@date)+1,0)))
order by EPayrollDate desc ) * (select COUNT(Attendancedate) from TblEmployeeAttendance where Employeeid=@empid and Attendancedate between @date and DATEADD(DD,30, @date) ) /[dbo].[ufn_GetDaysInMonth](@date)),0) as salary,
(select COUNT(Attendancedate) from TblEmployeeAttendance where Employeeid=@empid and Attendancedate between @date and DATEADD(DD,30, @date)) as noofdays from TblEmployee where Employeeid=@empid
FETCH NEXT FROM Employee_Cursor INTO @empid;
END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
select * from #tempdrop table #tempEnd
i want to calculate salary of employee if there is multiple payroll in a month for a employee like
in july month from 01/07/2016 to 13/07/2016 his payroll was 12500
and after that his payroll was 17000 so how to calculate total salary for july month of that employee
in my query its calculated only on one payroll..but i want to calculate on both payroll