## 3 replies

Last post Jan 14, 2015 10:33 AM by httsqv

Member

68 Points

138 Posts

### Comparing Price history

Hi,

I have a table CompanyPrice as below, here's an example

```Company            Service             Price               EffectiveDate
-------            --------            ------              --------------
Company1           Service1              50                  1//12015
Company1           Service1              60                  1/2/2015
Company1           Service1              70                  1/3/2015
Company1           Service2              20                  1/1/2015
Company2           Service1             100                  2/2/2015
Company2           Service1              80                  2/5/2015 ```

I there a way to have a result as below: (this result is generated from the data sample of the table) ?

```Company        Service      OldPrice     EffectiveDate        NewPrice      EffectiveDate     Variation
-------        -------      --------     -------------        --------      -------------     ---------
Company1       Service1     50            1/1/2015            60            1/2/2015          Increase
Company1       Service1     60            1/2/2015            70            1/3/2015          Increase
Company1       Service2     20            1/1/2015
Company2       Service2     100           2/2/2015            80            2/5/2015          Decrease```

Thanks

.............

• ### limno

All-Star

121980 Points

9625 Posts

Moderator

### Re: Comparing Price history

```Create Table CompanyPrices (Company  varchar(50), Service varchar(50), Price  int,  EffectiveDate date)
Insert into CompanyPrices values('Company1','Service1',50,'1/1/2015')
,('Company1','Service1',60,'1/2/2015')
,('Company1','Service1',70,'1/3/2015')
,('Company1','Service2',20,'1/1/2015')
,('Company2','Service1',100,'2/2/2015')
,('Company2','Service1',80,'2/5/2015')

Select * from (
Select *, lead(Price,1) Over(Partition by Company, Service  Order by EffectiveDate ) newPrice,
lead(EffectiveDate,1) Over(Partition by Company, Service  Order by EffectiveDate )  newDate
,Case When lead(Price,1) Over(Partition by Company, Service  Order by EffectiveDate ) -Price >=0 Then 'Increse'
When lead(Price,1) Over(Partition by Company, Service  Order by EffectiveDate ) -Price <0 Then 'Decrease'
When lead(Price,1) Over(Partition by Company, Service  Order by EffectiveDate ) -Price =0 Then 'No change'
End
as Variation

,row_number() Over(Partition by Company, Service  Order by EffectiveDate ) rn1
,row_number() Over(Partition by Company, Service  Order by EffectiveDate DESC ) rn2

from CompanyPrices
) t
WHERE (newPrice is not null)  Or ( newPrice is null and  rn1=1 AND rn2=1 )

Order by Company, Service, EffectiveDate

drop table CompanyPrices```

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm

Contributor

5450 Points

1123 Posts

### Re: Comparing Price history

Hi,

If you are using SQL Server 2005 or 2008 try:

```with CTE_RN as
(
select
*,
ROW_NUMBER() OVER(PARTITION BY Company, Service ORDER BY EffectiveDate) as RN
from CompanyPrice
)

select
o.Company,
o.Service,
o.Price as OldPrice,
o.EffectiveDate as OldEffectiveDate,
n.Price as NewPrice,
n.EffectiveDate as NewEffectiveDate,
case
when n.Price > o.Price then 'Increase'
when n.Price < o.Price then 'Decrease'
end as Variation
from CTE_RN as o
left join CTE_RN as n
on n.Company = o.Company and
n.Service = o.Service and
n.RN = o.RN + 1
where
(n.Price is not null) or
(n.Price is null and o.RN = 1)```

Hope this helps.

Member

68 Points

138 Posts