Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Fetch Next value ignoring nulls Expand / Collapse
Author
Message
Posted Friday, July 4, 2014 8:47 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:11 AM
Points: 317, Visits: 929
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
Post #1589456
Posted Friday, July 4, 2014 10:09 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:55 AM
Points: 1,292, Visits: 3,712
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
Post #1589457
Posted Friday, July 4, 2014 11:08 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:11 AM
Points: 317, Visits: 929
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.


v1	v	v2	                prev_date	        next_date
4 10 1/1/14 10:00 AM
5 20 1/1/14 10:05 AM
6 NULL 1/1/14 10:10 AM 1/1/14 10:05 AM 1/1/14 10:25 AM
7 NULL 1/1/14 10:15 AM 1/1/14 10:05 AM 1/1/14 10:25 AM
8 NULL 1/1/14 10:20 AM 1/1/14 10:05 AM 1/1/14 10:25 AM
9 60 1/1/14 10:25 AM
10 NULL 1/1/14 10:30 AM 1/1/14 10:25 AM 1/1/14 10:40 AM
11 80 1/1/14 10:40 AM


Thanks,
Regards,
Ami
Post #1589460
Posted Saturday, July 5, 2014 1:13 AM This worked for the OP Answer marked as solution
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:55 AM
Points: 1,292, Visits: 3,712
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
Post #1589469
Posted Saturday, July 5, 2014 3:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:11 AM
Points: 317, Visits: 929
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
Post #1589484
Posted Saturday, July 5, 2014 3:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:55 AM
Points: 1,292, Visits: 3,712
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.
Post #1589485
Posted Saturday, July 5, 2014 6:18 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:11 AM
Points: 317, Visits: 929
Great help, Thanks,

Regards,
Ami
Post #1589504
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse