create historical query

[ardi]

Please help me, ihave problem about creating historical query.
For example, i've table :

Date_stamp Value Status
-------------------------------------------
1-Jan-05 90 Good
5-Jan-05 95 Bad
8-Jan-05 97 Good

i need query so the output from that query, like :

Date_stamp Value Status LastDate
------------------------------------------------------------
1-Jan-05 90 Good 1-Jan-05
2-Jan-05 90 Good 1-Jan-05
3-Jan-05 90 Good 1-Jan-05
4-Jan-05 90 Good 1-Jan-05
5-Jan-05 95 Bad 5-Jan-05
6-Jan-05 95 Bad 5-Jan-05
7-Jan-05 95 Bad 5-Jan-05
8-Jan-05 97 Good 8-Jan-05

i have create query with comparison date method (get minimum diff
between two date)
but it's useless because my table contain more than 100 000 rows,
when i running that query my computer is hang.

do you have any suggestion ?

thanks

ardi

 

[John Vinson]

Please help me, ihave problem about creating historical query.
For example, i've table :

Date_stamp Value Status
-------------------------------------------
1-Jan-05 90 Good
5-Jan-05 95 Bad
8-Jan-05 97 Good

i need query so the output from that query, like :

Date_stamp Value Status LastDate
------------------------------------------------------------
1-Jan-05 90 Good 1-Jan-05
2-Jan-05 90 Good 1-Jan-05
3-Jan-05 90 Good 1-Jan-05
4-Jan-05 90 Good 1-Jan-05
5-Jan-05 95 Bad 5-Jan-05
6-Jan-05 95 Bad 5-Jan-05
7-Jan-05 95 Bad 5-Jan-05
8-Jan-05 97 Good 8-Jan-05

i have create query with comparison date method (get minimum diff
between two date)
but it's useless because my table contain more than 100 000 rows,
when i running that query my computer is hang.

Could you post the SQL of this comparison query? and indicate which
fields in your table are indexed?


John W. Vinson[MVP]

 

[Marshall Barton]

Please help me, ihave problem about creating historical query.
For example, i've table :

Date_stamp Value Status
-------------------------------------------
1-Jan-05 90 Good
5-Jan-05 95 Bad
8-Jan-05 97 Good

i need query so the output from that query, like :

Date_stamp Value Status LastDate
------------------------------------------------------------
1-Jan-05 90 Good 1-Jan-05
2-Jan-05 90 Good 1-Jan-05
3-Jan-05 90 Good 1-Jan-05
4-Jan-05 90 Good 1-Jan-05
5-Jan-05 95 Bad 5-Jan-05
6-Jan-05 95 Bad 5-Jan-05
7-Jan-05 95 Bad 5-Jan-05
8-Jan-05 97 Good 8-Jan-05

i have create query with comparison date method (get minimum diff
between two date)
but it's useless because my table contain more than 100 000 rows,
when i running that query my computer is hang.

To fill in nonexistent values you need another table.
Create a table named Numbers with one field named Num.
Populate it with values 1, 2, . . ., NN where NN is greater
than the largest gap in you dates.

Then try a query like:

SELECT DateAdd("d", Num - 1, T.Date_Stamp) As DateStamp,
T.Value, T.Status, T.Date_Stamp As LastDate
FROM table As T, Numbers
WHERE DateAdd("d", Num - 1, T.Date_Stamp)
< (SELECT Min(X.Date_Stamp)
FROM table As X
WHERE X.Date_Stamp > T.Date_Stamp)