The riddle of the - SQL Server Forums

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

SQL Server Development (2000)

The riddle of the

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-10-24 : 09:24:08

Robert writes "I do a lot of work with historical tables. My users submit "versions" of the data, each record is time-stamped so I know which is the most recent.

When I report that data, I want to perform joins with only the most recent records. To do this I normally create various result tables, and CURSOR through to build up the results I want.

But someone out there clever enough must know a better way!!

In the example below, the recordset I want is the most recent ETC (estimate to complete) for each project.

eg (using short form of date for simplicity)

TABLE tAETC
project, estimate, updated
-------------------------------------
Chocolate, 23, 12-Oct-2001
Chocolate, 12, 19-Oct-2001
Strawberry, 10, 12-Oct-2001
Strawberry, 6, 19-Oct-2001

TABLE tAPROJECT
project, manager, budget
-------------------------------------
Chocolate, Fred, 160
Strawberry, Ralph, 200
Vanilla, Bluey, 300

RESULT
project, manager, budget, estimate
-------------------------------------
Chocolate, Fred, 160, 12
Strawberry, Ralph, 200, 6
Vanilla, Bluey, 300, NULL

My question is: Is there any way to achieve this within a SELECT statement?
My platform is
*NT v4.0 sp 6
*SQL Server 2000
.......................................................
Scripts for building the tables are below
.......................................................

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tAPROJECT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tAPROJECT]
GO

CREATE TABLE [dbo].[tAPROJECT] (
[project] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[manager] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[budget] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tAETC]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tAETC]
GO

CREATE TABLE [dbo].[tAETC] (
[project] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[estimate] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[updated] [datetime] NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[tAPROJECT] (project, manager, budget)
VALUES ('Chocolate','Fred','160')
INSERT INTO [dbo].[tAPROJECT] (project, manager, budget)
VALUES ('Strawberry', 'Ralph', '200')
INSERT INTO [dbo].[tAPROJECT] (project, manager, budget)
VALUES ('Vanilla', 'Bluey', '300')
GO

INSERT INTO [dbo].[tAETC] (project, estimate, updated)
VALUES ('Chocolate', '23', CONVERT(datetime, '12-Oct-2001'))
INSERT INTO [dbo].[tAETC] (project, estimate, updated)
VALUES ('Chocolate', '12', CONVERT(datetime, '19-Oct-2001'))
INSERT INTO [dbo].[tAETC] (project, estimate, updated)
VALUES ('Strawberry', '10', CONVERT(datetime, '12-Oct-2001'))
INSERT INTO [dbo].[tAETC] (project, estimate, updated)
VALUES ('Strawberry', '6', CONVERT(datetime, '19-Oct-2001'))
GO

/*NOTE - I used nvarchar for the values but this is irrelevant to the problem.*/"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources