calculate total salary paid to employee [Resolved]

The problem can be compatibility level of database...

check compatibility level of your database. If it is not >= 110, set compatibility level to 110... so that LEAD/LAG window function will run....

select name, compatibility_level , version_name = 
CASE compatibility_level
    WHEN 65  THEN 'SQL Server 6.5'
    WHEN 70  THEN 'SQL Server 7.0'
    WHEN 80  THEN 'SQL Server 2000'
    WHEN 90  THEN 'SQL Server 2005'
    WHEN 100 THEN 'SQL Server 2008/R2'
    WHEN 110 THEN 'SQL Server 2012'
    WHEN 120 THEN 'SQL Server 2014'
END
from sys.databases

Run

ALTER DATABASE yourDBName 
SET COMPATIBILITY_LEVEL = 110 

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Hi
Please find the below query to find the total salary


calculation formyula : ((Percentage change* joiningsalary)/100)+joining salary will give total salary

select employeeid,sum(salaryhikepercentage) as tot_hikepercent
into #temp3
from salary_hike
group by employeeid

select #temp3.employeeid,(((tot_hikepercent*employee.joiningsalary)/100)+joiningsalary) as totalsalarypaid
from #temp3 join employee on #temp3.employeeid=employee.employee_id

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

you have not taken time (months) and hike can be 2 times in a year.
not getting correct value..

Mark as Answer if its helpful to you
---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

DECLARE @Employee table (Employee_ID    int, Name    varchar(100), JoiningSalary    int, JoiningDate date)
insert @Employee
SELECT 1, 'a', 1800, '2008-06-30' union all
SELECT 2, 'b', 2000, '2007-07-01'


DECLARE @SalaryHike table (ID     int, Employee_ID    int, SalaryHikePercentage   int, HikeDate date, Comments varchar(100))
INSERT @SalaryHike
SELECT 1, 1, 10, '2009-04-01', 'Hike' union all
SELECT 2, 2, 5, '2008-04-01', 'Hike' union all
SELECT 3, 1, 5, '2009-10-30', 'Bonus' union all
SELECT 4, 1, 10, '2010-04-30', 'Hike' union all
SELECT 5, 2, 15, '2010-04-30', 'Hike' union all
SELECT 6, 1, 10, '2011-04-30', 'Hike'


--Declaration of required variables 

DECLARE @EmpID int = 1  -- enter the employee ID 
	,@SAL DEC(18,2) 
	,@DOJ date 
	,@Inc dec(18,2) 
	,@TotSal dec(18,2)=0

--fetch joining date and salary of an employee 

SELECT @SAL = JoiningSalary , @DOJ = JoiningDate 
FROM @Employee
WHERE Employee_ID = @EmpID

--query to sum up the salary paid to an employee 
;With CteEMpHike As 
(
	SELECT @EmpID as Employee_ID, 0 as SalaryHikePercentage, @DOJ as HikeDate, '' as Comments   
	UNION ALL
	SELECT Employee_ID, SalaryHikePercentage, HikeDate, Comments
	FROM @SalaryHike WHERE Employee_ID=@EmpID
)
SELECT @Inc = ISNULL(@Inc,@SAL) + ((ISNULL(@Inc, @SAL)*SalaryHikePercentage)/100.0)
,@TotSal = ISNULL(@TotSal,@Inc) + @Inc*(DATEDIFF(DD, HikeDate,ISNULL(LEAD(HikeDate) OVER(ORDER BY HikeDate), GETDATE() ))/30) --summed up the salary of an employee from last hikedate to till date also 
from CteEMpHike
WHERE Employee_ID = @EmpID  

SELECT @TotSal TotalPaidSalaryForEmp, @EmpID as EmpID

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Bandi, thank u for your reply, in sql server 2012 version, i tried above query, but i am getting error as below:

Msg 195, Level 15, State 10, Line 21
'LEAD' is not a recognized built-in function name.

Mark as Answer if its helpful to you
---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator