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

basit0079 · September 26, 2017, 4:56am

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.

bitsmed · September 27, 2017, 3:46pm

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
;

basit0079 · September 28, 2017, 9:16am

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.

bitsmed · September 28, 2017, 5:06pm

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).

basit0079 · October 4, 2017, 6:57am

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.