By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,608 Members | 1,940 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,608 IT Pros & Developers. It's quick & easy.

Get previous record

P: n/a
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')

Dec 5 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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')

Dec 5 '05 #2

P: n/a

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

Dec 5 '05 #3

P: n/a
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

Dec 5 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.