February 17, 2021 at 7:08 pm
I am wanting to return only one row for continuous employment, what is the indcator is there WORKTYPE, RELATIONSHIPSTATUS and BEGINDATE. The bolded line is the BEGINDATE I want for Employee 100043, Employee 105640 has ENDDATE of 9999-12-31 which will be GETDATE, The attach image is of the results as they should be returned.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
EMPLOYEE INT, --Is an IDENTITY column on real table
RELATIONSHIPSTATUS VARCHAR,
WORKTYPE VARCHAR,
DATETIME,
ENDDATE DATETIME
)
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #mytable ON
--===== Insert the test data into the test table
INSERT INTO #mytable
(EMPLOYEE,RELATIONSHIPSTATUS,WORKTYPE,BEGINDATE,ENDDATE)
SELECT '100019','ACTIVE','SEASONAL','2012-04-28,00:00:00.000','2017-06-02,00:00:00.000'
SELECT '100019','ACTIVE','SEASONAL','2017-06-02,00:00:00.000','2017-08-26,00:00:00.000'
SELECT '100019','TERMINATED','SEASONAL','2017-08-26,00:00:00.000','2018-01-01,00:00:00.000'
SELECT '100019','ACTIVE','SEASONAL','2018-01-01,00:00:00.000','2018-02-01,00:00:00.000'
SELECT '100019','ACTIVE','SEASONAL','2018-02-01,00:00:00.000','2019-01-01,00:00:00.000'
SELECT '100019','ACTIVE','SEASONAL','2019-01-01,00:00:00.000','2019-08-21,00:00:00.000'
SELECT '100019','ACTIVE','SEASONAL','2019-08-21,00:00:00.000','2019-08-30,00:00:00.000'
SELECT '100019','ACTIVE','SEASONAL','2019-08-30,00:00:00.000','9999-12-31,00:00:00.000'
SELECT '100043','ACTIVE','SEASONAL','2016-05-23,00:00:00.000','2017-05-15,00:00:00.000'
SELECT '100043','ACTIVE','SEASONAL','2017-05-15,00:00:00.000','2017-08-26,00:00:00.000'
SELECT '100043','TERMINATED','SEASONAL','2017-08-26,00:00:00.000','2018-05-23,00:00:00.000'
SELECT '100043','ACTIVE','SEASONAL','2018-05-23,00:00:00.000','2018-06-05,00:00:00.000'
SELECT '100043','ACTIVE','SEASONAL','2018-06-05,00:00:00.000','2018-11-04,00:00:00.000'
SELECT '100043','TERMINATED','SEASONAL','2018-11-04,00:00:00.000','2019-01-01,00:00:00.000'
SELECT '100043','ACTIVE','SEASONAL','2019-01-01,00:00:00.000','2019-08-21,00:00:00.000'
SELECT '100043','ACTIVE','SEASONAL','2019-08-21,00:00:00.000','2019-08-30,00:00:00.000'
SELECT '100043','ACTIVE','SEASONAL','2019-08-30,00:00:00.000','2020-01-01,00:00:00.000'
SELECT '100043','ACTIVE','PART,TIME','2020-01-01,00:00:00.000','2020-06-01,00:00:00.000'
SELECT '100043','ACTIVE','SEASONAL','2020-06-01,00:00:00.000','2020-06-15,00:00:00.000'
SELECT '100043','ACTIVE','SEASONAL','2020-06-15,00:00:00.000','9999-12-31,00:00:00.000'
SELECT '105640','ACTIVE','SEASONAL','2020-01-01,00:00:00.000','9999-12-31,00:00:00.000'
--=====,Set,the,identity,insert,back,to,normal
SET IDENTITY_INSERT #mytable OFF
February 17, 2021 at 10:43 pm
There were a few issues with the data which required fixing before a query could run. The VARCHAR columns require a character length, in this case I made them both VARCHAR(20). The datetime columns have an extra comma which I replaced with a space. With these fixes the code calculates the difference in days between the begindate and enddate of the most recent employment. I couldn't match the numbers in results.png but maybe this gets the ball rolling. Maybe you could explain the required calculation a little more.
drop table if exists #mytable;
go
create table #mytable(
EMPLOYEE INT, --Is an IDENTITY column on real table
RELATIONSHIPSTATUS VARCHAR(20),
WORKTYPE VARCHAR(20),
BEGINDATE DATETIME,
ENDDATE DATETIME);
--SET DATEFORMAT DMY
--SET IDENTITY_INSERT #mytable ON;
INSERT INTO #mytable(EMPLOYEE,RELATIONSHIPSTATUS,WORKTYPE,BEGINDATE,ENDDATE) values
('100019','ACTIVE','SEASONAL','2012-04-28 00:00:00.000','2017-06-02 00:00:00.000'),
('100019','ACTIVE','SEASONAL','2017-06-02 00:00:00.000','2017-08-26 00:00:00.000'),
('100019','TERMINATED','SEASONAL','2017-08-26 00:00:00.000','2018-01-01 00:00:00.000'),
('100019','ACTIVE','SEASONAL','2018-01-01 00:00:00.000','2018-02-01 00:00:00.000'),
('100019','ACTIVE','SEASONAL','2018-02-01 00:00:00.000','2019-01-01 00:00:00.000'),
('100019','ACTIVE','SEASONAL','2019-01-01 00:00:00.000','2019-08-21 00:00:00.000'),
('100019','ACTIVE','SEASONAL','2019-08-21 00:00:00.000','2019-08-30 00:00:00.000'),
('100019','ACTIVE','SEASONAL','2019-08-30 00:00:00.000','9999-12-31 00:00:00.000'),
('100043','ACTIVE','SEASONAL','2016-05-23 00:00:00.000','2017-05-15 00:00:00.000'),
('100043','ACTIVE','SEASONAL','2017-05-15 00:00:00.000','2017-08-26 00:00:00.000'),
('100043','TERMINATED','SEASONAL','2017-08-26 00:00:00.000','2018-05-23 00:00:00.000'),
('100043','ACTIVE','SEASONAL','2018-05-23 00:00:00.000','2018-06-05 00:00:00.000'),
('100043','ACTIVE','SEASONAL','2018-06-05 00:00:00.000','2018-11-04 00:00:00.000'),
('100043','TERMINATED','SEASONAL','2018-11-04 00:00:00.000','2019-01-01 00:00:00.000'),
('100043','ACTIVE','SEASONAL','2019-01-01 00:00:00.000','2019-08-21 00:00:00.000'),
('100043','ACTIVE','SEASONAL','2019-08-21 00:00:00.000','2019-08-30 00:00:00.000'),
('100043','ACTIVE','SEASONAL','2019-08-30 00:00:00.000','2020-01-01 00:00:00.000'),
('100043','ACTIVE','PART,TIME','2020-01-01 00:00:00.000','2020-06-01 00:00:00.000'),
('100043','ACTIVE','SEASONAL','2020-06-01 00:00:00.000','2020-06-15 00:00:00.000'),
('100043','ACTIVE','SEASONAL','2020-06-15 00:00:00.000','9999-12-31 00:00:00.000'),
('105640','ACTIVE','SEASONAL','2020-01-01 00:00:00.000','9999-12-31 00:00:00.000');
--SET IDENTITY_INSERT #mytable OFF;
with
terminated(employee, start_dt) as (
select employee,
isnull(max(case when relationshipstatus='TERMINATED'
then enddate
else null end),
min(begindate))
from #mytable
group by employee),
active(employee, begindate, enddate, start_dt) as (
select m.employee, m.begindate, m.enddate, t.start_dt
from #mytable m
left join terminated t on m.employee=t.employee
and m.begindate>=t.start_dt)
select employee, min(begindate) cont_emp,
sum(datediff(day, begindate, calc_end_dt)) cont_days
from active
cross apply (values (case when enddate='9999-12-31 00:00:00.000'
then begindate
else enddate end)) v(calc_end_dt)
where start_dt is not null
group by employee;
employeecont_empcont_days
1000192018-01-01 00:00:00.000606
1000432019-01-01 00:00:00.000531
1056402020-01-01 00:00:00.0000
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply