Lag () with a condition in SQL Server

Question

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 

For each EC, I need the previous / next SV price. In this case, the request is simple.

 Select Lag(price, 1, price) over (order by date desc, time desc), Lead(price, 1, price) over (order by date desc, time desc) from ITEMS 

But there are some special cases when two or more lines are of type EC:

 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 23658 2,4 EC 2014/02/02 07:50:45 23660 2,4 EC 2014/02/02 07:50:48 23465 0,668 SV 2014/02/02 07:36:34 

can i use lead / lag in these cases? If not, do I need to use a subquery?

Answer 1

Your question (and Anon's excellent answer) is part of the SQL islands and spaces . In this answer, I will try to examine in detail "row_number () magic".

I made a simple event-based example in ballgame. For each event, we would like to print the previous and next message:

 create table TestTable (id int identity, event varchar(64)); insert TestTable values ('Start of Q1'), ('Free kick'), ('Goal'), ('End of Q1'), ('Start of Q2'), ('Penalty'), ('Miss'), ('Yellow card'), ('End of Q2'); 

Here is a query showing the "row_number () magic" method:

 ; with grouped as ( select * , row_number() over (order by id) as rn1 , row_number() over ( partition by case when event like '%of Q[1-4]' then 1 end order by id) as rn2 from TestTable ) , order_in_group as ( select * , rn1-rn2 as group_nr , row_number() over (partition by rn1-rn2 order by id) as rank_asc , row_number() over (partition by rn1-rn2 order by id desc) as rank_desc from grouped ) select * , lag(event, rank_asc) over (order by id) as last_event_of_prev_group , lead(event, rank_desc) over (order by id) as first_event_of_next_group from order_in_group order by id 

• The first CTE, called "grouped," computes two row_number() s. The first row is 1 2 3 for each row of the table. The second row_number() places pause declarations in one list and other events in the second list. The difference between the two, rn1 - rn2 , is unique for each section of the game. It is useful to check the difference in the output example: in the group_nr column. You will see that each value corresponds to one section of the game.
• The second CTE, called "order_in_group", determines the position of the current line on its island or a space. For an island with 3 lines, positions 1 2 3 for ascending order and 3 2 1 for descending order.
• Finally, we know enough to tell lag() and lead() how far to jump. We need to lag rank_asc lines to find the last line of the previous section. To find the first line of the next section, we need to run the lines rank_desc .

Hope this helps clarify the “magic” of the Gaps and islands. Here is a working example in SQL Fiddle.

Answer 2

Yes, you can use LEAD / LAG. You just need to pre-calculate how far you can jump with little magic ROW_NUMBER ().

 DECLARE @a TABLE ( number int, price money, type varchar(2), date date, time time) INSERT @a VALUES (23456,0.665,'SV','2014/02/02','08:00:02'), (23457,1.3 ,'EC','2014/02/02','07:50:45'), (23658,2.4 ,'EC','2014/02/02','07:50:45'), (23660,2.4 ,'EC','2014/02/02','07:50:48'), (23465,0.668,'SV','2014/02/02','07:36:34'); ; WITH a AS ( SELECT *, ROW_NUMBER() OVER(ORDER BY [date] DESC, [time] DESC) x, ROW_NUMBER() OVER(PARTITION BY CASE [type] WHEN 'SV' THEN 1 ELSE 0 END ORDER BY [date] DESC, [time] DESC) y FROM @a) , b AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY xy ORDER BY x ASC) z1, ROW_NUMBER() OVER(PARTITION BY xy ORDER BY x DESC) z2 FROM a) SELECT *, CASE [type] WHEN 'SV' THEN LAG(price,z1,price) OVER(PARTITION BY [type] ORDER BY x) ELSE LAG(price,z1,price) OVER(ORDER BY x) END, CASE [type] WHEN 'SV' THEN LEAD(price,z2,price) OVER(PARTITION BY [type] ORDER BY x) ELSE LEAD(price,z2,price) OVER(ORDER BY x) END FROM b ORDER BY x 

Answer 3

Here is another way to achieve the same result, but using the conditional functions max / min with the window in order. The sequence number can be set depending on which columns correspond to the target, but in this case, I believe that the OP implies that they will be Date and Time .

 DROP TABLE IF EXISTS #t; CREATE TABLE #t ( Number INT, Price MONEY, Type CHAR(2), Date DATE, Time TIME(0) ); INSERT INTO #t VALUES (23456, 0.666, 'SV', '2014/02/02', '10:00:02'), (23457, 1.4 , 'EC', '2014/02/02', '09:50:45'), (23658, 2.5 , 'EC', '2014/02/02', '09:50:45'), (23660, 2.5 , 'EC', '2014/02/02', '09:50:48'), (23465, 0.669, 'SV', '2014/02/02', '09:36:34'), (23456, 0.665, 'SV', '2014/02/02', '08:00:02'), (23457, 1.3 , 'EC', '2014/02/02', '07:50:45'), (23658, 2.4 , 'EC', '2014/02/02', '07:50:45'), (23660, 2.4 , 'EC', '2014/02/02', '07:50:48'), (23465, 0.668, 'SV', '2014/02/02', '07:36:34'), -- which one of these? (23465, 0.670, 'SV', '2014/02/02', '07:36:34'); -- WITH time_ordered AS ( SELECT *, DENSE_RANK() OVER (ORDER BY Date, Time) AS ordinal FROM #t ) SELECT *, CASE WHEN Type = 'EC' THEN MAX(CASE WHEN ordinal = preceding_non_EC_ordinal THEN Price END) OVER (PARTITION BY preceding_non_EC_ordinal ORDER BY ordinal ASC) END AS preceding_price, CASE WHEN Type = 'EC' THEN MIN(CASE WHEN ordinal = following_non_EC_ordinal THEN Price END) OVER (PARTITION BY following_non_EC_ordinal ORDER BY ordinal DESC) END AS following_price FROM ( SELECT *, MAX(CASE WHEN Type <> 'EC' THEN ordinal END) OVER (ORDER BY ordinal ASC) AS preceding_non_EC_ordinal, MIN(CASE WHEN Type <> 'EC' THEN ordinal END) OVER (ORDER BY ordinal DESC) AS following_non_EC_ordinal FROM time_ordered ) t ORDER BY Date, Time 

Note that the example provided by the OP has been extended to show that interspersed EC sequences give the expected result. The ambiguity introduced by the two earliest consecutive lines of type SV will then select the maximum value. Setting the sequence number to enable Price is a possible way to change this behavior.

SQLFiddle can be found here: http://sqlfiddle.com/#!18/85117/1