finding price increase with query

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

Transact-SQL (2000)

finding price increase with query

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2009-03-20 : 06:04:08

I've a price table that has product codes price date which the price was changed and the price

there can be multiple prices for a product i need to find out price increases for each product

Example data

prd_code prod_date price
1 01/01/2009 10
1 08/01/2009 11
1 01/01/2009 9

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-20 : 06:53:54

you mean the price increase over the previous date ?

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2009-03-20 : 07:02:08

Yes sorry the price increases for the previous date .

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-20 : 07:14:10

[code]
Create table #pricetemp (prd_code int,prod_date datetime,price numeric(15,5))
go
insert into #pricetemp
select 1, '01/01/2009', 10 union all
select 1, '08/01/2009', 11 union all
select 1, '09/01/2009', 9 union all
select 2, '01/02/2009', 9 union all
select 2, '01/01/2010', 11
go

select * from #pricetemp

go

select
a.prd_code,
a.prod_date,
a.price,
change=ISNULL( (a.price)
-
(select
price
from
#pricetemp
where
prod_date=
(select
max(prod_date)
from
#pricetemp
where
prod_date<a.prod_date and prd_code=a.prd_code)
and prd_code=a.prd_code)
,0)
from
#pricetemp a

go

drop table #pricetemp[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources