Fill up data from latest previous data untill it changes

Database

Fill up data from latest previous data untill it changes

Hi,

I got the source data like following

Table Data

RESID|         DT |           C1 | C2       |        C3            | C4

1        |    12/3/2014   | OF |  USD                             |   12

1        |    12/10/2014 | BK

1        |    12/15/2014 | CX |              12/15/2014      | 15

1        |    12/18/2014 | CT

I need to create a daily incremental file as show below changes on particular day and bringing other data from the previous availale (changed) date

Desired Result

RESID |  DT       | C1    | C2  |        C3          | C4

1 | 12/3/2014    |OF     |  USD   |                      | 12

1 | 12/10/2014  |BK     |  USD   |                      | 12

1 12/15/2014    |CX     |  USD   | 12/15/2014   | 15

1 12/18/2014    |CT     |  USD   | 12/15/2014   | 15

Help...!!

2 REPLIES
Enthusiast

Re: Fill up data from latest previous data untill it changes

SEL RESID,DT,
COALESCE(C1,MIN(C1) OVER (PARTITION BY RESID ORDER BY DT ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)),
COALESCE(C2,MIN(C2) OVER (PARTITION BY RESID ORDER BY DT ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)),
COALESCE(C3,MIN(C3) OVER (PARTITION BY RESID ORDER BY DT ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)),
COALESCE(C4,MIN(C4) OVER (PARTITION BY RESID ORDER BY DT ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING))
FROM P2
Senior Apprentice

Re: Fill up data from latest previous data untill it changes

What's your TD release?

TD14.10 supports

LAST_VALUE(C1 IGNORE NULLS) OVER (PARTITION BY RESID ORDER BY DT)