Query to compare date with previous record 1

[Aietoe]

All i want to do is calculate the difference between the date field in the current record and the date field with previous record.

I have tried to modify the code from the thread "query to compare data with previous record" but i do not understand it and finaly do not get any result(but many errors).

If someone(may be ByteMyzer) could help me to modify (or create) the code.

Thanks

Aietoe

 

[PHV]

What have you so far ?
How are your rows sequenced (ordered, sorted), ie how do you retrieve a "previous" record ?
Please, post some input samples and expected result.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Aietoe]

Ok PHV, here is approximatly what my input looks like:

Name Date
Joe 2004-08-10
Joe 2004-08-27
Joe 2004-09-08
Joe 2004-09-08
.............................
sorted by name and date.

and here is what i expect:

Name Date Diff
Joe 2004-08-10 0
Joe 2004-08-27 17
Joe 2004-09-08 12
Joe 2004-09-08 0
.............................

Thanks in advance for your help.

Aietoe

 

[PHV]

Something like this ?
SELECT A.Name, A.Date,
A.Date-Nz((SELECT Max(B.Date) FROM yourTable B WHERE B.Name=A.Name AND B.Date<A.Date),A.Date) As Diff
FROM yourTable A
ORDER BY 1, 2;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Aietoe]

Thanks a lot it does exactly what i'm looking for.

Aietoe

 

[Aietoe]

Hi PHV,

Making more tests, i found out that when 2 or more records have the same date, the diff stays the same for all records.

EX: Name Date Diff
Joe 2004-08-10 0
Joe 2004-08-27 17
Joe 2004-09-08 12
Joe 2004-09-08 12
Joe 2004-09-08 12
Joe 2004-09-09 1
but what i want is:

Name Date Diff
Joe 2004-08-10 0
Joe 2004-08-27 17
Joe 2004-09-08 12
Joe 2004-09-08 0
Joe 2004-09-08 0
Joe 2004-09-09 1

Thanks

Aietoe

 

[PHV]

No other field to rank the dates ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Aietoe]

Unfortunatly no.

Aietoe!

 

[PHV]

Where are Name and Date coming from ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Aietoe]

Created by a query.

Aietoe

 

[PHV]

Could you please post the SQL code of this query to see if we can find something to help you ?
Obviously, some info on the fields in the tables used by this query would help too.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Aietoe]

Hi PHV,

Many queries are executed to endup to the final table.

If i understand what your looking for, i would have to add-up 2 additionnal fields to make the record unique.

The table would then look like this:

Name Date Act ActSeq
Joe 2004-08-10 1234 8
Joe 2004-08-27 3456 22
Joe 2004-09-08 2222 1
Joe 2004-09-08 2222 2
Joe 2004-09-08 3333 11
Joe 2004-09-09 8766 9

Would these additionnal information help you?

Aietoe

 

[PHV]

You may try this, provided Act and ActSeq are always numeric and ActSeq never>=100:
SELECT A.Name, A.Date,
A.Date-Nz((SELECT Max(B.Date) FROM yourTable B WHERE B.Name=A.Name
AND (B.Date<A.Date Or (B.Date=A.Date And 100*B.Act+B.ActSeq<100*A.Act+A.ActSeq))),A.Date) As Diff
FROM yourTable A
ORDER BY 1, 2;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Aietoe]

Many thanks and a star to PHV for his great help and his patience.

Aietoe!