I am trying to calculate YTD (Year to date) salary amount
YTD: "YTD" means Year-To-Date. It is the period starting January 1 of the current year until the last day of the pay cycle. Current means what you made that pay period.
e.g. Your actual pay for January = 15000, for Feb=20000, for march 25000 then YTD of any month will be = to current month pay+ all pays that you got before current month, (only those month in which you got otherwise non paid months will be considered 0)
so my code is to calculate Current month salary via store procedure by passing EmplID and Month manually but i want to calculate YTD which i am unable to so far, HELP please.
ALTER FUNCTION [dbo].[GetTotalSalary1_func]
(
@emplID int,
@month VARCHAR(50) = NULL
)
RETURNS int
AS
BEGIN
Declare @StartDate Date, @EndDate Date, @mydate date, @TotalDays int, @TotalHours int,
@BasicSalary float, @BSalaryHour int, @TotalSalary float, @hms varchar(100),@hrs int, @mts int, @TotalHoursWorked float
set @hms = (Select OverallTime from MonthlyRecord where EmplID = @emplID AND Month = @month )
Set @hrs = LEFT(@hms,charindex(':',@hms)-1)
set @mts=RIGHT(@hms,len(@hms)-charindex(':',@hms))
set @TotalHoursWorked = @hrs + (@mts/60.0)
Set @mydate = GETUTCDATE()
Set @StartDate = (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101))
Set @EndDate = (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101))
Set @TotalDays =((DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 1)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END))
Set @TotalHours = (@TotalDays * 8)
Set @BasicSalary = (Select BasicSalary from HrEmployee where EmplID=@emplID)
Set @BSalaryHour = @BasicSalary / @TotalHours
Set @TotalSalary = @BSalaryHour * @TotalHoursWorked
RETURN @TotalSalary
END