SQLTeam.com | Weblogs | Forums

How to get the employee YearWise and get the other details from Promotion table accordingly

How to get the employee YearWise and get the other details from Promotion table accordingly.

The employee Table is given below.

CREATE TABLE [dbo].[Employee](
	[Emp_No] [numeric](18, 0) NULL,
	[Emp_Number] [nvarchar](50) NULL,
	[Emp_Name] [nvarchar](50) NULL,
	[Emp_JoiningDate] [date] NULL,
	[Emp_ResignDate] [date] NULL,
	[Emp_Status] [nvarchar](50) NULL,
	[Emp_Designation] [nvarchar](50) NULL,
	[Emp_Gross] [numeric](18, 0) NULL
) ON [PRIMARY]

GO

the Data is

INSERT INTO [Employee]
           ([Emp_No]
           ,[Emp_Number]
           ,[Emp_Name]
           ,[Emp_JoiningDate]
           ,[Emp_ResignDate]
           ,[Emp_Status],Emp_Designation,Emp_Gross)
     VALUES(
           1
           ,'A-001'
           ,'Alex'
           ,'2013-01-01'
           ,'2013-08-24'
           ,'Resigned','Trainee',200)
GO

INSERT INTO [Employee]
           ([Emp_No]
           ,[Emp_Number]
           ,[Emp_Name]
           ,[Emp_JoiningDate]
           ,[Emp_ResignDate]
           ,[Emp_Status],Emp_Designation,Emp_Gross)
     VALUES(
           2
           ,'A-002'
           ,'Adam'
           ,'2013-01-01'
           ,null
           ,'On Board','HRM',400)
GO

INSERT INTO [Employee]
           ([Emp_No]
           ,[Emp_Number]
           ,[Emp_Name]
           ,[Emp_JoiningDate]
           ,[Emp_ResignDate]
           ,[Emp_Status],Emp_Designation,Emp_Gross)
     VALUES(
           3
           ,'A-003'
           ,'Maxwell'
           ,'2014-01-01'
           ,null
           ,'On Board','FM',600)
GO

INSERT INTO [Employee]
           ([Emp_No]
           ,[Emp_Number]
           ,[Emp_Name]
           ,[Emp_JoiningDate]
           ,[Emp_ResignDate]
           ,[Emp_Status],Emp_Designation,Emp_Gross)
     VALUES(
           4
           ,'A-004'
           ,'Smith'
           ,'2014-01-01'
           ,'2014-08-20'
           ,'Resigned','CEO',1000)
GO

Promotion Table structure is

CREATE TABLE [dbo].[Promotion](
	[Prom_No] [numeric](18, 0) NULL,
	[Prom_EmpNo] [numeric](18, 0) NULL,
	[Last_Designation] [nvarchar](500) NULL,
	[Promoted_Designation] [nvarchar](500) NULL,
	[WEF_Date] [date] NULL,
	[Promoted_Gross] [numeric](18, 0) NULL,
	[Last_Gross] [numeric](18, 0) NULL
) ON [PRIMARY]

GO

the Data is

INSERT INTO [Promotion]
           ([Prom_No]
           ,[Prom_EmpNo]
           ,[Last_Designation]
           ,[Promoted_Designation]
           ,[WEF_Date]
           ,[Promoted_Gross]
           ,[Last_Gross])
     VALUES
           (1,2,'HRM Assitant','HRM','2014-01-01',400,200)          

GO
INSERT INTO [Promotion]
           ([Prom_No]
           ,[Prom_EmpNo]
           ,[Last_Designation]
           ,[Promoted_Designation]
           ,[WEF_Date]
           ,[Promoted_Gross]
           ,[Last_Gross])
     VALUES
           (2,3,'Accountant','FM','2015-01-01',600,300)          

GO

The O/P should be

Thanks
Basit.

1 Like

Could this be what you're looking for:

declare @start_dt date=(
   select cast(dateadd(year,datediff(year,0,min([emp_joiningdate])),0) as date)
     from employee
);

