So the question is this, since my dates are duplicated, this may be impossible correct? What other solutions could be possible? I would hate to put in a sort method as below, it would be unconventional .. though it would work ... and it would then sync everything up .. would this be poor practice? I am assuming yes. IF not, would there be a better way to do it?
SELECT reportID
FROM beaverReports
ORDER BY tripDate DESC
<cfloop query="thisQuery">
UPDATE beaverReports
SET tripSort = #thisQuery.currentRow#
WHERE reportID = #thisQuery.reportID#
</cfloop>
Paul Giesenhagen
QuillDesign
----- Original Message -----
From: Tony Weeg
To: CF-Talk
Sent: Friday, October 17, 2003 12:01 PM
Subject: RE: OT: SQL Query
does this work?
SELECT TOP 1 reportID
FROM beaverReports
WHERE tripDate >
(SELECT tripDate FROM beaverReports WHERE reportID =
#variables.thisReport#)
UNION
SELECT reportID
FROM beaverReports
WHERE reportID = #variables.thisReport#
UNION
SELECT TOP 1 reportID
FROM beaverReports
WHERE tripDate <
(SELECT tripDate FROM beaverReports WHERE reportID =
#variables.thisReport#)
ORDER BY tripDate
same thing minus the beginning ( and ending ) you had followed by a
straggler order by tripDate?
...tony
tony weeg
senior web applications architect
navtrak, inc.
www.navtrak.net
[EMAIL PROTECTED]
410.548.2337
-----Original Message-----
From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]
Sent: Friday, October 17, 2003 12:40 PM
To: CF-Talk
Subject: Re: OT: SQL Query
Ok here is the query that I have ... and the error that is showing up. I am
using MS SQL 2000
(
SELECT TOP 1 reportID
FROM beaverReports
WHERE tripDate >
(SELECT tripDate FROM beaverReports WHERE reportID =
#variables.thisReport#)
UNION
SELECT reportID
FROM beaverReports
WHERE reportID = #variables.thisReport#)
UNION
SELECT TOP 1 reportID
FROM beaverReports
WHERE tripDate <
(SELECT tripDate FROM beaverReports WHERE reportID =
#variables.thisReport#)
)
ORDER BY tripDate
ERROR:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 7: Incorrect syntax near
')'.
SQL = "SELECT TOP 1 * FROM beaverReports WHERE tripDate > (SELECT tripDate
FROM beaverReports WHERE reportID = 1032) UNION SELECT * FROM beaverReports
WHERE reportID = 1032) UNION SELECT TOP 1 * FROM beaverReports WHERE
tripDate < (SELECT tripDate FROM beaverReports WHERE reportID = 1032) ORDER
BY tripDate"
Thanks for the help!
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]
