i have a table like this:
Number Price Type Date Time
------ ----- ---- ---------- ---------
23456 0,665 SV 2014/02/02 08:00:02
23457 1,3 EC 2014/02/02 07:50:45
23460 0,668 SV 2014/02/02 07:36:34
23461 0,668 SV 2014/02/02 07:37:34
23462 0,668 SV 2014/02/02 07:38:34
23463 0,668 SV 2014/02/02 07:39:34
For each record I need previous/next price. In this case, the query is simple.
Select Lag(price) over (order by date desc, time desc),
Lead(price) over (order by date desc, time desc)
from ITEMS
but i need the result Where Next price <> record price
My Query is
Select Lag(price) over (order by date desc, time desc) Nxt_Price,
Lead(price) over (order by date desc, time desc) Prv_Price
from ITEMS
Where Nxt_Price <> price
but it refused to use that variable
LAG() : SQL Server provides LAG() function which is very useful in case the current row values need to be compared with the data/value of the previous record or any record before the previous record. The previous value can be returned on the same record without the use of self join making it straightforward to compare.
LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.
The LAG and LEAD functions are OLAP ranking functions that return the value of their expression argument for the row at a specified offset from the current row within the current window partition.
Lead and lag are both used in the development of the project schedule. Lead is an acceleration of the successor activity and can be used only on finish-to-start activity relationships. Lag is a delay in the successor activity and can be found on all activity relationship types.
Try the below query:
SELECT Nxt_Price, Prv_Price
FROM
(Select price, Lag(price) over (order by date desc, time desc) Nxt_Price,
Lead(price) over (order by date desc, time desc) Prv_Price
from ITEMS) AS InnerQuery
Where Nxt_Price <> price
It may help you.
Use a common table expression.
with myItems as (
Select Lag(price) over (order by date desc, time desc) Nxt_Price,
Lead(price) over (order by date desc, time desc) Prv_Price
from ITEMS
)
select *
from myItems
where Nxt_Price <> Prv_Price
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With