Thanks in advance, I will give it a try!
Paul Giesenhagen
QuillDesign
----- Original Message -----
From: Jochem van Dieten
To: CF-Talk
Sent: Friday, October 17, 2003 3:56 AM
Subject: Re: OT: SQL Query
Paul Giesenhagen said:
>
> I have a number of reports all dated, but dated by day (no time).
> Some of the reports are on the same day If I have an ID value, I
> need to grab the report before and the report after that ID and it
> needs to be by date.
>
> Here is what the data looks like:
>
> ReportID | ReportDate | Report |
> 1 9/10/2003
> 2 9/11/2003
> 3 9/11/2003
> 4 9/11/2003
> 5 9/12/2003
> 8 9/12/2003
> 9 9/14/2003
> 11 10/01/2003 etc..
>
> If I am looking at ReportID (5), then I want to know that reportID 4
> is previous and reportID 8 is next
>
> So my output should be
> Previous = 4
> This > > Next = 8
>
> Oh, and I need to know if previous is Nothing or Next is nothing (ie
> there isn't anymore eitherway).
It won't be evry fast, but should be acceptable if the dataset isn't
too large. The preliminary resultsets need to be limited to one row,
the code for that is between square brackets (database dependent).
(
SELECT [TOP 1] *
FROM table
WHERE ReportDate > (SELECT ReportDate FROM table WHERE ID = 5)
ORDER BY ReportDate, ID
[LIMIT 1]
UNION
SELECT *
FROM table
WHERE ID = 5)
UNION
SELECT [TOP 1] *
FROM table
WHERE ReportDate < (SELECT ReportDate FROM table WHERE ID = 5)
ORDER BY ReportDate DESC, ID DESC
[LIMIT 1]
)
ORDER BY ReportDate
Jochem
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
