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?)

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  July 24, 2006 at 10:06 pm

  #651245

  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)
  Intro to Tally Tables and Functions

• Sql Junkie-204042

  Hall of Fame

  Points: 3874

  More actions

  July 25, 2006 at 12:52 pm

  #651448

  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.