All Forums Database
k_pckiran 1 post Joined 07/15
18 Jul 2015
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...!!

 

 

yuvaevergreen 93 posts Joined 07/09
20 Jul 2015
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

 

dnoeth 4628 posts Joined 11/04
22 Jul 2015

What's your TD release?
TD14.10 supports
LAST_VALUE(C1 IGNORE NULLS) OVER (PARTITION BY RESID ORDER BY DT)

Dieter

You must sign in to leave a comment.