Help Needed

Database
Highlighted
Enthusiast

Help Needed

Hi All,

I have the following data, I would like to have code in the blank spaces in such a way that it matches with the previous valid code.

I have tried break when but its not working.

min(code) over (partition by id, subid ORDER BY date RESET WHEN code is NUILL ) as newcode

Can some one help please.

 

Example DATA

ID SUBIDCODEDATE
80952106520F2707/11/2015
80952106520 08/11/2015
80952106520 09/11/2015
80952106520 10/11/2015
80952106520 11/11/2015
80952106520 12/11/2015
80952106520G9813/11/2015
80952106520 14/11/2015
80952106520 15/11/2015
80952106520 16/11/2015
80952106520 17/11/2015
80952106520 18/11/2015
80952106520Y1019/11/2015
80952106520 20/11/2015
80952106520 21/11/2015
80952106520 22/11/2015
80952106520 23/11/2015
80952106520 24/11/2015
80952106520 25/11/2015

 

 

Desired Output

ID SUBIDCODEDATE
80952106520F2707/11/2015
80952106520F2708/11/2015
80952106520F2709/11/2015
80952106520F2710/11/2015
80952106520F2711/11/2015
80952106520F2712/11/2015
80952106520G9813/11/2015
80952106520G9814/11/2015
80952106520G9815/11/2015
80952106520G9816/11/2015
80952106520G9817/11/2015
80952106520G9818/11/2015
80952106520Y1019/11/2015
80952106520Y1020/11/2015
80952106520Y1021/11/2015
80952106520Y1022/11/2015
80952106520Y1023/11/2015
80952106520Y1024/11/2015

Accepted Solutions
Junior Contributor

Re: Help Needed

You need the magic IGNORE NULLS option of LAST_VALUE :-)

 

If the missing code is already NULL:

LAST_VALUE(code IGNORE NULLS) over (partition by id, subid ORDER BY date) as newcode

If it's blank:

LAST_VALUE(NULLIF(code,'') IGNORE NULLS) over (partition by id, subid ORDER BY date) as newcode

 

1 ACCEPTED SOLUTION
1 REPLY
Junior Contributor

Re: Help Needed

You need the magic IGNORE NULLS option of LAST_VALUE :-)

 

If the missing code is already NULL:

LAST_VALUE(code IGNORE NULLS) over (partition by id, subid ORDER BY date) as newcode

If it's blank:

LAST_VALUE(NULLIF(code,'') IGNORE NULLS) over (partition by id, subid ORDER BY date) as newcode