Need help with a self-join to table

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)

Need help with a self-join to table

Author

Topic

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-05-16 : 09:53:53

I have a table structured as follows:

Date ID Value
Jan-15-02 ABC 520
Feb-02-02 ABC 600
Mar-08-02 ABC 610

Note that the dates in my table are irregular (so while they appear to have monthly intervals, they are not neat months).

I would like to return the average of each two Values per ID in this table. Given that I have to work with irregular intervals between each date record per ID and value, I am using a self-join as follows:

select a.Date, a.ID, a.Value, min(b.Date)

from myTable a,
myTable b,
where a.ID = b.ID
and b.Date > a.Date
group by a.Date, a.ID, a.Value

which is nice because this gives me the ability to look at the very next successive date in this table (b.Date) given any date (a.Date) per ID. However, when I try to return the b.Value and then perhaps add another column showing the average of a.Value + b.Value, my joins seems to get all thrown off. So, I'd like to continue doing the above but return:

select a.Date, a.ID, a.Value, min(b.Date), b.Value, "Ave" = (a.Value + b.Value)/2

Can anyone suggest how I might construct such a query? I've tried adding b.Value and "Ave" to the select list and adding these to the group by clause, but my result set expands well beyond what it should be when I do this.

Any help would be much appreciated.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-16 : 13:25:33

You want just a date + the folllowing date?
select a.Date, a.ID, a.Value, b.Date, b.Value, (a.Value + b.Value) / 2
from myTable a,
myTable b,
where a.ID = b.ID
and b.Date = (select min(c.date) from myTable c where c.id = a.id and c.Date > a.Date)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-05-16 : 15:29:54

Thank you, I'll give it a try.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources