从商店程序计算YTD [英] calculating YTD from store procedure

问题描述

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



年初：年初至今是指年初至今。这是从当年的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

推荐答案

SELECT SUM（...）FROM YourTable WHERE YEAR（paydate）= YEAR（getdate（））



这将为您提供当年所有价值的总和。



在您的代码中，它看起来像你'根据小时数和费率重新计算支付金额。在你的文字描述中，似乎你把支付的金额总计为一个字段。



我不知道你的桌子里有什么 - 无论是什么给你在每个时期收到的总薪水都属于SUM函数（例如amt或hrsWorked * hourlyRate）。

SELECT SUM(...) FROM YourTable WHERE YEAR(paydate) = YEAR(getdate())

This will give you the sum of all values for the current year.

In your code, it seems like you're calculating the amount paid based on hours and rates. In your text description, it seems that you're summing the amounts paid as though they were a field.

I don't know what you have in your table - whatever gives you the total pay received during each period is what belongs inside the SUM function (amt or hrsWorked*hourlyRate, for example).

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