Audit History

Question

Post reply

Audit History

Sql Junkie-204042

Hall of Fame

Points: 3874
More actions
July 24, 2006 at 9:42 am

#90344

I need a query to return the original stoptime of a time clock record.
Whenever the time record of an employee needs to be changed, the original record on the timeclock table is flagged as deleted and a new record is created.
The original record has a reference column to the ID of the newly created time clock record
ID StartTime             StopTime                 TransID   Deleted
1 2006-07-21 09:30  2006-07-21 10:30      2           1
2 2006-07-21 09:30  2006-07-21 10:40      3          1
3 2006-07-21 09:30  2006-07-21 10:50       0           0
The original stop time was 10:30.
When record ID=2 was created, ID=1 was flagged as deleted and TransID was set to 2.
When record ID=3 was created, ID=2 was flagged as deleted and TransID was set to 3. User sees only the 3rd row but needs to know that the original stop time was 10:30
I have 2 options
a. Have the sql query call a function to return the original StopTime
CREATE FUNCTION fnTimeClockOriginal (@ID as int) RETURNS datetime
AS
BEGIN
DECLARE @CurrentID int, @OriginalStopTime as datetime, @OriginalID int
SET @CurrentID = @ID
SET @OriginalID = @ID
SELECT @OriginalStopTime = StopTime FROM TimeClock WHERE ID = @ID
WHILE EXISTS (SELECT 1 FROM TimeClock WHERE TransID = @CurrentID)
BEGIN
SELECT @OriginalStopTime = StopTime, @OriginalID = ID FROM TimeClock WHERE TransID = @CurrentID
SET @CurrentID = @OriginalID
END
RETURN @OriginalStopTime
END
GO

SELECT ID, startTime, StopTime, dbo.fnTimeClockOriginal(ID) OriginalStopTime FROM Timeclock where ID = 355731
b. The second option would be to add a new column to the table (OriginalID) and a trigger to update this column when deleted column is updated to 1.
The timelock table holds about 400,000 records. There is no index on TransID but can be added.
Out of the 8000 time clock records created in the last month, only 150 records were changed.
Which is the better solution (or is there an alternative?)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 1

Totally untested... should be nasty fast if the ID and StartTimeColumns are indexed.

SELECT tc.ID,

tc.StartTime,

tc.StopTime,

d.MinStopTime AS OriginalStopTime

FROM dbo.TimeClock tc WITH (NOLOCK),

(--Derived table finds the min stop time for each start time

SELECT StartTime,

MIN(StopTime) AS MinStopTime

FROM TimeClock

GROUP BY StartTime

) d

WHERE tc.StartTime = d.StartTime

AND tc.ID = 355731 --(or some variable)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 2

This will not work if you have 2 (or more) clock record at the same start time for different employees. The time clock table also holds clock records for jobs done on shop floor. I will have to use the transid column to go up the chain.