Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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