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)  Finding the last history row in table

Author  Topic  mrusso Starting Member 1 Post Posted - 2001-11-23 : 14:45:19 I need to find the last history row in a table that has records with a begin date and an end date. I need the current active row, or the last history row where the record has an end date. A record is 'active' if the end date is null or >=GetDate(). Currently, I have the following query to return all of the active rows, but I need to return the last terminated row for the Test1 employee where the row begins on 2000-01-01 and ends on 2001-01-30. Thanks for any suggestions! -- select a.ID, a.RecType, a.DateBeg, a.DateEnd, a.SSN, a.FirstName, a.LastName, a.Annual From Test_Table as a where (a.DateBeg <=GetDate() AND (a.DateEnd >=GetDate() OR a.DateEnd is NULL)) -- if exists (select * from dbo.sysobjects where id = object_id(N'[TEST_TABLE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [TEST_TABLE] GO CREATE TABLE [dbo].[TEST_TABLE] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [RecType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DateBeg] [datetime] NOT NULL , [DateEnd] [datetime] NULL , [SSN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [HireDate] [datetime] NULL , [Annual] [money] NOT NULL , CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( [ID] ) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT TEST_TABLE ON insert into TEST_TABLE(ID,RecType,DateBeg,DateEnd,SSN,FirstName,LastName,HireDate,Annual) values(1,'BASE','Jul 19 2000 12:00AM',NULL,'534545478','Michael','Yak','Feb 1 1990 12:00AM',66000.00) insert into TEST_TABLE(ID,RecType,DateBeg,DateEnd,SSN,FirstName,LastName,HireDate,Annual) values(10,'BASE','Jan 1 2000 12:00AM','Jan 30 2001 12:00AM','565446778','Test1','Test1',NULL,73000.00) insert into TEST_TABLE(ID,RecType,DateBeg,DateEnd,SSN,FirstName,LastName,HireDate,Annual) values(11,'BASE','Jan 1 2002 12:00AM','Jan 30 2002 12:00AM','565446778','Test1','Test1',NULL,55000.00) insert into TEST_TABLE(ID,RecType,DateBeg,DateEnd,SSN,FirstName,LastName,HireDate,Annual) values(3,'BASE','Mar 13 1999 12:00AM',NULL,'123456789','Nicole','Yak','Jul 5 1990 12:00AM',40000.00) insert into TEST_TABLE(ID,RecType,DateBeg,DateEnd,SSN,FirstName,LastName,HireDate,Annual) values(4,'BASE','Aug 23 1998 12:00AM','Mar 12 1999 12:00AM','123456789','Nicole','Yak','Jul 5 1990 12:00AM',25000.00) insert into TEST_TABLE(ID,RecType,DateBeg,DateEnd,SSN,FirstName,LastName,HireDate,Annual) values(5,'BASE','Jul 5 1990 12:00AM','Aug 22 1998 12:00AM','123456789','Nicole','Yak','Jul 5 1990 12:00AM',21000.00) insert into TEST_TABLE(ID,RecType,DateBeg,DateEnd,SSN,FirstName,LastName,HireDate,Annual) values(8,'BASE','Feb 1 1990 12:00AM','Jul 18 2000 12:00AM','534545478','Michael','Yak','Feb 1 1990 12:00AM',60000.00) insert into TEST_TABLE(ID,RecType,DateBeg,DateEnd,SSN,FirstName,LastName,HireDate,Annual) values(9,'BASE','Oct 1 2000 12:00AM',NULL,'987654321','James','Yak','Oct 1 2000 12:00AM',76000.00) SET IDENTITY_INSERT TEST_TABLE OFF Edited by - mrusso on 11/23/2001 14:53:54