Fill down value to specific date by group

Question

I am having a bit of toruble with filling down some values in SQL. Here is an example of the way my data is structured.

ID  Date    Value  EndDate
A   1/1/17  500    6/1/17
A   2/1/17  489    6/1/17
A   3/1/17  480    6/1/17
A   4/1/17  475    6/1/17
A   5/1/17  460    6/1/17
A   6/1/17  0      6/1/17
A   7/1/17  0      6/1/17
B   4/1/17  784    8/1/17
B   5/1/17  801    8/1/17
B   6/1/17  0      8/1/17
B   7/1/17  0      8/1/17
B   8/1/17  0      8/1/17
B   9/1/17  0      8/1/17
B   10/1/17 0      8/1/17
C   2/1/17  980    7/1/17
C   3/1/17  564    7/1/17
C   4/1/17  647    7/1/17
C   5/1/17  500    7/1/17
C   6/1/17  0      7/1/17
C   7/1/17  0      7/1/17
C   8/1/17  0      7/1/17

For each group, I would like to fill down the value column to the point where the Date is equal to EndDate. The desired output would look like this

ID  Date    Value  EndDate
A   1/1/17  500    6/1/17
A   2/1/17  489    6/1/17
A   3/1/17  480    6/1/17
A   4/1/17  475    6/1/17
A   5/1/17  460    6/1/17
A   6/1/17  460    6/1/17
A   7/1/17  0      6/1/17
B   4/1/17  784    8/1/17
B   5/1/17  801    8/1/17
B   6/1/17  801    8/1/17
B   7/1/17  801    8/1/17
B   8/1/17  801    8/1/17
B   9/1/17  0      8/1/17
B   10/1/17 0      8/1/17
C   2/1/17  980    7/1/17
C   3/1/17  564    7/1/17
C   4/1/17  647    7/1/17
C   5/1/17  500    7/1/17
C   6/1/17  500    7/1/17
C   7/1/17  500    7/1/17
C   8/1/17  0      7/1/17

Any suggestions? Thanks!

HI xQbert. I specifically need the last non zero value to be filled down when the result set is ordered by ID and Date. Does this take that ordering into account? — Justin Klevs, Apr 19 '17 at 18:10
Oh 1 sec. I see now we' carry the prior value forward but only if the date is <= end date. I thought value contained the desired value . — xQbert, Apr 19 '17 at 18:11

score 2 · Accepted Answer · answered Apr 19 '17 at 19:23

2

You need to apply a LAST_VALUE-based logic like this: change zeroes to NULL when date <= EndDate and then look for the most recent existing value

Last_Value(CASE
             WHEN Date <= EndDate
             THEN NullIf(Value,0)
             ELSE Value
           END IGNORE NULLS)
Over (PARTITION BY ID
      ORDER BY Date)

answered Apr 19 '17 at 19:23

dnoeth

59,503
4
39
56

Not completely tested yet but at this point it looks all good! – Justin Klevs Apr 19 '17 at 19:35
NullIfZero? :-) – David דודו Markovitz Apr 20 '17 at 05:53
@DuduMarkovitz: Nope, because it's proprietary syntax and `NullIf(Value,0)` is shorter than `NullIfZero(Value)` :-) – dnoeth Apr 20 '17 at 06:05
Most functions are proprietary ... :-) – David דודו Markovitz Apr 20 '17 at 06:29
P.s. Good morning :-) – David דודו Markovitz Apr 20 '17 at 06:30

score 0 · Answer 2 · edited May 23 '17 at 11:54

0

Normally I'd use Lag() analytic, but I don't think teradata supports it so..

SELECT Id
     , Date
     , Case when Date<=EndDate then Lag(Value) over (order by ID, EndDate)
            else 0 end as value
     , EndDate
FROM Table

... so maybe... based on Teradata equivalent for lead and lag function of oracle

SELECT Id
     , Date
     , Case when Date<=EndDate then coalesce(MAX(Value) 
            over (Partition by 1 order by ID, EndDate ROWS BETWEEN 1 PRECEDING and 1 PRECEDING),0)
            else 0 end as value
     , EndDate
FROM Table

I've no test bed for teradata so you'll have to let us know if it works. or what error you encounter

edited May 23 '17 at 11:54

Community

1
1

answered Apr 19 '17 at 18:15

xQbert

34,733
2
41
62

Checking on this now. Thanks! – Justin Klevs Apr 19 '17 at 18:33
the 2nd one may get some false positives due to the coalese but I wasn't sure how you wanted to handle situations when preceding value didn't exist.... – xQbert Apr 19 '17 at 18:44

Fill down value to specific date by group

2 Answers2