
Below is the answer for second question
--Table Creation
DECLARE @Employee table(EmployeeID int,EmployeeName varchar(100),JoiningSalary int)
DECLARE @Salary_hike table (EmployeeID int,SalaryHikePercentage int,Year int)
-- Sample data for both tables
insert into @EMPLOYEE
SELECT 1, 'AAA', 180 union all
SELECT 2, 'BBB', 200 union all
SELECT 3, 'CCC', 150 union all
SELECT 4, 'DDD', 100
insert into @Salary_hike
SELECT 1, 10, 2000 union all
SELECT 1, 10, 2001 union all
SELECT 1, 10, 2002 union all
SELECT 1, 10, 2003 union all
SELECT 1, 10, 2004 union all
SELECT 1, 10, 2005 union all
SELECT 1, 40, 2010 union all
SELECT 1, 20, 2015
--Write a query to get total salary paid to employee during 2000 to 2015 year
--Below Query is for getting Total pais salary based on employee
DECLARE @RevisedSal bigint , @EmployeeID int = 1
SELECT @RevisedSal = ISNULL(@RevisedSal, JoiningSalary)+ ( ISNULL(@RevisedSal, JoiningSalary)*SalaryHikePercentage)/100
from @Employee e
join @Salary_hike eh on e.employeeID= eh.employeeID
where e.EmployeeID = @EmployeeID
and year between 2000 and 2015
SELECT @RevisedSal TotalSalPaid, @EmployeeID as EmployeeID
Output:
TotalSalPaid EmployeeID
529 1
Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif
Sudheep.Grandhe, if this helps please login to Mark As Answer. | Alert Moderator