Count Total Experience of a employee in sql query

Question

How to write a sql query to get a employees total experience [Previous companies exp. and current company experience]. I have only two columns in sql tables. Joining date and leaving dates. Plzz solve my query as per as soon possible.

Rajesh Pathakoti · Answer

SELECT DATEDIFF(DAYJoiningDateLeavingDate) then it will return in Days SELECT DATEDIFF(YYJoiningDateLeavingDate) then it will return in Years

Rajesh Pathakoti · Answer

Then take case statement and check if the Emp in employee previous exprience details table  if he not there then show current exp as a total exp. For ex: EmpPresentCompDtls- EmpIDEmpName JoiningDateLeavingDate EmpPrevoisCompDtls- EmpIDPreviousComp JoiningDateLeavingDate select EmpIdEmpName(case when e1.EmpId =e2.EmpId then ( SELECT DATEDIFF(YYe2.JoiningDatee2.LeavingDate)+ DATEDIFF(YYe1.JoiningDatee1.LeavingDate) ) else ( SELECT DATEDIFF(YYe1.JoiningDatee1.LeavingDate)) end) as TotalExp from EmpPresentCompDtls e1 EmpPrevoisCompDtls e2 where e1.EmpId=e2.EmpId May be Sytax errors check once but the logic is correct

Ashu Dhaundiyal · Answer

I agree with you but this is not my query..... Let me explain all the query... I have a table that contain employee information such as EmpIDEmpName JoiningDate etc.... I have one another table that contain employee previous exprience details such as Emp_previous_CompanyJoining_DateLeaving_DateLast_CTC etc... I have a grid view like that.... ----------------------------------------------------------------------------------------------------- EmpID EmpName Desig Deptt DOJ Currt_Exp Total_Exp ----------------------------------------------------------------------------------------------------- 101 Ramesh Developer IT 13 Jan 2013 01 Yr 6 M Blank 101 Sureh Developer IT 01 Nov 2012 01 Yr 8 M 5 Yr 102 Mohan Programmer IT 01 Nov 2011 02 Yr 8 M Blank 103 Sohan Web Design IT 01 Nov 2010 03 Yr 8 M 5 Yr 104 Mukesh Network IT 01 Nov 2009 04 Yr 8 M 5 Yr ----------------------------------------------------------------------------------------------------- If Total_Exp coloum is Blank than it mean that employee have not any previous companies work experience. My question is if Total_Exp column is Blank than total_Exp value is current_exp....... How to write a query....... Plz plz solve it.......