Select min value in an inner join

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

General SQL Server Forums

New to SQL Server Programming

Select min value in an inner join

Author

Topic

Sean_B
Posting Yak Master

111 Posts

Posted - 2006-11-28 : 13:51:48

Hello,

I'd appreciate any help with the following problem.

I'm trying to update a table using an inner self join.

I've a list of historical records with start dates, and I need to add end dates to the records, using the start date of the next record.

The table I'm using looks like this
CREATE TABLE [dbo].[IbesEstimateHist](
[IbesEstimateHistId] [int] IDENTITY(1,1) NOT NULL,
[IbesEstimateId] [int] NULL,
[EstimateDate] [datetime] NULL,
[EstimateEndDate] [datetime] NULL CONSTRAINT [DF_IbesEstimateHist_EstimateEndDate] DEFAULT ('9999-12-31 00:00.000'),
[Value] [decimal](13, 4) NULL,
CONSTRAINT [PK_IbesEstimateHist] PRIMARY KEY CLUSTERED
(
[IbesEstimateHistId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

and here's some example data

insert into IbesEstimateHist
([IbesEstimateId],[EstimateDate],[EstimateEndDate],[Value])
values(1,'2006-01-01','9999-12-31',100)
insert into IbesEstimateHist
([IbesEstimateId],[EstimateDate],[EstimateEndDate],[Value] )
values (1,'2006-02-01','9999-12-31',100)
insert into IbesEstimateHist
([IbesEstimateId],[EstimateDate],[EstimateEndDate],[Value])
values (1,'2006-03-01','9999-12-31',100)

These are three historical records for the same estimate, I want to set the end dates of the earlier records to the start date of the next record that was recieved.

This is the SQL that I've tried using but I can't seem to get it right

select esth1.IbesEstimateId, esth1.EstimateEndDate,min(next.estimatedate)
from IbesEstimateHist esth1
inner join
(
select esth2.EstimateDate as estimatedate, esth2.IbesEstimateId
from IbesEstimateHist esth2
) as next
on esth1.IbesEstimateId = next.IbesEstimateId
and esth1.EstimateDate < next.estimatedate
group by esth1.IbesEstimateId, esth1.EstimateEndDate

I'd be grateful for any help, thanks.

Sean_B

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-28 : 14:07:54

Here's an example I created for your previous post but could never post because you deleted that post.

declare @IbesEstimateHist table (IbesEstimateHistId int identity,
IbesEstimateId int,
EstimateDate datetime,
EstimateEndDate datetime default('12/31/2050'),
Value decimal(9,2))
insert @IbesEstimateHist (IbesEstimateId, EstimateDate, Value)
select 1, '1/1/2006', 10.5 union all
select 1, '2/1/2006', 11.5 union all
select 1, '3/1/2006', 12.5 union all
select 2, '10/1/2006', 100.5 union all
select 2, '10/2/2006', 101.5 union all
select 2, '10/3/2006', 102.5

select IbesEstimateHistId, IbesEstimateId, EstimateDate,
	isnull((select min(EstimateDate) from @IbesEstimateHist where IbesEstimateId = A.IbesEstimateId 
	and IbesEstimateHistId > A.IbesEstimateHistId), EstimateEndDate) as EstimateEndDate,
	Value 
from @IbesEstimateHist A

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-28 : 14:09:19

Same here. When sending the post was refused...

Peter Larsson
Helsingborg, Sweden

Sean_B
Posting Yak Master

111 Posts

Posted - 2006-11-29 : 02:54:04

Thanks for the help it is greatly appreciated and sorry about removing the original query, I did it because it was badly worded and inaccurate, in future, I'll just make a correction to what I've posted.

Sean_B

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources