Search code examples
sqlsql-serverssmsdatediffssms-2012

Sum all the previous work experiences (into months) where rows are dynamic (1 row for 1 experience) and different for each employee

I have a table which stores work experience history of employees where each experience is stored as 1 row.

TblWorkExp :

--------------------------------------------------
ID      | ExperienceID |  From_Date | To_Date    |
--------------------------------------------------
EMP001  |      1       |  1-Jan-18  | 31-Dec-18  |
--------------------------------------------------
EMP001  |      2       |  1-Jan-19  | 31-May-19  |
--------------------------------------------------
EMP001  |      3       |  1-Jun-19  | 31-Dec-19  |
--------------------------------------------------
EMP002  |      1       |  1-Jan-19  | 28-Feb-19  |
--------------------------------------------------
EMP002  |      2       |  1-Apr-19  | 31-May-19  |
--------------------------------------------------

My Output should be 

---------------------------
ID      | Total Experience|  
---------------------------
EMP001  |      24 Months  |  
---------------------------
EMP002  |      04 Months  | 
---------------------------

What I had done initially was calculating datediff months between
Min(ExperienceID) from_date and max(ExperienceID) to_date which would give me results if there are no gaps between jobs

Select ExpMin.ID, datediff(month,ExpMin.From_date ,ExpMax.To_Date) as 'Total Experience'
 from TblWorkExp ExpMin, TblWorkExp ExpMax 
where 
ExpMin.ID = ExpMax.ID and
ExpMin.From_date = (select min(a.From_date) from TblWorkExp a where ExpMin.ID = a.ID )and
ExpMax.To_date = (select Max(a.To_date) from TblWorkExp a where ExpMax.ID = a.ID )

It works fine when there are no gaps between jobs. I need my results like

---------------------------
ID      | Total Experience|  
---------------------------
EMP001  |      24 Months  |  
---------------------------
EMP002  |      04 Months  | 
---------------------------
Solution

  • Here's your query.

    select ID, sum(datediff(month, from_date, to_date)) As [Total Experience] from TblWorkExp 
group by ID