October 29, 2015 at 8:17 am
I have a table that contains an employee id and dates signifying time periods that those employees were working. I need to calculate anniversaries, such as 20 year, which are the sum of all periods spend working projected out to 20 years. For example,
emp_idstart_date end_date
1001 1998-01-01 2003-06-21
1002 1999-05-23 2008-03-28
1001 2004-08-19 NULL
1003 2004-10-12 2006-07-25
1004 2005-04-28 NULL
1002 2008-11-02 NULL
1003 2009-05-17 NULL
The periods in which the employees were inactive (the time period between active ranges) would push back their anniversary date, obviously. I'm only concerned with employees that are currently active (ie. the most recent record has a NULL end date). I thought about trying to use datediff to calculate the time between active periods, but I'm not sure how to go about doing it. Any thoughts?
October 29, 2015 at 8:24 am
Can you give expected outcome please?
If I understand, the output should be
empid, years_service
1001, 11
1004, 10
1002, 7
1003, 6
create table #test (empid int, start_date datetime, end_date datetime)
insert into #test values
(1001,'1998-01-01','2003-06-21'),
(1002,'1999-05-23','2008-03-28'),
(1001,'2004-08-19',NULL),
(1003,'2004-10-12','2006-07-25'),
(1004,'2005-04-28',NULL),
(1002,'2008-11-02',NULL),
(1003,'2009-05-17',NULL)
select
empid,
start_date,
datediff(year, start_date, getdate())
from
#test
where
end_date IS NULL
drop table #test
October 29, 2015 at 8:25 am
Mark Harley (10/29/2015)
I have a table that contains an employee id and dates signifying time periods that those employees were working. I need to calculate anniversaries, such as 20 year, which are the sum of all periods spend working projected out to 20 years. For example,emp_idstart_date end_date
1001 1998-01-01 2003-06-21
1002 1999-05-23 2008-03-28
1001 2004-08-19 NULL
1003 2004-10-12 2006-07-25
1004 2005-04-28 NULL
1002 2008-11-02 NULL
1003 2009-05-17 NULL
The periods in which the employees were inactive (the time period between active ranges) would push back their anniversary date, obviously. I'm only concerned with employees that are currently active (ie. the most recent record has a NULL end date). I thought about trying to use datediff to calculate the time between active periods, but I'm not sure how to go about doing it. Any thoughts?
when you say "time difference" can you please be specific in what your expected result set is to be based on this sample data.....
is it years... years/mths....no of days.....HH:MM:SS??
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 29, 2015 at 8:46 am
The expected outcomes would be a specific date for each employee. For example, employee 1001 would have a projected 20-year date of 2018-01-01 plus the number of days they were inactive (374 I believe), which would push the date to 2019-10-01. Is that any clearer?
October 29, 2015 at 9:03 am
So a rolling total of sorts?
So based on the sample data of
create table #test (empid int, start_date datetime, end_date datetime)
insert into #test values
(1001,'1998-01-01','2003-06-21'),
(1001,'2004-08-19',NULL),
(1002,'1999-05-23','2008-03-28'),
(1002,'2008-11-02',NULL),
(1003,'2004-10-12','2006-07-25'),
(1003,'2009-05-17',NULL),
(1004,'2005-04-28',NULL)
You want the output of
1001 - 2019-03-02
1002 - 2019-12-28
1003 - 2027-08-05
1004 - 2025-04-28
October 29, 2015 at 9:10 am
Yes, exactly.
October 29, 2015 at 9:15 am
First itteration.
Looks a bit ugly, sure someone can tidy it up or suggest another way
;with cte as
(
select empid, datediff(day,end_date,start_date) as inactivedays from
(
select
empid, start_date,
case when end_date is null then (select max(end_date) from #test t where t.end_date < t1.start_date and t.empid = t1.empid)
else end_date
end as end_date
from #test t1
) as d
where datediff(day,end_date,start_date) > 0
)
select
#test.empid,
dateadd(day, isnull(inactivedays,0), dateadd(year, 20, min(start_date)))
from
#test
left join cte
on #test.empid = cte.empid
group by #test.empid, inactivedays
October 29, 2015 at 9:18 am
This should give you what you want. What we need to do is simply sum the total months of service for each employee who is still active (has one record where end_date is null), and then divide by 12. Or perhaps sum days of service and divide by 365.
create table #test (empid int, start_date datetime, end_date datetime)
insert into #test values
(1001,'1998-01-01','2003-06-21'),
(1002,'1999-05-23','2008-03-28'),
(1001,'2004-08-19',NULL),
(1003,'2004-10-12','2006-07-25'),
(1004,'2005-04-28',NULL),
(1002,'2008-11-02',NULL),
(1003,'2009-05-17',NULL)
select empid
, (sum( datediff(month, start_date, isnull(end_date,getdate())) ) / 12)YearsServive
from #test
where empid in (select empid from #test where end_date is null)
group by empid;
drop table #test;
empidYearsServive
1001 16
1002 15
1003 8
1004 10
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 29, 2015 at 10:34 am
my shot at this...first glance suggests I get different results from anthony
but I have added and addtional row
create table #test (empid int, start_date datetime, end_date datetime)
insert into #test values
(1001,'1998-01-01','2003-06-21'),
(1001,'2004-01-01','2004-06-15'), -- note added here
(1001,'2004-08-19',NULL),
(1002,'1999-05-23','2008-03-28'),
(1002,'2008-11-02',NULL),
(1003,'2004-10-12','2006-07-25'),
(1003,'2009-05-17',NULL),
(1004,'2005-04-28',NULL);
WITH d1 as (
SELECT
empid
, start_date
, ROW_NUMBER() OVER (PARTITION BY empid ORDER BY start_date) AS rn
, DATEDIFF(d , lag(end_date , 1 , 0) OVER (PARTITION BY empid ORDER BY start_date) , start_date) AS inactivedays
FROM #test
)
, d2 as (
SELECT
empid
, SUM(inactivedays) AS totalinactive
FROM d1
WHERE (rn > 1)
GROUP BY
empid
)
SELECT
#test.empid
, MIN(#test.start_date) AS sdate
, ISNULL(DATEADD(year , 20 , DATEADD(day , d2.totalinactive , MIN(#test.start_date))) , DATEADD(year , 20 , MIN(#test.start_date))) AS odate
FROM #test LEFT OUTER JOIN
d2 ON #test.empid = d2.empid
GROUP BY
#test.empid
, d2.totalinactive
ORDER BY
#test.empid;
drop table #test
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
October 29, 2015 at 12:26 pm
If the goal is just to take the employee's original start date, add 20 years, and then add any days they were inactive, then here's another way, using J Livingston's new sample data:
create table #test (empid int, start_date datetime, end_date datetime)
insert into #test values
(1001,'1998-01-01','2003-06-21'),
(1001,'2004-01-01','2004-06-15'), -- note added here
(1001,'2004-08-19',NULL),
(1002,'1999-05-23','2008-03-28'),
(1002,'2008-11-02',NULL),
(1003,'2004-10-12','2006-07-25'),
(1003,'2009-05-17',NULL),
(1004,'2005-04-28',NULL);
SELECT DATEADD(DD,DATEDIFF(DD, MIN(start_date), GETDATE())-SUM(DATEDIFF(DD,start_date, ISNULL(end_date,getdate()))), DATEADD(YYYY,20,MIN(start_date))) AS Anniversary, empid
FROM #test
GROUP BY empid;
drop table #test
Cheers!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply