quote:

---

Originally posted by jcarrallo
select sph_symbol, a.sph_value,
b.sph_value
from Price_1 a
join Prices_1 b on b.sph_symbol = a.sph_symbol

where a.sph_date = DATEADD(DAY, -1, GETDATE())100
and b.sph_date = DATEADD(DAY, -5, GETDATE())

---

declare @Price_1 table (sph_symbol varchar(10), sph_date datetime, sph_value int);
insert into @Price_1

	select 'aaa', '2014-07-25', 1 union all
	select 'aaa', '2014-07-24', 2 union all
	select 'aaa', '2014-07-20', 3 union all
	select 'bbb', '2014-07-25', 6 union all
	select 'bbb', '2014-07-24', 4 union all
	select 'bbb', '2014-07-20', 1;

select	a.sph_symbol,
	[MaxValueFromYesterday] = max(case when a.sph_date = dateadd(dd,datediff(dd,0,getdate()-1),0) then sph_value else null end),
	[MaxValueFrom5DaysAgo] = max(case when a.sph_date = dateadd(dd,datediff(dd,0,getdate()-5),0) then sph_value else null end)
from	@Price_1 a
group
by      a.sph_symbol;