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!
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