Get employee's salary for each month

Question

I have 2 table [EmployeeInfo] [EmployeeInfoHistory] and I am storing salary of the employee in this table. I am able to get the salary of the employee against respective employee and stuff.

But I want to show employee's salary for every month like listed below.

My structure is for [EmployeeInfo]:

Create Table [EmployeeInfo]
(
  EmpID Int,
  Salary Int,
  EmployementStatus Varchar(50),
  HiringDate Date
);

My structure is for [EmployeeInfoHistory]:

Create Table [EmployeeInfoHistory]
(
  EmpID Int,
  NewSalary Int,
  UpdatedEmployementStatus Varchar(50),
  PromotionDate Date
);

Data for Info Table:

EmpID  Salary  Status   HiringDate
1      20000   Intern   2017-10-02
2      30000   Jr. DBA  2017-11-01

Data for InfoHistory Table:

EmpID  Salary  UpdatedStatus   PromotionDate
1      25000   Jr. DBA         2018-01-01
2      45000   Sr. DBA         2018-01-01

I want to list date in given format.

EmpID   Month     Salary  Status
1       October   20k     Intern
1       November  20k     Intern
1       December  20k     Intern
1       January   25k     Jr. DBA
2       November  30k     Jr. DBA
2       December  30k     Jr. DBA
2       January   45k     Sr. DBA

Thanks in advance

Answer 1

You need a table with dates. Here I've made a table using a CTE wich I join with your tables.

; WITH DS AS
(
    SELECT cast (Dateadd(year, -25, getdate()) AS date) as [Date]
        UNION all
    SELECT dateadd(Month, 1, [Date])
        FROM DS
        WHERE Date < getdate()
)
Select EMP.EmpID, DateName(month, DS.[Date]) as [Month], isnull(HIS.NewSalary, EMP.Salary) as Salary, ISNULL(HIS.UpdatedStatus, EMP.EmployementStatus) as [Status]
from EmployeeInfo as EMP
    inner join DS 
    on EMP.HiringDate <= DS.[Date]  
        left join EmployeeInfoHistory as HIS
        on HIS.EmpId = EMP.EmpID and HIS.PromotionDate <= DS.[Date]  
option(MaxRecursion 10000)