Modeling a table

[#1]

Hi everybody, good morning.

I have a question about modeling a table.

*Original Structure

ID | Dt_Competencia | Status

1 | 01/01/2010 | A

1 | 03/01/2010 | B

1 | 05/01/2010 | C

3 | xxxxxxxx | Y

*

Desired structure

ID | Dt_Competencia | Status

1 | 01/01/2010 | A

1 | 02/01/2010 | A //

Status must equal the previous record

1 | 03/01/2010 | B

1 | 04/01/2010 | B //

Status must equal the previous record

1 | 05/01/2010 | C

3 | xxxxxxxx | Y

Does anyone have any idea w / that I can achieve this result?

Already grateful.

[#2 · Accepted solution]

Hi there,

I don't quite understand your question, do you want to write a new status letter that matches the previous status?? or do want to order by ID, Date and Satus???

For the first case:

Load ID, Date, if(previous(ID) <> ID, Status, previous(Status)) as Status

from table

order by ID, Date;

For the second case:

Load ID, Date, Status

from table

order by ID, Date, Status;

Regards

[#3]

I need to generate the dates that are in the range between the minimum and maximum date and bring the status of the previous record

*Original Structure

ID | Dt_Competencia | Status

1 | 01/01/2010 (Min date) | A

1 | 03/01/2010 | B

1 | 05/01/2010 (max date) | C

3 | xxxxxxxx | Y

*Desired structure

ID | Date_Comp | Status

1 | 01/01/2010 (Min DATE) | A

1 | 02/01/2010 (New date) | A // Status must equal the previous record (New record)

1 | 03/01/2010 | B

1 | 04/01/2010 (New Date) | B // Status must equal the previous record (New record)

1 | 05/01/2010 Max(Date) | C

3 | xxxxxxxx | Y

Thanks