Calculating YTD from store procedure

Question

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.

USE [a1]
GO
/****** Object:  UserDefinedFunction [dbo].[GetTotalSalary1_func]    Script Date: 2014-02-21 4:12:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


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

Answer 1

any help ?

Answer 2

Hello Evil, I am not able to see that you are doing a SUM of salaries?

Which table has the salary details per month? Could you please provide us DDL,DML and sample output. We would be able to help you better.

Answer 3

not table . this above function calculates total salary for PROVIDED month 

Answer 4

Hello Evil, Whats your issue?

Please find below test script I used...its working fine

create Table HrEmployee (EmplID int, BasicSalary int)
Insert into HrEmployee Select 1,1000

Select dbo.GetTotalSalary1_func(1,'0')
--Select (1000/(24*8))*8 --Which is matching

Drop table HrEmployee


Go
create 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 = 8;--LEFT(@hms,charindex(':',@hms)-1)
set @mts=00;--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

Answer 5

With out knowing your data structure its really difficult to suggest something. 

---

Satheesh
My Blog | How to ask questions in technical forum

Answer 6

USE [a1]
GO
/****** Object:  UserDefinedFunction [dbo].[GetTotalSalary1_func]    Script Date: 2014-02-21 4:12:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetTotalSalary1_func] 
(
	@emplID int,
	@month VARCHAR(50) = NULL
)
RETURNS @RESULTS TABLE
(
TotalHoursWorked float,
TotalHoursWorkedYTD float
)
AS
BEGIN
	Declare @StartDate Date,Declare @StartDateYTD Date,  @EndDate Date, @mydate date, @TotalDays int, @TotalDaysYTD int, @TotalHours int, @TotalHoursYTD int, 
		        @BasicSalary float, @BSalaryHour float, @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 @StartDateYTD = (SELECT CONVERT(VARCHAR(25),DATEADD(yy,DATEDIFF(yy,0,@mydate),0),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 @TotalDaysYTD =((DATEDIFF(dd, @StartDateYTD, @EndDate) + 1)
		  -(DATEDIFF(wk, @StartDateYTD, @EndDate) * 1)
		  -(CASE WHEN DATENAME(dw, @StartDateYTD) = 'Sunday' THEN 1 ELSE 0 END)) 
		Set @TotalHours  = (@TotalDays * 8)
		Set @TotalHoursYTD  = (@TotalDaysYTD * 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
		Set @TotalSalaryYTD = @BSalaryHour * @TotalHoursWorkedYTD
		--------------------------------------------------------------------------------------
	
	-- Return the result of the function
INSERT @RESULTS
VALUES(@TotalSalary,@TotalSalaryYTD)
	RETURN 
END

And since it returns a table you've invoke it like

SELECT t.*,f.*
FROM YourTable t
CROSS APPLY dbo.GetTotalSalary1_func(t.EmpIDField,t.MonthField)f

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 7

in short, i am caluculating current salary

Set @TotalSalary = @BSalaryHour * @TotalHoursWorked

 by passing Emplid and Month as parameters to the function i posted but now i want to calculate YTD.

Mean this month salary + all previous salaries, e.g when i provide (5, '2013-march') so for empid 5 it would return March's salary + sum of all salaries that he got prior to march. 

Answer 8

? any help ?

Answer 9

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. 

in short, i am caluculating current salary

Set @TotalSalary = @BSalaryHour * @TotalHoursWorked

 by passing Emplid and Month as parameters to the function i posted but now i want to calculate YTD.

Mean this month salary + all previous salaries, e.g when i provide (5, '2013-march') so for empid 5 it would return March's salary + sum of all salaries that he got prior to march. 

USE [a1]
GO

/****** Object:  UserDefinedFunction [dbo].[GetTotalSalary1_func]    Script Date: 2014-02-21 4:12:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


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

Answer 10

? any help would be appreciated 

Answer 11

So does this help? I added one more parameter @ytd and when =1 it will take the @startdate as 1st Jan . Will this logic work for you?

create FUNCTION [dbo].[GetTotalSalary1_func] 
(
	@emplID int,
	@month VARCHAR(50) = NULL,
	@ytd int=0
)
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 = 8;--LEFT(@hms,charindex(':',@hms)-1)
set @mts=00;--RIGHT(@hms,len(@hms)-charindex(':',@hms))

set @TotalHoursWorked = @hrs + (@mts/60.0)


		
		Set @mydate = GETUTCDATE()
		Set @StartDate = case when @ytd=0 then (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101)) else convert(varchar(4),year(@mydate))+'0101' end
		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

---

Satheesh
My Blog | How to ask questions in technical forum

Answer 12

will this help?

create Table HrEmployee (EmplID int, BasicSalary int)
Insert into HrEmployee Select 1,1000

Select dbo.GetTotalSalary1_func(1,'0')
--Select (1000/(24*8))*8 --Which is matching

Drop table HrEmployee


Go
create FUNCTION [dbo].[GetTotalSalary1_func] 
(
	@emplID int,
	@month VARCHAR(50) = NULL,
	@ytd int=0
)
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 = 8;--LEFT(@hms,charindex(':',@hms)-1)
set @mts=00;--RIGHT(@hms,len(@hms)-charindex(':',@hms))

set @TotalHoursWorked = @hrs + (@mts/60.0)


		
		Set @mydate = GETUTCDATE()
		Set @StartDate = case when @ytd=0 then (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101)) else convert(varchar(4),year(@mydate))+'0101' end
		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

---

Satheesh
My Blog | How to ask questions in technical forum

Answer 13

didnt i gave you solution here?

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/99cb7a5f-3906-423e-b9ef-02800531b6af/calculating-ytd-from-store-procedure?forum=sqlgetstarted

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 14

sorry to say sir but it will drop my HrEmployee table, why would i do that ? it's my main table

Answer 15

sir it will drop my HREmployee table which is my main table

Answer 16

Remove the drop part and check just the functions. I copied the script from the above which had the drop. that is not intended. 

---

Satheesh
My Blog | How to ask questions in technical forum