with cte_dt(dt)
  as (select top(datepart(year,current_timestamp)-datepart(year,@start_dt)+1)
             dateadd(year,row_number() over(order by (select null))-1,@start_dt)
        from             (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as tally1(n)
             cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as tally2(n)
             cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as tally3(n)
     )
select datepart(year,y.dt) as y
      ,e.emp_no
      ,e.emp_number
      ,e.emp_name
      ,e.emp_joiningdate
      ,e.emp_resigndate
      ,e.emp_status
      ,isnull(case
                 when p.wef_date>y.dt
                 then p.last_designation
                 else p.promoted_designation
              end
             ,e.emp_designation
             )
       as designation
      ,isnull(case
                 when p.wef_date>y.dt
                 then p.last_gross
                 else p.promoted_gross
              end
             ,e.emp_gross
             )
       as gross
  from cte_dt as y
       inner join employee as e
               on e.emp_joiningdate<=y.dt
              and isnull(e.emp_resigndate,current_timestamp)>=y.dt
       left outer join promotion as p
                    on p.prom_empno=e.emp_no
;

Many Thanks, but the Year is also showing duplicate/multiple for sample employee. below is the data.

INSERT INTO [UserDB].[dbo].[Employee]
           ([Emp_No]
           ,[Emp_Number]
           ,[Emp_Name]
           ,[Emp_JoiningDate]
           ,[Emp_ResignDate]
           ,[Emp_Status]
           ,[Emp_Designation]
           ,[Emp_Gross])
     VALUES
           (1,'A-001','Alex','2012-09-19',null,'OnBoard','Trainee3',2000)
GO


INSERT INTO [UserDB].[dbo].[Promotion]
           ([Prom_No]
           ,[Prom_EmpNo]
           ,[Last_Designation]
           ,[Promoted_Designation]
           ,[WEF_Date]
           ,[Promoted_Gross]
           ,[Last_Gross])
     VALUES
           (1,1,'Trainee1','Trainee2','2013-11-01',1000,500)
GO

INSERT INTO [UserDB].[dbo].[Promotion]
           ([Prom_No]
           ,[Prom_EmpNo]
           ,[Last_Designation]
           ,[Promoted_Designation]
           ,[WEF_Date]
           ,[Promoted_Gross]
           ,[Last_Gross])
     VALUES
           (2,1,'Trainee2','Trainee3','2014-03-01',2000,1000)
GO

O/P is below

result should be

Thanks
Basit.

With the query I showed, used on your sample data, I get:

y     emp_no  emp_number  emp_name  emp_joiningdate  emp_resigndate  emp_status  designation   gross
2013  1       A-001       Alex      2013-01-01       2013-08-24      Resigned    Trainee       200
2013  2       A-002       Adam      2013-01-01       NULL            On Board    HRM Assitant  200
2014  2       A-002       Adam      2013-01-01       NULL            On Board    HRM           400
2014  3       A-003       Maxwell   2014-01-01       NULL            On Board    Accountant    300
2014  4       A-004       Smith     2014-01-01       2014-08-20      Resigned    CEO           1000
2015  2       A-002       Adam      2013-01-01       NULL            On Board    HRM           400
2015  3       A-003       Maxwell   2014-01-01       NULL            On Board    FM            600
2016  2       A-002       Adam      2013-01-01       NULL            On Board    HRM           400
2016  3       A-003       Maxwell   2014-01-01       NULL            On Board    FM            600
2017  2       A-002       Adam      2013-01-01       NULL            On Board    HRM           400
2017  3       A-003       Maxwell   2014-01-01       NULL            On Board    FM            600

No duplicates (same employee shows only once each year).

Many Thanks bitsmed, i want little bit different results. The Query and data is given below.

Employee Number 'A-001' Join on '01-07-2013' then the O/P will be showing in image.

As Employee Number 'A-001' there is no promotion in 2014 then in Last_Designation,Promoted_Designation, Last_Gross and Promoted_Gross need to be same as 2013 or previous one.

CREATE TABLE [dbo].[Employee](
	[Emp_No] [numeric](18, 0) NULL,
	[Emp_Number] [nvarchar](50) NULL,
	[Emp_Name] [nvarchar](50) NULL,
	[Emp_JoiningDate] [date] NULL,
	[Emp_ResignDate] [date] NULL,
	[Emp_Status] [nvarchar](50) NULL,
	[Emp_CurrentDesignation] [nvarchar](50) NULL,
	[Emp_CurrentGross] [numeric](18, 0) NULL
) ON [PRIMARY]

GO


INSERT INTO [UserDB].[dbo].[Employee]
           ([Emp_No]
           ,[Emp_Number]
           ,[Emp_Name]
           ,[Emp_JoiningDate]
           ,[Emp_ResignDate]
           ,[Emp_Status]
           ,[Emp_CurrentDesignation]
           ,[Emp_CurrentGross])
     VALUES
           (1,'A-001','Alex','2013-07-01',null,'On Board','Trainee3',2000)
GO

INSERT INTO [UserDB].[dbo].[Employee]
           ([Emp_No]
           ,[Emp_Number]
           ,[Emp_Name]
           ,[Emp_JoiningDate]
           ,[Emp_ResignDate]
           ,[Emp_Status]
           ,[Emp_CurrentDesignation]
           ,[Emp_CurrentGross])
     VALUES
           (2,'A-002','Smith','2014-07-01','2015-07-01','Resigned','HR1',1500)
GO


CREATE TABLE [dbo].[Promotion](
	[Prom_No] [numeric](18, 0) NULL,
	[Prom_EmpNo] [numeric](18, 0) NULL,
	[Last_Designation] [nvarchar](500) NULL,
	[Promoted_Designation] [nvarchar](500) NULL,
	[WEF_Date] [date] NULL,
	[Promoted_Gross] [numeric](18, 0) NULL,
	[Last_Gross] [numeric](18, 0) NULL
) ON [PRIMARY]

GO

INSERT INTO [UserDB].[dbo].[Promotion]
           ([Prom_No]
           ,[Prom_EmpNo]
           ,[Last_Designation]
           ,[Promoted_Designation]
           ,[WEF_Date]
           ,[Promoted_Gross]
           ,[Last_Gross])
     VALUES
           (1,1,'Trainee1','Trainee2','2013-11-01',1000,500)
GO


INSERT INTO [UserDB].[dbo].[Promotion]
           ([Prom_No]
           ,[Prom_EmpNo]
           ,[Last_Designation]
           ,[Promoted_Designation]
           ,[WEF_Date]
           ,[Promoted_Gross]
           ,[Last_Gross])
     VALUES
           (2,1,'Trainee2','Trainee3','2015-03-01',2000,1000)
GO

Result should be

Thanks once again.

Thanks
Basit.