Extracting last entry of recordset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Extracting last entry of recordset

Author

Topic

bmerlin347
Starting Member

4 Posts

Posted - 2004-07-28 : 12:54:11

First Hello to everyone, I am new to this forum and to SQL.
My background is Lotus Domino so ..

Here is the scenario I am trying to solve, I know exactly what I want to do, but I do not know which route to take with SQL:

We have users who throughout the manufacturing shifts specify how long they are going to work. The best scenario is that they only enter the data once, but as any real life example this does not happen. They may make 2 to 3 entries to specify the length of the work shift.
So I want to first get all the records for a single shift, then only keep the last record, as it is the one we want. I kinda figured out how to get the data per shift, but I have no idea for keeping only the last entry.

************************************************************
Sample Data:

ENTRY Time Stamp Field01 Field02 Field 03
2004-06-10 06:05:47.677 6 2 10 3
2004-06-10 20:02:14.000 20 1 7 2
2004-06-10 20:10:30.937 20 1 10 2

Any help from you guys would be very appreciated.
Thanks

Ben

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-28 : 13:16:09

Not sure what you mean....do you want to use MAX?

Brett

8-)

bmerlin347
Starting Member

4 Posts

Posted - 2004-07-28 : 14:22:40

We may have users enter more than one record / entry throughout the production shift.
So what I want to do is to only keep the last record entered in the table.

What I have to do is to group all the record entries by shift and then discard all the records Except the last one.

Hope this makes sense.

Thanks
Ben

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-28 : 14:39:49

[code]
USE Northwind
GO

CREATE TABLE myTable99(UserId int, DateEntry datetime)
GO

INSERT INTO myTable99(UserId, DateEntry)
SELECT 1, '07/28/2004 12:00:00' UNION ALL
SELECT 1, '07/28/2004 14:00:00' UNION ALL
SELECT 1, '07/28/2004 16:00:00' UNION ALL
SELECT 2, '07/28/2004 12:00:00' UNION ALL
SELECT 2, '07/28/2004 18:00:00'
GO

SELECT * FROM myTable99
GO

SELECT *
INTO myTable99_New
FROM myTable99 o
WHERE EXISTS (SELECT UserId
FROM myTable99 i
GROUP BY UserId
HAVING MAX(i.DateEntry) = o.DateEntry)
GO

TRUNCATE TABLE myTable99
GO

SELECT * FROM myTable99
GO

INSERT INTO myTable99(UserId, DateEntry)
SELECT UserId, DateEntry FROM myTable99_New
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
DROP TABLE myTable99_New

[/code]

Brett

8-)

bmerlin347
Starting Member

4 Posts

Posted - 2004-07-28 : 14:46:07

I just ran it on the Northwind DB and it works great.
Let me try it out on my actual data and get back with you.

Thanks for taking the time to respond so fast, this is great help!!!

bmerlin347
Starting Member

4 Posts

Posted - 2004-07-28 : 15:10:35

Well, it did work except that I forgot to do it for each shift.
Good thing that I did a back up first ;))

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-28 : 15:16:56