从商店程序计算YTD

问题描述

我正在计算年初至今（年初至今）的工资金额



年初：年初至今是指年初至今。这是从当年的1月1日开始直到薪资周期的最后一天。当前意味着您支付的时间。



例如你的实际支付1月= 15000，2月= 20000，2月25000，那么任何月份的年初至今将是=当月支付+你当前月份之前的所有支付，（只有你没有支付的月份的那个月）被认为是0）



所以我的代码是通过手动传递EmplID和Month来计算当前月工资，但我想计算YTD，我无法这样做请帮助。

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 @TotalHoursWorked = (Select OverallTime from MonthlyRecord where EmplID = @emplID AND Month = @month )
		Set @BSalaryHour = @BasicSalary / @TotalHours
		Set @TotalSalary = @BSalaryHour * @TotalHoursWorked
		
		--------------------------------------------------------------------------------------
	
	-- Return the result of the function
	RETURN @TotalSalary

END

推荐答案

这篇关于从商店程序计算YTD的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！