question

caoneill avatar image
caoneill asked

SQL - Compensation by Year for Employees

Hi,I need to be able to break down salary by year for employees.I am giving an example of a current and terminated employee. The term date should be taken from the employee table. When salary are changed within the year I need to create an end date for the salary so I can prorate the total for the year. Below is an example of the data and expected output. Thanks so much!

CREATE TABLE [dbo].[EMPLOYEE]( [EMPID] [VARCHAR](15)NULL, [NAME] [VARCHAR](15)NULL, [FIRST_HIRE_DATE] [DATETIME] NULL, [HIRE_DT] [DATETIME] NULL, [TERM_DT] [DATETIME] NULL ) ON [PRIMARY] INSERT INTO [dbo].[EMPLOYEE] VALUES('100123','John','2015-12-14 00:00:00.000','2015-12-14 00:00:00.000',NULL) INSERT INTO [dbo].[EMPLOYEE] VALUES('100124','Jane','2015-02-09 00:00:00.000','2015-02-09 00:00:00.000','2019-11-01 00:00:00.000') CREATE TABLE [dbo].[EMPLOYEE_CHANGES]( [EMPID] [VARCHAR](15)NULL, [NAME] [VARCHAR](15)NULL, [SALARY_EFFECTIVE_DATE] [DATETIME] NULL, [CHANGE_REASON] [VARCHAR](25) NULL, [ANNUAL_SALARY] [money] NULL ) ON [PRIMARY] INSERT INTO [dbo].[EMPLOYEE_CHANGES] VALUES('100123','John','2011-12-14 00:00:00.000','NewHire','Active',100000.00) INSERT INTO [dbo].[EMPLOYEE_CHANGES] VALUES('100123','John','2017-01-01 00:00:00.000','AnnualIncrease','Active',110000.00) INSERT INTO [dbo].[EMPLOYEE_CHANGES] VALUES('100124','Jane','2015-02-09 00:00:00.000','NewHire','Active',200000.00) INSERT INTO [dbo].[EMPLOYEE_CHANGES] VALUES('100124','Jane','2016-02-13 00:00:00.000','AnnualIncrease','Active',215000.00) INSERT INTO [dbo].[EMPLOYEE_CHANGES] VALUES('100124','Jane','2017-02-11 00:00:00.000','AnnualIncrease','Active',225000.00) INSERT INTO [dbo].[EMPLOYEE_CHANGES] VALUES('100124','Jane','2019-02-09 00:00:00.000','Resignation','Terminated',225000.00)

EMPID NAME FIRST_HIRE_DATE HIRE_DT TERM_DT Year AnnualSalary SalaryEffectiveDate SalaryEndDate SalaryChangeReason 100123 John 12/14/2015 12/14/2015 NULL 2015 100000 12/14/2015 12/31/2015 NewHire 100123 John 12/14/2015 12/14/2015 NULL 100123 John 12/14/2015 12/14/2015 NULL 2017 110000 1/1/2017 12/31/2016 AnnualIncrease 100123 John 12/14/2015 12/14/2015 NULL 100123 John 12/14/2015 12/14/2015 NULL 100124 Jane 2/9/2015 2/9/2015 11/1/2019 2015 200000 2/9/2015 12/31/2015 NewHire 100124 Jane 100124 Jane 2/9/2015 2/9/2015 11/1/2019 2016 215000 2/13/2016 12/31/2016 AnnualIncrease 100124 Jane 100124 Jane 2/9/2015 2/9/2015 11/1/2019 2017 225000 2/11/2017 12/31/2017 AnnualIncrease 100124 Jane 100124 Jane 2/9/2015 2/9/2015 11/1/2019 2019 225000 1/1/2019 11/1/2019 Terminated
sql server 2016
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I have this so far but still need to have a row with start and end dates for each employee by year. Thanks!

select e.empid,e.name,e.FIRST_HIRE_DATE,e.HIRE_DT,e.TERM_DT,NULL as YEAR, c.ANNUAL_SALARY,SALARY_EFFECTIVE_DATE as SalaryEffStartDt, lead(dateadd(day,-1,SALARY_EFFECTIVE_DATE),1,e.TERM_DT) over (partition by c.empid order by c.SALARY_EFFECTIVE_DATE) SalaryEffEndDt from employee e join EMPLOYEE_CHANGES c on c.empid = e.EMPID order by empid,SalaryEffStartDt

0 Likes 0 ·

I think you're missing a field in your definition of EMPLOYEE_CHANGES in the script you provided.

Also, can you provide the output you're expecting to see as another create table / insert script? It's hard to figure out what's going on from the copy/paste job in the question.

0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.