Post
Ask Question
Basit Khan

Basit Khan

  • NA
  • 336
  • 122.5k

How to get the employee YearWise and get the other details f

Sep 26 2017 3:30 AM
Hi,
 
How to get the employee YearWise and get the other details from Promotion table accordingly.
 
The employee Table is given below.
  1. CREATE TABLE [dbo].[Employee](  
  2. [Emp_No] [numeric](18, 0) NULL,  
  3. [Emp_Number] [nvarchar](50) NULL,  
  4. [Emp_Name] [nvarchar](50) NULL,  
  5. [Emp_JoiningDate] [date] NULL,  
  6. [Emp_ResignDate] [date] NULL,  
  7. [Emp_Status] [nvarchar](50) NULL,  
  8. [Emp_Designation] [nvarchar](50) NULL,  
  9. [Emp_Gross] [numeric](18, 0) NULL  
  10. ) ON [PRIMARY]  
  11. GO  
  12. the Data is  
  13. INSERT INTO [Employee]  
  14. ([Emp_No]  
  15. ,[Emp_Number]  
  16. ,[Emp_Name]  
  17. ,[Emp_JoiningDate]  
  18. ,[Emp_ResignDate]  
  19. ,[Emp_Status],Emp_Designation,Emp_Gross)  
  20. VALUES(  
  21. 1  
  22. ,'A-001'  
  23. ,'Alex'  
  24. ,'2013-01-01'  
  25. ,'2013-08-24'  
  26. ,'Resigned','Trainee',200)  
  27. GO  
  28. INSERT INTO [Employee]  
  29. ([Emp_No]  
  30. ,[Emp_Number]  
  31. ,[Emp_Name]  
  32. ,[Emp_JoiningDate]  
  33. ,[Emp_ResignDate]  
  34. ,[Emp_Status],Emp_Designation,Emp_Gross)  
  35. VALUES(  
  36. 2  
  37. ,'A-002'  
  38. ,'Adam'  
  39. ,'2013-01-01'  
  40. ,null  
  41. ,'On Board','HRM',400)  
  42. GO  
  43. INSERT INTO [Employee]  
  44. ([Emp_No]  
  45. ,[Emp_Number]  
  46. ,[Emp_Name]  
  47. ,[Emp_JoiningDate]  
  48. ,[Emp_ResignDate]  
  49. ,[Emp_Status],Emp_Designation,Emp_Gross)  
  50. VALUES(  
  51. 3  
  52. ,'A-003'  
  53. ,'Maxwell'  
  54. ,'2014-01-01'  
  55. ,null  
  56. ,'On Board','FM',600)  
  57. GO  
  58. INSERT INTO [Employee]  
  59. ([Emp_No]  
  60. ,[Emp_Number]  
  61. ,[Emp_Name]  
  62. ,[Emp_JoiningDate]  
  63. ,[Emp_ResignDate]  
  64. ,[Emp_Status],Emp_Designation,Emp_Gross)  
  65. VALUES(  
  66. 4  
  67. ,'A-004'  
  68. ,'Smith'  
  69. ,'2014-01-01'  
  70. ,'2014-08-20'  
  71. ,'Resigned','CEO',1000)  
  72. GO  
  73. Promotion Table structure is  
  74. CREATE TABLE [dbo].[Promotion](  
  75. [Prom_No] [numeric](18, 0) NULL,  
  76. [Prom_EmpNo] [numeric](18, 0) NULL,  
  77. [Last_Designation] [nvarchar](500) NULL,  
  78. [Promoted_Designation] [nvarchar](500) NULL,  
  79. [WEF_Date] [date] NULL,  
  80. [Promoted_Gross] [numeric](18, 0) NULL,  
  81. [Last_Gross] [numeric](18, 0) NULL  
  82. ) ON [PRIMARY]  
  83. GO  
  84. the Data is  
  85. INSERT INTO [Promotion]  
  86. ([Prom_No]  
  87. ,[Prom_EmpNo]  
  88. ,[Last_Designation]  
  89. ,[Promoted_Designation]  
  90. ,[WEF_Date]  
  91. ,[Promoted_Gross]  
  92. ,[Last_Gross])  
  93. VALUES  
  94. (1,2,'HRM Assitant','HRM','2014-01-01',400,200)  
  95. GO  
  96. INSERT INTO [Promotion]  
  97. ([Prom_No]  
  98. ,[Prom_EmpNo]  
  99. ,[Last_Designation]  
  100. ,[Promoted_Designation]  
  101. ,[WEF_Date]  
  102. ,[Promoted_Gross]  
  103. ,[Last_Gross])  
  104. VALUES  
  105. (2,3,'Accountant','FM','2015-01-01',600,300)  
  106. 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

Answers (2)