# 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 @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

Friday, February 21, 2014 11:31 AM

• 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 @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

Saturday, February 22, 2014 7:42 AM
• Remove the drop part and check just the functions. I copied the script from the above which had the drop. that is not intended.

Saturday, February 22, 2014 5:35 PM

### All replies

• any help ?
Friday, February 21, 2014 12:10 PM
• 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.

Friday, February 21, 2014 12:14 PM
• not table . this above function calculates total salary for PROVIDED month
Friday, February 21, 2014 12:19 PM
• 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 @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

Friday, February 21, 2014 12:27 PM
• With out knowing your data structure its really difficult to suggest something.

Friday, February 21, 2014 12:36 PM
• 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 @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

Friday, February 21, 2014 12:44 PM
• 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.

Friday, February 21, 2014 12:51 PM
• ? any help ?

Friday, February 21, 2014 5:36 PM
• 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 @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

• Merged by Sunday, February 23, 2014 3:28 AM Similar discussion by same user
Saturday, February 22, 2014 6:13 AM
• ? any help would be appreciated
Saturday, February 22, 2014 6:51 AM
• 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 @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

Saturday, February 22, 2014 7:38 AM
• 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 @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

Saturday, February 22, 2014 7:42 AM
• 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