query

drop table share
create table share
(
shid int identity ,
dt datetime,
price int
)
insert into share(dt,price) values('1-jan-2006',10)
insert into share(dt,price) values('2-jan-2006',11)
insert into share(dt,price) values('6-jan-2006',15)
insert into share(dt,price) values('4-jan-2006',9)
select * from share
output i want is
the difference in share prices from the previous day.
so
1 (11-10)
4 (15-11)
-6 (9-15)
select top 100 percent p1.shid p1shid, p2.shid p2shid, p1.price p1price,
p2.price p2price
from
(select * from share
) as p2,
share p1
where p1.shid < p2.shid
order by p1.shid, p2.shid
thisis the closest i could come up with but it has duplicates.Try,
select b.price - a.price
from t1 as a inner join t1 as b
on a.dt = (select max(c.dt) from t1 as c where c.dt < b.dt)
Why 15 - 11 and 9 - 15 instead 9 - 11 and 15 - 9?. If you meant previous
[shid] then try:
select b.price - a.price
from t1 as a inner join t1 as b
on a.shid = (select max(c.shid) from t1 as c where c.shid < b.shid)
AMB
"ichor" wrote:

> drop table share
> create table share
> (
> shid int identity ,
> dt datetime,
> price int
> )
>
> insert into share(dt,price) values('1-jan-2006',10)
> insert into share(dt,price) values('2-jan-2006',11)
> insert into share(dt,price) values('6-jan-2006',15)
> insert into share(dt,price) values('4-jan-2006',9)
> select * from share
>
> output i want is
>
> the difference in share prices from the previous day.
> so
> 1 (11-10)
> 4 (15-11)
> -6 (9-15)
>
> select top 100 percent p1.shid p1shid, p2.shid p2shid, p1.price p1price,
> p2.price p2price
> from
> (select * from share
> ) as p2,
> share p1
> where p1.shid < p2.shid
> order by p1.shid, p2.shid
> thisis the closest i could come up with but it has duplicates.
>
>|||u r right. sorry for the mistake.
the query works fine.
the first query is what i was after.
i guess my approach of writing the query was wrong
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:73C20DA8-65E8-4203-BD0A-AF90394834A5@.microsoft.com...
> Try,
> select b.price - a.price
> from t1 as a inner join t1 as b
> on a.dt = (select max(c.dt) from t1 as c where c.dt < b.dt)
> Why 15 - 11 and 9 - 15 instead 9 - 11 and 15 - 9?. If you meant previous
> [shid] then try:
> select b.price - a.price
> from t1 as a inner join t1 as b
> on a.shid = (select max(c.shid) from t1 as c where c.shid < b.shid)
>
> AMB
> "ichor" wrote:
>|||I think this should work:
select shid, dt, price - isnull((Select price from share b where b.shid =
a.shid - 1), 0) from share a
Hope this help...
Ed
"ichor" wrote:

> drop table share
> create table share
> (
> shid int identity ,
> dt datetime,
> price int
> )
>
> insert into share(dt,price) values('1-jan-2006',10)
> insert into share(dt,price) values('2-jan-2006',11)
> insert into share(dt,price) values('6-jan-2006',15)
> insert into share(dt,price) values('4-jan-2006',9)
> select * from share
>
> output i want is
>
> the difference in share prices from the previous day.
> so
> 1 (11-10)
> 4 (15-11)
> -6 (9-15)
>
> select top 100 percent p1.shid p1shid, p2.shid p2shid, p1.price p1price,
> p2.price p2price
> from
> (select * from share
> ) as p2,
> share p1
> where p1.shid < p2.shid
> order by p1.shid, p2.shid
> thisis the closest i could come up with but it has duplicates.
>
>|||can u explain why we need a b and c?
in the query?
"ichor" <ichor@.hotmail.com> wrote in message
news:Oe6jYSxRGHA.792@.TK2MSFTNGP10.phx.gbl...
>u r right. sorry for the mistake.
> the query works fine.
> the first query is what i was after.
> i guess my approach of writing the query was wrong
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:73C20DA8-65E8-4203-BD0A-AF90394834A5@.microsoft.com...
>|||We need to join a tabla to itself, so we need to alias the second reference
to it (b). The join condition is between a row in one side (b) and the a row
in the other side (a) where its [dt] value (in a) is equal to the previous of
current in b (the max([c.dt]) where [dt] < b.[dt]).
AMB
"ichor" wrote:

> can u explain why we need a b and c?
> in the query?
> "ichor" <ichor@.hotmail.com> wrote in message
> news:Oe6jYSxRGHA.792@.TK2MSFTNGP10.phx.gbl...
>
>|||could we have done this using only a and b and no c?
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3AD01065-EBAA-4AAE-990C-E1F29FB8BD9B@.microsoft.com...
> We need to join a tabla to itself, so we need to alias the second
> reference
> to it (b). The join condition is between a row in one side (b) and the a
> row
> in the other side (a) where its [dt] value (in a) is equal to the previous
> of
> current in b (the max([c.dt]) where [dt] < b.[dt]).
>
> AMB
> "ichor" wrote:
>