Return previous record value in query 1

[IanHallett]

I have a table which includes the following fields and sample values :

J_REF PeriodEndDate Value

100067 03/07/2005 1000
100067 04/07/2005 965
100067 10/07/2005 1125
100067 17/07/2005 875

I need to query the table and return all the records including the previous period end date, i.e. the PeriodEndDate value from the previous record.

Any help would be much appreciated.

 

[Golom]

Try

Select J_Ref, PeriodEndDate, 

       (Select TOP 1 T.PeriodEndDate From tbl T
        Where T.PeriodEndDate < A.PeriodEndDate
        Order By T.PeriodEndDate DESC) As PreviousEndDate

From tbl A

 

[PHV]

Something like this ?
SELECT A.J_REF, A.PeriodEndDate, A.Value, Max(B.PeriodEndDate) As PreviousDate
FROM yourTable AS A LEFT JOIN yourTable AS B ON A.J_REF = B.J_REF AND A.PeriodEndDate>B.PeriodEndDate
GROUP BY A.J_REF, A.PeriodEndDate, A.Value

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

 

[IanHallett]

Thanks PH, that worked a treat.

How could I add to this and insert a default date (e.g. 01/01/2001) if no previous date (record) existed.

Thanks.

 

[PHV]

Replace this:
Max(B.PeriodEndDate) As PreviousDate
with this:
Nz(Max(B.PeriodEndDate), #2001-01-01#) As PreviousDate

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

 

[IanHallett]

Excellent. Thanks for your help, much appreciated.