Calculate days elapsed between 2 records

[bradb]

Hi, I'm new to FM so please bear with me.

I have a flat database which contains information about vehicles, specifically when they were recieved and shipped. What I need to find out is the number of days between the date recieved and date shipped. The table looks like this:

VIN Trans_Type Date

1 ARR 1/1/2005

1 xxx 1/2/2005

1 OUT 1/3/2005

2 ARR 1/2/2005

...ect...

Where ARR is the code for when the vehicle arrived, OUT is when it shipped and xxx is any number of other Transactions (Trans_type) that occured in between, meaning that there could be upwards of 20 - 30 records between the ARR and OUT.

The nice thing is that when the data is sorted by VIN, it places the Trans_Type in cronological order, thus ARR is the first record for each specific VIN and OUT is the last.

The database is quite large, over 1 million records, and I'm stuck (since I know squat and this project was dumped on my desk). I run FM pro 7 on Mac OS panther.

Any help is greatly appreciated.

Brad

[Ocean West]

Welcome Brad,

You could do a self relationship between the first record and the last record.

In the table define a field calculation text result with the word "OUT"

The relationship would be a mult-predicute relationshp between the vin number and the status & the constant key.

See attached

VIN.fp7.zip

Edited July 22, 2005 by Guest
changed file

[bradb]

Thanks Ocean but I need a bit more detail...

I can't for some reason download the example you uploaded, the error I get is:

Parse error: parse error, unexpected T_STRING in /home/fmforums/public_html/uploads/1121985808-VIN.fp7 on line 9

So I'm not sure what you mean by a relationship between the first record and the last. How do I do that for each VIN? (there are more than 100,000 of them, each with an ARR, an OUT and any number of other Trans_types stored in separate records)

My guess is that you are suggesting that I should define a self-join relationship as follows

new_relationship::VIN=VIN

&

new_relationship::Trans_type=Trans_type

&

new_relationship::Constant_key=Constant_key

where constant_key is the new calculation field with the word "OUT"

how does this help me calculate the time elapsed between the record for a Trans_Type=ARR and Trans_type=OUT for the same VIN? (sorry if this is a dumb question, or if it is obvious from the file you uploaded, but I'm new to all of this, including relational databases)

Thanks

Brad

[Ocean West]

Sorry about the download I forgot to stuff it first.

I think this will help better explain it.

(I re attached the file to the first post.)

[comment]

Actually, you could simply make the relationship

yourTable::VIN = sameVIN::VIN

Then a calc of:

Max ( Same VIN::Date ) - Min ( Same VIN::Date )

--

Note that both solutions will have refresh problems, due to double reference to related fields.

Edited July 22, 2005 by Guest

[-Queue-]

Similarly, you could sort the relationship by ascending Date and use

Last(Same VIN::Date) - Same VIN::Date

or vice versa. The speed difference in this case is probably negligible though.

You might also add a test for an 'out' record before the calc evaluates.

Let( L = Last(Same VIN::Trans_Type);

If( L = "out"; L - Same VIN::Date )

)