Return to site

Lawyers in 9999

3E data effective records and referential integrity

3E maintains date effective records which record the history of timekeepers and matters over their time at a firm, and allow retrospective reports to be run.  So the question sometimes becomes, "please explain why do we have a fee-timekeeper working for us in 9999"?  Or in more technical speak, "looking at the tkprdate record why is there an entry where the nxstartdate and nxenddate are both 9999-12-31-00:00:00.000"?
The answer is fairly simple, 3E is maintaining referential integrity.  In the case I was looking at, the timekeeper in question had their department changed at the end of financial year, but it wasn't implemented in 3E for another month.  In this case you shouldn't just make the change in Timekeeper Maintenance, because it won't reflect correctly in the data.  You have to use Timekeeper Global Change instead.  This is because any time entries posted between the start of the financial year and the date the change was made were linked to the prior year's tkprdate record. 
When you do make the change, 3E doesn't just update the time entries.  Instead, it creates new ones and marks the old ones as inactive, and leaves them linked to the tkprdate record with the 9999-12-31 date, so that it wouldn't possibly appear in a report.  The net total of these time entries is zero, and everything balances, and integrity is preserved.
The following two SQLs might give some more insight.  The first should return all of the timekeepers with extreme dates, but the total should be zero.  The second is a similar query, but only for active timecards, and shouldn't return anything at all.
select number, tkprdate.office, tkprdate.department, tkprdate.title, min(workdate), max(workdate), count(*), sum(workamt), td.Office, td.Department, td.title
from tkprdate
join Timekeeper on tkprdate.TimekeeperLkUp=Timekeeper.tkprindex
join timecard on timecard.TkprEffDate=tkprdateid
join tkprdate td on td.TimekeeperLkUp=TkprDate.TimekeeperLkUp and WorkDate between td.NxStartDate and td.NxEndDate
where tkprdate.nxstartdate='9999-12-31'
group by number, tkprdate.office, tkprdate.Department, tkprdate.title, td.Office, td.Department, td.title
order by number, tkprdate.office, tkprdate.Department
select number, tkprdate.office, tkprdate.department, tkprdate.title, min(workdate), max(workdate), count(*), sum(workamt), td.Office, td.Department, td.title
from tkprdate
join Timekeeper on tkprdate.TimekeeperLkUp=Timekeeper.tkprindex
join timecard on timecard.TkprEffDate=tkprdateid
join tkprdate td on td.TimekeeperLkUp=TkprDate.TimekeeperLkUp and WorkDate between td.NxStartDate and td.NxEndDate
where tkprdate.nxstartdate='9999-12-31' and timecard.IsActive=1
group by number, tkprdate.office, tkprdate.Department, tkprdate.title, td.Office, td.Department, td.title
order by number, tkprdate.office, tkprdate.Department
All Posts
×

Almost done…

We just sent you an email. Please click the link in the email to confirm your subscription!

OKSubscriptions powered by Strikingly