July 4, 2014 at 8:47 pm
Hi,
I'm trying to find the previous / next values of a field when it is having null values. I'm not able to use lag / lead as they don't ignore nulls.
declare @t table (v1 int , v int, v2 datetime)
insert into @t values(4,10, '2014-01-01 10:00:00')
insert into @t values(5, 20,'2014-01-01 10:05:00')
insert into @t values(6, null, '2014-01-01 10:10:00')
insert into @t values(7, null,'2014-01-01 10:15:00')
insert into @t values(8, null,'2014-01-01 10:20:00')
insert into @t values(9,60, '2014-01-01 10:25:00')
insert into @t values(10, null, '2014-01-01 10:30:00')
insert into @t values(11, 80, '2014-01-01 10:40:00')
select T.v1,
v, NV,
first_value(T.v)
over(partition by T.NV
order by T.v1
rows between unbounded preceding and current row) as prevvalue,
first_value(t.v)
over (partition by NV+1
order by t.v1
range between current row and unbounded following) as nextvalue,
V2
from (
select v1,v2, v,
sum(case when v is null then 0 else 1 end)
over( order by v1) as NV
from @t
) as T order by t.v1
what is the mistake that i'm doing here? The next values are having nulls. i'm trying to get next value of 'v' ignoring nulls
Thanks,
Ami
July 4, 2014 at 10:09 pm
Quick question, what is the expected output?
You could use MAX with the right window specification
😎
USE tempdb;
GO
declare @t table (v1 int , v int, v2 datetime)
insert into @t values(4,10, '2014-01-01 10:00:00')
insert into @t values(5, 20,'2014-01-01 10:05:00')
insert into @t values(6, null, '2014-01-01 10:10:00')
insert into @t values(7, null,'2014-01-01 10:15:00')
insert into @t values(8, null,'2014-01-01 10:20:00')
insert into @t values(9,60, '2014-01-01 10:25:00')
insert into @t values(10, null, '2014-01-01 10:30:00')
insert into @t values(11, 80, '2014-01-01 10:40:00')
SELECT
*
,MAX(X.v) OVER (PARTITION BY X.XX) AS PREC_VAL
,MAX(X.v) OVER (PARTITION BY X.YY) AS FOLL_VAL
FROM
(
SELECT
TT.v1
,TT.v
,TT.v2
,SUM(CASE WHEN TT.v IS NULL THEN 0 ELSE 1 END) OVER
(
ORDER BY TT.v1
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS XX
,SUM(CASE WHEN TT.v IS NULL THEN 0 ELSE 1 END) OVER
(
ORDER BY TT.v1 DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS YY
FROM @t TT
) AS X
ORDER BY 1
Results
v1 v v2 XX YY PREC_VAL FOLL_VAL
----------- ----------- ----------------------- ----------- ----------- ----------- -----------
4 10 2014-01-01 10:00:00.000 1 4 10 10
5 20 2014-01-01 10:05:00.000 2 3 20 20
6 NULL 2014-01-01 10:10:00.000 2 2 20 60
7 NULL 2014-01-01 10:15:00.000 2 2 20 60
8 NULL 2014-01-01 10:20:00.000 2 2 20 60
9 60 2014-01-01 10:25:00.000 3 2 60 60
10 NULL 2014-01-01 10:30:00.000 3 1 60 80
11 80 2014-01-01 10:40:00.000 4 1 80 80
July 4, 2014 at 11:08 pm
Many Thanks,
it works, one more question,
The same way, I would like to bring the date column also. The expected output is, if the value of 'v' is null then i want the prev 'date' value column where the value 'v' is not null. And the same row i want the next date where 'v' is not null.
v1vv2 prev_date next_date
4101/1/14 10:00 AM
5201/1/14 10:05 AM
6NULL1/1/14 10:10 AM1/1/14 10:05 AM1/1/14 10:25 AM
7NULL1/1/14 10:15 AM1/1/14 10:05 AM1/1/14 10:25 AM
8NULL1/1/14 10:20 AM1/1/14 10:05 AM1/1/14 10:25 AM
9601/1/14 10:25 AM
10NULL1/1/14 10:30 AM1/1/14 10:25 AM1/1/14 10:40 AM
11801/1/14 10:40 AM
Thanks,
Regards,
Ami
July 5, 2014 at 1:13 am
Use the same method, just null the date in the subquery
😎
USE tempdb;
GO
declare @t table (v1 int , v int, v2 datetime)
insert into @t values(4,10, '2014-01-01 10:00:00')
insert into @t values(5, 20,'2014-01-01 10:05:00')
insert into @t values(6, null, '2014-01-01 10:10:00')
insert into @t values(7, null,'2014-01-01 10:15:00')
insert into @t values(8, null,'2014-01-01 10:20:00')
insert into @t values(9,60, '2014-01-01 10:25:00')
insert into @t values(10, null, '2014-01-01 10:30:00')
insert into @t values(11, 80, '2014-01-01 10:40:00')
SELECT
X.v1
,X.v
,X.v2
,MAX(X.v) OVER (PARTITION BY X.XX) AS PREC_VAL
,MAX(X.v) OVER (PARTITION BY X.YY) AS FOLL_VAL
,MAX(X.DT_V) OVER (PARTITION BY X.XX) AS PREC_V2
,MAX(X.DT_V) OVER (PARTITION BY X.YY) AS FOLL_V2
FROM
(
SELECT
TT.v1
,TT.v
,TT.v2
,CASE WHEN TT.v IS NOT NULL THEN TT.v2 ELSE NULL END AS DT_V
,SUM(CASE WHEN TT.v IS NULL THEN 0 ELSE 1 END) OVER
(
ORDER BY TT.v1
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS XX
,SUM(CASE WHEN TT.v IS NULL THEN 0 ELSE 1 END) OVER
(
ORDER BY TT.v1 DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS YY
FROM @t TT
) AS X
ORDER BY 1
Results
v1 v v2 PREC_VAL FOLL_VAL PREC_V2 FOLL_V2
----------- ----------- ----------------------- ----------- ----------- ----------------------- -----------------------
4 10 2014-01-01 10:00:00.000 10 10 2014-01-01 10:00:00.000 2014-01-01 10:00:00.000
5 20 2014-01-01 10:05:00.000 20 20 2014-01-01 10:05:00.000 2014-01-01 10:05:00.000
6 NULL 2014-01-01 10:10:00.000 20 60 2014-01-01 10:05:00.000 2014-01-01 10:25:00.000
7 NULL 2014-01-01 10:15:00.000 20 60 2014-01-01 10:05:00.000 2014-01-01 10:25:00.000
8 NULL 2014-01-01 10:20:00.000 20 60 2014-01-01 10:05:00.000 2014-01-01 10:25:00.000
9 60 2014-01-01 10:25:00.000 60 60 2014-01-01 10:25:00.000 2014-01-01 10:25:00.000
10 NULL 2014-01-01 10:30:00.000 60 80 2014-01-01 10:25:00.000 2014-01-01 10:40:00.000
11 80 2014-01-01 10:40:00.000 80 80 2014-01-01 10:40:00.000 2014-01-01 10:40:00.000
July 5, 2014 at 3:17 am
declare @t table (v1 int , v int, v2 datetime)
insert into @t values(4,10, '2014-01-01 10:00:00')
insert into @t values(5, 20,'2014-01-01 10:05:00')
insert into @t values(6, null, '2014-01-01 10:10:00')
insert into @t values(7, null,'2014-01-01 10:15:00')
insert into @t values(8, null,'2014-01-01 10:20:00')
insert into @t values(9,60, '2014-01-01 10:25:00')
insert into @t values(10, null, '2014-01-01 10:30:00')
insert into @t values(11, 80, '2014-01-01 10:40:00')
--select * from @t
SELECT
v1,v, v2
,MAX(X.v) OVER (PARTITION BY X.XX) AS PREC_VAL
,MAX(X.v) OVER (PARTITION BY X.YY) AS FOLL_VAL
,MIN(X.V2) OVER (PARTITION BY X.XX ORDER BY X.V DESC) AS PDate
, MAX(X.V2) OVER (PARTITION BY X.YY ORDER BY X.V DESC) AS NDate
FROM
(
SELECT
TT.v1
,TT.v
,TT.v2
,SUM(CASE WHEN TT.v IS NULL THEN 0 ELSE 1 END) OVER
(
ORDER BY TT.v1
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS XX
,SUM(CASE WHEN TT.v IS NULL THEN 0 ELSE 1 END) OVER
(
ORDER BY TT.v1 DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS YY
FROM @t TT
) AS X
ORDER BY 1
Thanks a lot!!
I've tried the above solution. Do you foresee any issues with the above one?
Thanks,
Regards,
Ami
July 5, 2014 at 3:24 am
Only one thing, if the date value of the null v entry is either the earliest or the latest within the partition, it will not be ignored. Setting it to null in the subquery will guarantee that it gets ignored.
😎
July 5, 2014 at 6:18 am
Great help, Thanks,
Regards,
Ami
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply