July 9, 2013 at 11:54 am
I have a table that tracks all changes to employee Roles.
Here is the table with sample data
CREATE TABLE [dbo].[Audit2](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ms_id] [nchar](10) NOT NULL,
[OldRole] [nchar](10) NULL,
[NewRole] [nchar](10) NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[Voided] [bit] NULL,
CONSTRAINT [PK_Audit2] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO AUDIT2 (ms_id,OldRole, NewRole, StartDate, EndDate, Voided)
VALUES ('XYZ',NULL, 'A','1-1-2013','1-31-2013',0)
,('XYZ',NULL,'B','2-1-2013','2-28-2013',0)
,('XYZ',NULL,'C','3-1-2013','3-31-2013',0)
,('XYZ',NULL,'D','4-1-2013','4-30-2013',0)
,('XYZ','D','E','4-1-2013','4-15-2013',0)
,('XYZ','C',NULL,'3-1-2013','3-31-2013',1)
This table basically tracks all Role changes. Voided = 1 means that entry was cancelled.
I need to generate a report that gives the roles at a point of time. for e.g. like this
Period Role
Jan 1 2013 - Jan 31 2013A
Feb 1 2013 - Feb 28 2013B
Mar 1 2013 - Mar 31 2013Voided
Apr 1 2013 - Apr 15 2013E
Apr 15 2013 - Apr 30 2013 D
Any help would be appreciated.
thanks
July 9, 2013 at 12:20 pm
Is this what you're looking for?
declare @dtmPointInTime datetime = '03/15/2013';
select ms_id, startdate, enddate, NewRole, case when voided = 1 then 'Voided' else '' end Voided
from Audit2
where @dtmPointInTime between StartDate and EndDate
order by Id;
I feel compelled to tell you that this reminds me of an HR system I worked on quite a few years ago. Management wanted to track changes to "only a few" :hehe: fields and we kept track of them with the change process in a table that looked a lot like this one. The "only a few" grew to the point where it was no longer realistic to continue this way. We ended up implementing a daily snapshot of the entire employees table with a datestamp field on it and the job to take the snapshot ran every day. We could then compare any two dates to determine the differences.
I honestly hope this doesn't happen to you here, but be prepared if it does. In the end, the snapshot approach was infinitely simpler than trying to parse through date-stamped change records and trying to build a snapshot of where the specific employee was in time.
July 9, 2013 at 12:44 pm
Can you please describe in more detail about the daily snapshot approach?
July 9, 2013 at 12:52 pm
What i was looking like is the complete history.
for e.g. this gives me 2 rows, one from 4/1-4/30 and other from 4/1-4/15
declare @dtmPointInTime datetime = '04/10/2013';
select ms_id, startdate, enddate, NewRole, case when voided = 1 then 'Voided' else '' end Voided
from Audit2
where @dtmPointInTime between StartDate and EndDate
order by Id;
instead i would like this
4/1 - 4/15 E
4/16-4/30 D
July 9, 2013 at 1:04 pm
kk1173 (7/9/2013)
Can you please describe in more detail about the daily snapshot approach?
Yes. We had an employees table that contained a lot fields. There were a lot of categorization fields plus the usual stuff like name, employment status, etc. We created an empty copy of the table and then added a snapshot_date field. Then, every day early in the morning, a database job would run that would insert all rows from the employees table into the snapshot table with the current date.
Over time, this process built up a complete history of all employees. You could select two points in time to compare how many people were in certain departments, how many titles we had, how many employees were transferred from one functional group to another, the counts of all active employees by type or nearly anything else management wanted. The key was to get the fields required to generate their reports, so we opted to do the entire table. We denormalized it in case the lookup tables changed and we kept the whole thing confined to a single snapshot table. We figured it was better to have the data we needed and spend the disk space. In the end, this proved to be the right decision because the requirements never stopped growing. The application to do the comparison was complicated, but it gave management everything they needed.
July 9, 2013 at 1:18 pm
Thanks for the info. Will keep in mind if the data grows out of hand.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply