CREATE TABLE #t ( ID INT, [Name] VARCHAR(32), price INT, date DATETIME )INSERT INTO #t(ID,[Name],price,date) VALUES(1,'aa',10,'2014-08-30') INSERT INTO #t(ID,[Name],price,date) VALUES(2,'bb',20,'2014-08-30') INSERT INTO #t(ID,[Name],price,date) VALUES(1,'aa',11,'2014-08-31') INSERT INTO #t(ID,[Name],price,date) VALUES(2,'bb',21,'2014-08-31') INSERT INTO #t(ID,[Name],price,date) VALUES(1,'aa',9,'2014-09-01') INSERT INTO #t(ID,[Name],price,date) VALUES(2,'bb',19,'2014-09-01')SELECT t_newer.ID,t_newer.Name,t_newer.price 'New_price',t_older.price 'Older_price' FROM ( SELECT * FROM #t WHERE CONVERT(VARCHAR(16),date,23) = CONVERT(VARCHAR(16),GETDATE(),23) ) t_newer INNER JOIN (SELECT * FROM #t WHERE DATEDIFF(dd,date,GETDATE()) = 1) t_older ON t_newer.ID = t_older.ID
select a.id,.a.name,b.price as old_price,a.price as new_price,a.date from (select top 1 * from tb order by date desc) as a inner join (select top 1 * from (select top 2 * from tb order by date desc) as t) as b on a.date=b.date+1
SELECT t.*,tt.price AS Old_price FROM ( SELECT t1.id,t1.name,t1.Price,t1.[date],MAX(t2.[date]) AS MaxOldDate FROM testtb AS t1 LEFT JOIN testtb AS t2 ON t1.name=t2.name AND DATEDIFF(d,t2.[date],t1.[date])>0 GROUP BY t1.id,t1.name,t1.Price,t1.[date] ) AS t LEFT JOIN testtb AS tt ON t.name=tt.NAME AND DATEDIFF(d,tt.[date],t.MaxOldDate)=0
(
ID INT,
[Name] VARCHAR(32),
price INT,
date DATETIME
)INSERT INTO #t(ID,[Name],price,date) VALUES(1,'aa',10,'2014-08-30')
INSERT INTO #t(ID,[Name],price,date) VALUES(2,'bb',20,'2014-08-30')
INSERT INTO #t(ID,[Name],price,date) VALUES(1,'aa',11,'2014-08-31')
INSERT INTO #t(ID,[Name],price,date) VALUES(2,'bb',21,'2014-08-31')
INSERT INTO #t(ID,[Name],price,date) VALUES(1,'aa',9,'2014-09-01')
INSERT INTO #t(ID,[Name],price,date) VALUES(2,'bb',19,'2014-09-01')SELECT t_newer.ID,t_newer.Name,t_newer.price 'New_price',t_older.price 'Older_price' FROM
(
SELECT * FROM #t WHERE CONVERT(VARCHAR(16),date,23) = CONVERT(VARCHAR(16),GETDATE(),23)
) t_newer INNER JOIN
(SELECT * FROM #t WHERE DATEDIFF(dd,date,GETDATE()) = 1) t_older
ON t_newer.ID = t_older.ID
a.id,.a.name,b.price as old_price,a.price as new_price,a.date
from
(select top 1 * from tb order by date desc) as a
inner join
(select top 1 * from (select top 2 * from tb order by date desc) as t) as b
on
a.date=b.date+1
SELECT t.*,tt.price AS Old_price FROM (
SELECT t1.id,t1.name,t1.Price,t1.[date],MAX(t2.[date]) AS MaxOldDate FROM testtb AS t1
LEFT JOIN testtb AS t2 ON t1.name=t2.name AND DATEDIFF(d,t2.[date],t1.[date])>0
GROUP BY t1.id,t1.name,t1.Price,t1.[date]
) AS t LEFT JOIN testtb AS tt ON t.name=tt.NAME AND DATEDIFF(d,tt.[date],t.MaxOldDate)=0