Previous Date Query

[Richard Lindsay]

Hi everyone, I'm trying to create a SQL passthrough query or SQL view and
this ones driving me mad!

SQL Server 2000 for back end
Access 97 for front end

I have a table called Invoice, in which are the following fields
InvoiceNumber (Primary Key)
BookRef (indexed identifier for a client)
InvoiceDate (date of the Invoice)

Example data

InvoiceNumber, BookRef, InvoiceDate
10, 1, 31/08/2007
11, 2, 31/08/2007
12, 3, 31/08/2007
13, 1, 31/09/2007
14, 2, 31/09/2007
15, 3, 31/09/2007
16, 1, 31/10/2007
17, 2, 31/10/2007
18, 3, 31/10/2007

What I want to do is create a passthough query or view that returns the
Distinct InvoiceNumber, Bookref and PREVIOUS InvoiceDate where the
InvoiceDate is specified.

Thus when I specify InvoiceDate="31/10/2007"

This should return the following data, note the InvoiceNumber is for the
specified InvoiceDate but the PreviousInvoiceDate is from, you guessed it,
the previous invoice record for that BookRef.
InvoiceNumber, BookRef, PreviousInvoiceDate
16, 1, 31/09/2007
17, 2, 31/09/2007
18, 3, 31/09/2007

If was to specify InvoiceDate="31/09/2007"

This should return the following data
InvoiceNumber, BookRef, PreviousInvoiceDate
13, 1, 31/08/2007
14, 2, 31/08/2007
15, 3, 31/08/2007

I just can't seem to work it out
Can anyone point me in the right direction
I know you can't pass data to a view, but a hard coded example will give me
a starting point that I can convert to a passthrough.

Hope someone can help
Thanks

 

[John Spencer]

Can't test this right now, but if I remember my MS SQL query language
correctly, the query would look something like the following as a pass
through query.

SELECT InvoiceNumber, BookRef, InvoiceDate
FROM SomeTable
WHERE InvoiceDate =
(SELECT MAX(InvoiceDate)
FROM SomeTable
WHERE InvoiceDate < '2007-10-31')

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Ken Sheridan]

Try:

SELECT InvoiceNumber, BookRef,
(SELECT MAX(InvoiceDate)
FROM Invoice AS I2
WHERE I2.BookRef = I1.Bookref)
AS PreviousInvoiceDate
FROM Invoice AS I1
WHERE InvoiceDate = '2007-10-31';

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

On second thoughts:

SELECT InvoiceNumber, BookRef,
(SELECT MAX(InvoiceDate)
FROM Invoice AS I2
WHERE I2.BookRef = I1.Bookref
AND I2.InvoiceDate < I1.InvoiceDate)
AS PreviousInvoiceDate
FROM Invoice AS I1
WHERE InvoiceDate = '2007-10-31';

Ken Sheridan
Stafford, England

 

[Richard Lindsay]

Ken
I'm just doing some preliminary testing, but it looks like you've cracked
it!
Will let you know how I get on,
Thanks!
And also, thanks to John Spencer for kicking off the thread.

 

[Richard Lindsay]

Hi Ken,
Perfect, cut the run time down from 9 seconds to 1/4 second
Many thanks