calculate total salary paid to employee [Resolved]

Posted by Sriharim under Sql Server on 6/9/2015 | Points: 10 | Views : 1320 | Status : [Member] | Replies : 5
Employee table :


Employee_ID Name JoiningSalary JoiningDate
1 a 1800 2008-06-30
2 b 2000 2007-07-01


monthly salary mentioned in above table..

Salary Hike table:


ID Employee_ID SalaryHikePercentage Date Comments
1 1 10 2009-04-01 Hike
2 2 5 2008-04-01 Hike
3 1 5 2009-10-30 Bonus
4 1 10 2010-04-30 Hike
5 2 15 2010-04-30 Hike
6 1 10 2011-04-30 Hike


Please tell the QUERY, to calculate the total salary paid to employee from joining date to till 2014-12-31

---
Srihari



Responses

Posted by: Bandi on: 6/16/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

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

Posted by: Lcoimbatorer on: 6/10/2015 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Sriharim on: 6/10/2015 [Member] Starter | Points: 25

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

---
Srihari

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

Posted by: Bandi on: 6/12/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down

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

Posted by: Sriharim on: 6/16/2015 [Member] Starter | Points: 25

Up
0
Down
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.



---
Srihari

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

Login to post response