Lag() Lead() with condition

Question

My data looks something like this. I need to find on each Reassign who was the prior and next download.

System  Reassign    2017-09-08
Walker  Download    2017-09-09
System  Reassign    2017-09-10
Kruger  Download    2017-09-11
System  Reassign    2017-12-01

I had some success with this post, but was off on some rows. Using my sample data/query below how can I get the results I need?

Lag() with conditon in sql server

DECLARE @a TABLE (indiv varchar(20), status varchar(20), date date)
INSERT @a VALUES

('System','Reassign','2017/09/08'),
('Walker','Download','2017/09/09'),
('System','Reassign','2017/09/10'),
('Kruger','Download','2017/09/11'),
('System','Reassign','2017/09/12'),
('System','Reassign','2017/09/17'),
('Kruger','Download','2017/09/18'),
('System','Reassign','2017/10/01'),
('Hazle','Download','2017/11/02'),
('System','Reassign','2017/11/05'),
('Jones','Download','2017/11/06'),
('System','Reassign','2017/12/01'),
('Don','Download','2017/12/02');



; WITH a AS (SELECT *,
            ROW_NUMBER() OVER(ORDER BY [date] ASC) x, 
            ROW_NUMBER() OVER(PARTITION BY case when [status] = 'Download' then 1 end ORDER BY [date] ASC) y 
          FROM @a)

, b AS    (SELECT *,
          --x-y as groupxy,
          ROW_NUMBER() OVER(PARTITION BY x-y ORDER BY x ASC) rank_asc
            FROM a)

SELECT *
       ,CASE WHEN [status] = 'Reassign' 
         THEN 
           LAG([indiv],rank_asc) OVER(ORDER BY x) 
           END AS [Previous Download Indiv]

from b
order by [date]

These are my expected results...

indiv   status      date        Prev Download Indiv Next Download Indiv
System  Reassign    2017-09-08  NULL            NULL
Walker  Download    2017-09-09  NULL            NULL
System  Reassign    2017-09-10  Walker          Kruger
Kruger  Download    2017-09-11  NULL            NULL
System  Reassign    2017-09-12  Kruger          Kruger
System  Reassign    2017-09-17  Kruger          Kruger
Kruger  Download    2017-09-18  NULL            NULL
System  Reassign    2017-10-01  Kruger          Hazle
Hazle   Download    2017-11-02  NULL            NULL
System  Reassign    2017-11-05  Hazle           Jones
Jones   Download    2017-11-06  NULL            NULL
System  Reassign    2017-12-01  Jones           Don
Don     Download    2017-12-02  NULL            NULL

Please provide a sample of your dataset and a target output. — Andrew O'Brien, Feb 23 '18 at 20:31

score 0 · Accepted Answer · answered Feb 23 '18 at 21:43

DECLARE @a TABLE (indiv varchar(20), status varchar(20), date date)
INSERT @a VALUES
('System','Reassign','2017/09/08'),
('Walker','Download','2017/09/09'),
('System','Reassign','2017/09/10'),
('Kruger','Download','2017/09/11'),
('System','Reassign','2017/09/12'),
('System','Reassign','2017/09/17'),
('Kruger','Download','2017/09/18'),
('System','Reassign','2017/10/01'),
('Hazle','Download','2017/11/02'),
('System','Reassign','2017/11/05'),
('Jones','Download','2017/11/06'),
('System','Reassign','2017/12/01'),
('Don','Download','2017/12/02');


select * 
     , (select top 1 l.indiv from @a l where l.status <> 'Reassign' and l.date < a.date order by l.date desc) as lag
     , (select top 1 l.indiv from @a l where l.status <> 'Reassign' and l.date > a.date order by l.date asc)  as lead
from @a a 
where a.status  = 'Reassign' 
union all 
select a.*, null, null 
from @a a
where a.status <> 'Reassign' 
order by date;

With all the values have an ID = 1, then duplicated with an ID = 2, how would I then partition or break this data by ID? — Newbie, Feb 28 '18 at 16:51

score 0 · Answer 2 · answered Feb 24 '18 at 04:42

Same that could be also achieve with using of correlated Subquery

SELECT 
       indiv, status, date,
       Prev = CASE WHEN t.status <> 'Download' THEN
                  (SELECT TOP 1 indiv FROM table WHERE [date] < t.[date] AND status <> 'Reassign' ORDER BY [date] DESC) END,
       Next = CASE WHEN t.status <> 'Download' THEN
                  (SELECT TOP 1 indiv FROM table WHERE [date] > t.[date] AND status <> 'Reassign' ORDER BY [date]) END
FROM table t

Lag() Lead() with condition

2 Answers2