Get previous record

[teddysnips]

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

[MC]

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

<te********@hotmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.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')

[teddysnips]

MC wrote:

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

Hi MC

Your response put me onto another way that doesn't use a sub-query:

SELECT TOP 1 *
FROM tblTest WHERE fldClocktime <
'02/01/2005 12:34' and fldEmployeeID = 1
ORDER BY fldClocktime DESC

Both methods work fine - thanks for your help.

Edward

[MC]

Yes, it works if you're running the query for one employee. I thought you
needed something for all employees or something like that. Oh well, as long
as you managed to solve the problem :).

MC
<te********@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

MC wrote:

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

Hi MC

Your response put me onto another way that doesn't use a sub-query:

SELECT TOP 1 *
FROM tblTest WHERE fldClocktime <
'02/01/2005 12:34' and fldEmployeeID = 1
ORDER BY fldClocktime DESC

Both methods work fine - thanks for your help.

Edward