Data Modeling w/ RangeDate

[#1]

Hi there,

I'm having difficulty in reaching the following result of modeling.

Original Structure

ID	DT_COMPETENCIA	STATUS
1	01/01/2010	A
1	03/01/2010	B
1	05/01/2010	C
2	01/01/2010	A
2	04/01/2010	B
3	31/12/2009	A
3	02/01/2010	B
3	04/04/2010	C

Final Structure

ID	DT_COMPETENCIA	STATUS
1	01/01/2010	A
1	02/01/2010	A
1	03/01/2010	B
1	04/01/2010	B
1	05/01/2010	C
2	01/01/2010	A
2	02/01/2010	A
2	03/01/2010	A
2	04/01/2010	B
3	31/12/2009	A
3	01/01/2010	A
3	02/01/2010	B
3	03/01/2010	B
3	04/04/2010	C

Legend
***	New records in Bold
NewRecords(Status) = Previous(Status)
	Range Date ID= 1
Min	01/01/2010
Max	05/01/2010
	Range Date ID= 2
Min	01/01/2010
Max	04/01/2010
	Range Date ID= 3
Min	31/12/2009
	04/04/2010

[;)]

Can anyone help me?

Thanks!!

[pover]

It would be easier to create a new columna and use intervalmatch to link to a master calendar table. So using previous() or peek() you can get a table like the following:

ID	DT_COMPETENCIA TO_DATE	STATUS
1	01/01/2010 02/01/2010	A
1	03/01/2010 04/01/2010	B
1	05/01/2010 31/12/9999	C
2	01/01/2010 03/01/2010	A
2	04/01/2010 31/12/9999	B
3	31/12/2009 01/0/2010	A
3	02/01/2010 03/04/2010	B
3	04/04/2010 31/12/9999	C

Instead of 31/12/9999 you could put null() and use the command nullasvalue TO_DATE so that the range is infinite.

Then use the intervalmatch function to link to a master calendar or a table that has all the dates.

Regards.

[#3]

Hi Karl,

Thanks for your attention.

Sorry about my english

But I need to create a line for each date within the range

Min_Date = Date('01/01/2010','DD/MM/YYYY') >> Sales_Date

Max_Date = Date('03/01/2010','DD/MM/YYYY') >> Sales_Date

Something like that:

_______________

Original Structure_

ID DATE STATUS

1 01/01/2010 A

1 03/01/2010 A

2 03/01/2010 B

2 06/01/2010 A

_________________________________________

4 RECORDS

_____________

Final Structure_

ID DATE STATUS

1 01/01/2010 A

1 02/01/2010 A (New Record)

1 03/01/2010 A

2 03/01/2010 B

2 04/01/2010 B (New Record)

2 05/01/2010 B (New Record)

2 06/01/2010 A

_________________________________________

7 RECORDS

Best regards

[pover]

Vitor,

I guess I don't understand why you need to create those extra rows when you can get the same end result using what I recommended as you can see in the attached file.

Regards.

[johnw]

And if you really want all those extra rows, you can join the tables together at the end. I'm not sure there's a need, though.