SQL query for getting conversion days between Active to inactive of employee

Question

ID EmployeeID Status EffectiveDate

 1       110545        Active        01-01-2011
 2       110700        Active        05-01-2012
 3       110060        Active        05-01-2012
 4       110222        Active        30-06-2012
 5       110222        Resigned      22-05-2016
 6       110545        Resigned      01-07-2012
 7       110545        Active        12-02-2013

How would it be possible using T-SQL to find the amount of time that has passed between when the status as "Active" and "inactive" for each employee excluding current status as "Active" for Re-joined employee.

Output should

ID     EmployeeID      Days

 1       110222     1422
 2       110545      371

Use DateDiff(), This may help: http://stackoverflow.com/a/9521452/1830909 — QMaster, Mar 14 '17 at 07:59
I don't get what you want, can you show the expected result and explain it? — dnoeth, Mar 14 '17 at 08:09
For Emp 110545 , i need number of days conversion from active to inactive w.r.t current status.. so out of for emp 110545 should be 01-01-2011 (ActiveDate) to 01-07-2012(resigned date) — Ashvin, Mar 14 '17 at 08:12
You know what *number of days conversion from active to inactive w.r.t current status* means, but I don't. — dnoeth, Mar 14 '17 at 08:14
in conversion report i want to display active to inactive duration for each employees thats it..for this emp 110545 i am having mutiple status.. @dnoeth — Ashvin, Mar 14 '17 at 08:32
see the link referenced to know on how to ask a perfect question:https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — TheGameiswar, Mar 14 '17 at 10:17

score 1 · Answer 1 · answered Mar 16 '17 at 15:26

One approach is to search for all Resigned status and then use a cross apply to find the previous Active status, like this:

declare @Emp table (ID int, EmployeeID int, Status varchar(8), EffectiveDate date)
insert into @Emp (ID, EmployeeID, Status, EffectiveDate) values
(1, 110545, 'Active', '2011-01-01'),
(2, 110700, 'Active', '2012-01-05'),
(3, 110060, 'Active', '2012-01-05'),
(4, 110222, 'Active', '2012-06-30'),
(5, 110222, 'Resigned', '2016-05-22'),
(6, 110545, 'Resigned', '2012-07-01'),
(7, 110545, 'Active', '2013-02-12')


select
    row_number() over (order by EmployeeID) as ID,
    e.EmployeeID,
    datediff(dd, e2.EffectiveDate, e.EffectiveDate) as Days
from @Emp as e
cross apply
(
    select top 1 e2.EffectiveDate
    from @Emp as e2
    where e.EmployeeID = e2.EmployeeID and e2.EffectiveDate < e.EffectiveDate
    order by EffectiveDate desc
) as e2
where e.Status = 'Resigned'

Results

ID     EmployeeID      Days
 1       110222       1422
 2       110545        547*

*Your sample EffectiveDate data is formatted in DD-MM-YYYY

After creating table when i run select query statement its show error msg :Msg 208, Level 16, State 1, Line 1 Invalid object name 'EMployeeID'. — Ashvin, Mar 17 '17 at 07:18

SimarjeetSingh Panghlia · Accepted Answer · 2017-03-20T07:32:27.430

0

Try This

 select *, DATEDIFF(day, date, todate) as totalday from 
(select e.employeeid,e.status,e.date,MIN(em.date) as todate from tbl_emp e
left join tbl_emp em on e.date < em.date and e.employeeid = em.employeeid
where e.status = 'active'
group by e.employeeid,e.status,e.date
having MIN(em.date) is not null 
)
as m
order by date

edited Mar 20 '17 at 07:32

answered Mar 14 '17 at 08:04

SimarjeetSingh Panghlia

2,200
13
14

thanks simarjeet it helps a lot ,but i hv modified my question w.r.t output please look into it. – Ashvin Mar 17 '17 at 07:22
Simarjeet, Expected Output should be :- ID EmployeeID Days 1 110222 1422 2 110545 547* – Ashvin Mar 17 '17 at 14:51

SQL query for getting conversion days between Active to inactive of employee

2 Answers2