November 1, 2016 at 3:51 am
Hi,
I currently have an audit table and I want to query this table and put it into a SCD Type 2 table but having trouble with the sql.
There are two tables:
hr_audit - which holds any changes to an employee
hrd_person - which holds the start date and termination date for an employee
Below is script for both tables starting with hr_audit
CREATE TABLE [dbo].[hrd_Audit](
[PERSON_ID] [numeric](18, 0) NULL,
[FULL_NAME] [varchar](240) NULL,
[TIME_STAMP] [datetime] NULL,
[USER_NAME] [varchar](240) NULL,
[TRANSACTION_TYPE] [varchar](30) NULL,
[EFFECTIVE_DATE] [datetime] NULL,
[BASE_TABLE_NAME] [varchar](50) NULL,
[COLUMN_NAME] [varchar](50) NULL,
[OLD_VALUE] [varchar](240) NULL,
[NEW_VALUE] [varchar](240) NULL,
[PROCESSED_FLAG] [varchar](10) NULL
) ON [PRIMARY]
GO
insert into hrd_Audit values (6260,'Joe Bloggs','2006-05-09 12:47:58','MIKVAD','UPDATE','2006-05-01 00:00:00','REP_ALL_EXAM_S','ORGANIZATION','Cleaner','Warehouse ','DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2006-05-09 12:47:58','MIKVAD','UPDATE','2006-05-01 00:00:00','REP_ALL_EXAM_S','SUPERVISOR','Mike Tyson','Donald Trump','DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2006-05-09 12:47:58','MIKVAD','UPDATE','2006-05-01 00:00:00','REP_ALL_EXAM_S','SUPERVISOR EMPLOYEE NUMBER','8000146','8091052','DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2006-05-09 12:47:58','MIKVAD','UPDATE','2006-05-01 00:00:00','REP_ALL_EXAM_S','JOB','Operations|Developer|Operations Developer','It|Analyst|.','DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2006-05-09 12:47:58','MIKVAD','UPDATE','2006-05-01 00:00:00','REP_ALL_EXAM_S','JOB TYPE','Operations Data Analyst Trump House','Data Analyst','DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2006-05-09 15:33:02','MIKVAD','DELETION',NULL,'REP_ALL_EXAM_S','ORGANIZATION','Computer Systems Control Services',NULL,'DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2006-05-09 15:33:02','MIKVAD','DELETION',NULL,'REP_ALL_EXAM_S','SUPERVISOR EMPLOYEE NUMBER','8089085',NULL,'DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2006-05-09 15:33:02','MIKVAD','DELETION',NULL,'REP_ALL_EXAM_S','SUPERVISOR','Michael Todd',NULL,'DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2006-05-09 15:33:02','MIKVAD','DELETION',NULL,'REP_ALL_EXAM_S','JOB','It|Analyst|.',NULL,'DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2006-05-09 15:33:02','MIKVAD','DELETION',NULL,'REP_ALL_EXAM_S','JOB TYPE','Data Analyst',NULL,'DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2006-05-09 15:33:02','MIKVAD','DELETION',NULL,'REP_ALL_EXAM_S','LOCATION','James House',NULL,'DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2006-06-08 14:59:37','MIKVAD','UPDATE','2006-06-30 00:00:00','REP_ALL_EXAM_S','SUPERVISOR EMPLOYEE NUMBER','1000641','1019025','DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2006-06-08 14:59:21','MIKVAD','UPDATE','2006-06-30 00:00:00','REP_ALL_EXAM_S','ORGANIZATION','Inventory Planning','Computer Systems Control Services','DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2006-06-08 14:59:21','MIKVAD','UPDATE','2006-06-30 00:00:00','REP_ALL_EXAM_S','JOB','Operations|Developer|Operations Developer','IT|Analyst|.','DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2006-06-08 14:59:21','MIKVAD','UPDATE','2006-06-30 00:00:00','REP_ALL_EXAM_S','JOB TYPE','Operations Data Analyst Trump House',NULL,'DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2006-06-08 14:59:37','MIKVAD','UPDATE','2006-06-30 00:00:00','REP_ALL_EXAM_S','SUPERVISOR','Mike Tyson','Donald Trump','DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2006-06-08 15:00:57','MIKVAD','UPDATE','2006-06-30 00:00:00','REP_ALL_EXAM_S','JOB','IT|Analyst|.','It|Analyst|.','DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2006-06-08 15:00:57','MIKVAD','UPDATE','2006-06-30 00:00:00','REP_ALL_EXAM_S','JOB TYPE',NULL,'Data Analyst','DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2006-06-08 15:00:57','MIKVAD','UPDATE','2006-06-30 00:00:00','REP_ALL_EXAM_S','LOCATION','Trump House','Brooklyn','DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2006-06-08 15:01:08','MIKVAD','UPDATE','2006-06-30 00:00:00','REP_ALL_EXAM_S','LOCATION','Brooklyn','Trump House','DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2007-08-23 11:45:00','ZAHMAR','UPDATE','2007-08-14 00:00:00','REP_ALL_EXAM_S','ORGANIZATION','Computer Systems Control Services','Trump IT Operations Information','DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2007-08-23 11:45:00','ZAHMAR','UPDATE','2007-08-14 00:00:00','REP_ALL_EXAM_S','JOB','It|Analyst|.','Technical|Analyst|.','DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2008-11-12 12:55:12','GORBLA','UPDATE','2008-11-12 00:00:00','REP_ALL_EXAM_S','LOCATION','Trump House','Trinity Park','DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2009-11-05 09:39:21','MIKVAD','UPDATE','2009-11-01 00:00:00','REP_ALL_EXAM_S','JOB TYPE','Data Analyst','SQL Developer','DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2010-11-08 12:54:47','STETOD','UPDATE','2010-11-01 00:00:00','REP_ALL_EXAM_S','SUPERVISOR','Donald Trump','Pinal Dave','DONE')
insert into hrd_Audit values (6260,'Joe Bloggs','2010-11-08 12:54:47','STETOD','UPDATE','2010-11-01 00:00:00','REP_ALL_EXAM_S','SUPERVISOR EMPLOYEE NUMBER','8089085','8088184','DONE')
Table for hrd_person
CREATE TABLE [dbo].[hrd_person](
[PERSON_ID] [int] NOT NULL,
[User_ID] [int] NOT NULL,
[Start_Date] [datetime] NULL,
[Termination_Date] [datetime] NULL,
CONSTRAINT [PK_hrd_person] PRIMARY KEY CLUSTERED
(
[PERSON_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into hrd_person values (6260,12156,'2003-01-01 00:00:00.000',NULL)
insert into hrd_person values (6280,12346,'2012-08-01 00:00:00.000','2016-08-01 00:00:00.000')
I then want my sql query to return the below results, this is based on getting the Start_Date from the table hrd_person and use this as the startDate for their first Job.
And then use the conditions for the other jobs from the table hrd_Audit based on the below:
PERSON_ID = 6260
and COLUMN_NAME in ('JOB TYPE')
and TRANSACTION_TYPE != 'DELETION'
and NEW_VALUE is not null
Select x.*
From (
Select 6260 as personid, 'Operations Data Analyst Trump House' as jobTitle, '2003-01-01 00:00:00.000' as startDate, '2006-04-30 00:00:00.000' as endDate
union
Select 6260 as personid, 'Data Analyst' as jobTitle, '2006-05-01 00:00:00.000' as startDate, '2006-06-29 00:00:00.000' as endDate
union
Select 6260 as personid, 'Data Analyst' as jobTitle, '2006-06-30 00:00:00.000' as startDate, '2009-10-31 00:00:00.000' as endDate
union
Select 6260 as personid, 'SQL Developer' as jobTitle, '2009-11-01 00:00:00.000' as startDate, null as endDate
) x
order by 3
November 1, 2016 at 8:33 am
I think the best way to handle this will be a self join, probably on a temp table that has just the relevant rows from the audit table, maybe something like:
CREATE TABLE #person_jobs (
person_id int,
effective_date datetime,
old_job varchar(240),
job_type varchar(240),
sort int,
PRIMARY KEY (person_id, sort));
INSERT INTO #person_jobs
(person_id, effective_date, old_job, job_type, sort)
SELECT a.person_id, a.effective_date, a.old_value, a.new_value, ROW_NUMBER() OVER (PARTITION BY a.person_id ORDER by a.effective_date) AS sort
FROM dbo.hrd_Audit a
WHERE a.column_name in ('JOB TYPE')
AND a.transaction_type != 'DELETION'
AND a.new_value IS NOT NULL;
SELECT p.person_id AS PersonID, j.old_job AS JobTitle, p.start_date AS StartDate, DATEADD(day, -1, j.effective_date) AS EndDate
FROM dbo.hrd_Person p
INNER JOIN #person_jobs j ON p.person_id = j.person_id AND j.sort = 1
WHERE p.person_id = 6260
UNION ALL
SELECT j1.person_id, j1.job_type AS JobTitle, j1.effective_date AS StartDate, DATEADD(day, -1, j2.effective_date) AS EndDate
FROM #person_jobs j1
LEFT OUTER JOIN #person_jobs j2 ON j1.person_id = j2.person_id AND j1.sort+1 = j2.sort
DROP TABLE #person_jobs
November 1, 2016 at 4:06 pm
Thanks Chris for your reply, it looks like it has done the job! But I did notice that when I inserted another row into the table hrd_audit
insert into hrd_Audit values (6280,'Lionel Messi','2012-10-01 00:00:00.000','SOMESO','UPDATE','2012-10-12 00:00:00.000','REP_ALL_EXAM_S','JOB TYPE',NULL,'Temp ','DONE')
And then comment out the condition "WHERE p.person_id = 6260", for the end result the PersonID (6280) has two rows.
I want if possible to have one row for PersonId (6280) and to have the EndDate for this personid to be '2016-08-01 00:00:00.000' as it has a Termination_Date for this person_id in the table hrd_Person
Thanks
November 2, 2016 at 7:41 am
You could easily make the first record reflect the termination date by doing this:
SELECT p.person_id AS PersonID, j.old_job AS JobTitle, p.start_date AS StartDate, ISNULL(p.Termination_Date, DATEADD(day, -1, j.effective_date)) AS EndDate
FROM dbo.hrd_Person p
LEFT OUTER JOIN #person_jobs j ON p.person_id = j.person_id AND j.sort = 1
--WHERE p.person_id = 6260
UNION ALL
SELECT j1.person_id, j1.job_type AS JobTitle, j1.effective_date AS StartDate, DATEADD(day, -1, j2.effective_date) AS EndDate
FROM #person_jobs j1
LEFT OUTER JOIN #person_jobs j2 ON j1.person_id = j2.person_id AND j1.sort+1 = j2.sort;
essentially showing termination date if there is one, otherwise looking for the first job type change. I'm not sure what your business rules are about how to deal with the Temp job record for 6280 in hrd_Audit that starts 2012-10-12 though. I'm also not sure how you would handle if this person was terminated again from the Temp job, since you only track one termination date, at the person level.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply