Test
Friday, 9 December 2016
Thursday, 7 April 2016
Procedure
procedure [dbo].[SelectEmployeeTotalHour]
--@EmployeeId VARCHAR(10),
@InTimeDate VARCHAR(10) ,
@OutTimeDate VARCHAR(10)
as
begin
--select em.* ,(SELECT ISNULL(SUM(CAST(ea.ActualHours AS DECIMAL(18,2))),0) FROM dbo.EmpAttendance AS ea
-- left join EmployeeMaster as EM on EM.EmployeeId=ea.EmpId WHERE EM.EmployeeId=ea.EmpId and ea.Date >= @StartDate and ea.Date<=@EndDate) as workinghours
-- from EmployeeMaster as em where IsDeleted=0
select EM.*, (select ISNULL(SUM(rim.NoOfHour),0) from EmployeeTimeSheetMaster as rim where EmployeeId in(select EmployeeId from EmployeeMaster as test where test.EmployeeId = EM.EmployeeId) and rim.TimeSheetDate>=@InTimeDate and rim.TimeSheetDate<=@OutTimeDate ) as NoOfHour,
(select count(rim.Status) from EmployeeTimeSheetMaster as rim where Status='Present' and EmployeeId in(select EmployeeId from EmployeeMaster as test where test.EmployeeId = EM.EmployeeId) and rim.TimeSheetDate>=@InTimeDate and rim.TimeSheetDate<=@OutTimeDate ) as NoOfDays from EmployeeMaster as EM where EM.IsDeleted=0
end
--@EmployeeId VARCHAR(10),
@InTimeDate VARCHAR(10) ,
@OutTimeDate VARCHAR(10)
as
begin
--select em.* ,(SELECT ISNULL(SUM(CAST(ea.ActualHours AS DECIMAL(18,2))),0) FROM dbo.EmpAttendance AS ea
-- left join EmployeeMaster as EM on EM.EmployeeId=ea.EmpId WHERE EM.EmployeeId=ea.EmpId and ea.Date >= @StartDate and ea.Date<=@EndDate) as workinghours
-- from EmployeeMaster as em where IsDeleted=0
select EM.*, (select ISNULL(SUM(rim.NoOfHour),0) from EmployeeTimeSheetMaster as rim where EmployeeId in(select EmployeeId from EmployeeMaster as test where test.EmployeeId = EM.EmployeeId) and rim.TimeSheetDate>=@InTimeDate and rim.TimeSheetDate<=@OutTimeDate ) as NoOfHour,
(select count(rim.Status) from EmployeeTimeSheetMaster as rim where Status='Present' and EmployeeId in(select EmployeeId from EmployeeMaster as test where test.EmployeeId = EM.EmployeeId) and rim.TimeSheetDate>=@InTimeDate and rim.TimeSheetDate<=@OutTimeDate ) as NoOfDays from EmployeeMaster as EM where EM.IsDeleted=0
end
Subscribe to:
Posts (Atom)