Posted by MC on 12/05/05 11:06

Here si one way of doing it.

SELECT *
FROM tblTest
WHERE
fldCLocktime = (select max(ClockTime) from tblTest where clocktime <
'02/01/2005 12:34' and fldEmployeeID = 1)
AND
fldEmployeeID = 1



MC



<teddysnips@hotmail.com> wrote in message
news:1133772388.766696.128240@g44g2000cwa.googlegroups.com...
> Please see DDL and INSERT statements below.
>
> Let's say that some process throws out the second row, where the
> Clocktime = '02/01/2005 12:34'
>
> Without the use of a cursor, how can I retrieve the PREVIOUS value for
> that employee? Pseudo SQL might be something like:
>
> SELECT
> *
> FROM
> tblTest
> WHERE
> fldCLocktime = THE-ONE-IMMEDIATELY-BEFORE '02/01/2005 12:34'
> AND
> fldEmployeeID = 1
>
> TIA
>
> Edward
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblTest]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[tblTest]
> GO
>
> CREATE TABLE [dbo].[tblTest] (
> [fldTestID] [int] IDENTITY (1, 1) NOT NULL ,
> [fldEmployeeID] [int] NULL ,
> [fldClocktime] [datetime] NULL ,
> ) ON [PRIMARY]
> GO
>
> INSERT INTO tblTest
> (fldEmployeeID,
> fldClocktime)
> VALUES
> (1,
> '01/01/2005 12:34')
>
> INSERT INTO tblTest
> (fldEmployeeID,
> fldClocktime)
> VALUES
> (1,
> '02/01/2005 12:34')
>
> INSERT INTO tblTest
> (fldEmployeeID,
> fldClocktime)
> VALUES
> (1,
> '03/01/2005 12:34')
>

[Back to original message]