0

I'm having a few issues with LEAD/LAG. For each row within a set of IDs I'm wanting to get the previous/next source where isAQI = 1. Desired output is as follows in prevAQI and nextAQI columns.

I've tried the same approach as Lag() with conditon in sql server, but with no luck. Any help would be much appreciated!

enter image description here

Sample data as follows:

DECLARE @a TABLE ( id int, timest datetime, source char(2),
                   isAQI int, prevAQI char(2), nextAQI char(2))
INSERT @a VALUES
(6694   ,'2015-06-11 08:55:06.000'  ,'I'    ,1, NULL, 'A'), 
(6694   ,'2015-06-11 09:00:00.000'  ,'A'    ,1, 'I', 'I'),
(6694   ,'2015-06-11 09:11:49.000'  ,'C'    ,NULL, 'A', 'I'),
(6694   ,'2015-06-11 09:29:06.000'  ,'O'    ,NULL, 'A', 'I'),
(6694   ,'2015-06-11 09:29:06.000'  ,'DT'   ,NULL, 'A', 'I'),
(6694   ,'2015-06-11 09:34:11.000'  ,'DT'   ,NULL, 'A', 'I'),
(6694   ,'2015-06-11 09:34:11.000'  ,'O'    ,NULL, 'A', 'I'),
(6694   ,'2015-06-11 10:06:27.000'  ,'I'    ,1, 'A', 'I'),
(6694   ,'2015-06-11 11:25:09.000'  ,'DT'   ,NULL, 'I', 'I'),
(6694   ,'2015-06-11 18:25:24.000'  ,'C'    ,NULL, 'I', 'I'),
(6694   ,'2015-06-12 17:57:16.000'  ,'I'    ,1, 'I', NULL);

SELECT *
FROM @a
Community
  • 1
  • 1
user3603308
  • 351
  • 2
  • 14

1 Answers1

6

See if the following query works for you:

WITH C AS
(
  SELECT *,
    MAX(goodval) OVER(PARTITION BY id
                      ORDER BY timest
                      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prv,
    MIN(goodval) OVER(PARTITION BY id
                      ORDER BY timest
                      ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS nxt
  FROM @a
    CROSS APPLY ( VALUES( CONVERT(VARCHAR(23), timest, 121) 
                          + CASE WHEN isAQI = 1 THEN source END ) ) AS A(goodval)
)
SELECT id, timest, source,
CASE WHEN prv IS NOT NULL THEN SUBSTRING(prv, 24, 2) END AS prevAQI,
CASE WHEN nxt IS NOT NULL THEN SUBSTRING(nxt, 24, 2) END AS nextAQI
FROM C
ORDER BY id, timest;
Itzik Ben-Gan
  • 376
  • 1
  • 4
  • Thanks so much! I knew there had to be a more efficient way, but this is brilliant. You've done in one cte what I had done in three - more practice with window frame clauses I think! Cheers – user3603308 Aug 02 '15 at 08:24