|
select A. product name, MAX (A. time) AS time, SUM (A. quantity) AS quantity, A. unit price AS unit price 1, B. unit price AS unit price 2, A. unit price-(select top 1 unit price from testtb where Product name = A. Product name and time <'2016-12-01' order by time desc) AS difference
FROM testtb A LEFT JOIN testtb B ON A. Product name=B. Product name
AND B. time=(SELECT MAX(time) FROM testtb WHERE product name=A. product name AND time<A. time AND (unit price<>A. unit price OR time<'2016-12-01'))
WHERE A. time>='2016-12-01' AND A. time<='2016-12-31'
GROUP BY A. Product name, A. Unit price, B. Unit price
ORDER BY MAX(A. time) |
|