Tech
Forums
Jobs
Books
Events
Interviews
Live
More
Learn
Training
Career
Members
Videos
News
Blogs
Login
Sign Up
Ask Question
2
Reply
How to get the employee YearWise and get the other details f
basit khan
Sep 26 2017 3:30 AM
225
Reply
Hi,
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
This DATA will come from Employee Table
This Data will come from Promotion table if there else employee table
Year
Emp_No
Em_Number
Emp_Name
Emp_Joining
Emp_Resigned
Emp_Status
Designation
Gross
2013
1
A-001
Alex
01-01-2013
24-08-2013
Resigned
Trainee
200
2013
2
A-002
Adam
01-01-2013
OnBoard
HRM Assitant
200
2014
2
A-002
Adam
01-01-2013
OnBoard
HRM
400
2014
3
A-003
Maxwel
01-01-2014
OnBoard
Accountant
300
2014
4
A-004
Smith
01-01-2014
20-08-2014
Resigned
CEO
100
2015
2
A-002
Adam
01-01-2013
OnBoard
HRM
400
2015
3
A-003
Maxwel
01-01-2014
OnBoard
FM
600
2016
2
A-002
Adam
01-01-2013
OnBoard
HRM
400
2016
3
A-003
Maxwel
01-01-2014
OnBoard
FM
600
2017
2
A-002
Adam
01-01-2013
OnBoard
HRM
400
2017
3
A-003
Maxwel
01-01-2014
OnBoard
FM
600
Thanks
Basit
Upload Source Code
Select only zip and rar file.
Post
Reset
Cancel
Answers (
2
)
Next Recommended Forum
What is the Strored Procedure in SQL Server
SQL Query:i am created a bank database