SQL query problem!

[GzArIa]

Hi there,

I have got a problem in defining a SQL statement. What I have at the moment is a DB with some columns one of them having the date in there. Like this...
10/05/2004
12/05/2004
15/05/2004
24/05/2004
28/05/2004
01/06/2004

Now what I want is when I am looking for a particular date let's say 23/05/2004 I want the results of the 2 records before this date and the 2 records after this date. Speak 24 & 28/05 as well as 15 & 12/05.

How should I approach that problem.
thanks for your help,...

GzArIa

[miranda]

If your code is in VBScript use the DateAdd function to add two days to a date (or subtract) do it like so

dim strDate 
strDate = "23/05/2004"
'to get two days later
DateAdd("d",2,strDate) 
Response.Write DateAdd("d",2,strDate)  ' will return 25/05/2004

'to get two days prior 
DateAdd("d",-2,strDate)
Response.Write DateAdd("d",-2,strDate)   'will return 21/05/2005

[GzArIa]

thanks for your help, but that's actually not what I am looking for, the problem I see is in creating the right SQL statement, I want all the information of the last two and the two rows before that particular date, no matter what dates these are,...

GzArIa

[fractalvibes]

select whateverColumns

from yourtable

where (yourDateField < begDate) or (yourDateField > endDate)


You supply the required values of begDate and endDate

fv

[Roy Sinclair]

You haven't said what SQL engine you're working with which can make any answer you get invalid anyway but for SQL Server I'd try something like this:

select otherfieldslist,datefield from tablename where datefield = requesteddate
union
select otherfieldslist,Min(datefield) from tablename where datefield > requesteddate
group by otherfieldslist
union
select otherfieldslist,Max(datefield) from tablename where datefield < requesteddate
group by otherfieldslist

There may be a cleaner way to get those three records but this should work.

[miranda]

Sorry, I should have been more specific about how to use this. You should use the DateAdd function to change the date to then use in the SQL statement.

Dim strDate, beginDate, endDate
strDate = "23/05/2004"   'You could also get this from a form or calendar etc...
beginDate = DateAdd("d",-2,strDate) 
endDate = DateAdd("d",2,strDate)
'this is a sql server answer  
SQL = "SELECT yourColumns FROM yourTable WHERE someDateField BETWEEN '" & beginDate & "' AND '" & endDate & "'"

'to use it in Access you need to do so like this
SQL = "SELECT yourColumns FROM yourTable WHERE someDateField BETWEEN #" & beginDate & "# AND #" & endDate & "#"

[Roelf]

i think the problem is a lot more complex. The dates in the table are not consecutive. Some are missing. I believe he (she) wants to select the two entries in the list before and the two entries in the list after a certain date, where the date doesn't have to exist in the database. So just subtracting and adding two days doesn't give the required results.

the logic should be like:
select the two dates which are closest to the entered date but before this date and select the two dates which are closest to the entered date, but after that date

I think Roy is closest, but that query only selects the first before and the first after the entered date.

[GzArIa]

Roelf's right, that's exactly what I need,...

any suggestions,
thanks,...

[miranda]

GzArIa,

How do you pull the date? through a form field? If so then you simply do it like this.

<% 
Dim strDate, beginDate, endDate
strDate = Request.form("some_form_field")
beginDate = DateAdd("d",-2,strDate) 
endDate = DateAdd("d",2,strDate)
'this is a sql server answer  
SQL = "SELECT yourColumns FROM yourTable WHERE someDateField BETWEEN '" & beginDate & "' AND '" & endDate & "'"

'to use it in Access you need to do so like this
SQL = "SELECT yourColumns FROM yourTable WHERE someDateField BETWEEN #" & beginDate & "# AND #" & endDate & "#"
%>

or like this

<% 
Dim strDate, beginDate, endDate
strDate = Request.form("some_form_field") 

beginDate = DateAdd("d",-2,strDate) 
endDate = DateAdd("d",2,strDate)
'this is a sql server answer  
SQL = "SELECT yourColumns FROM yourTable WHERE someDateField >= '" & beginDate & "' AND someDateField <= '" & endDate & "'"

'to use it in Access you need to do so like this
SQL = "SELECT yourColumns FROM yourTable WHERE someDateField >= #" & beginDate & "# AND someDateField <= #" & endDate & "#"
%>

either way what the DateAdd function does is add (subtract) a given amount of time from a certain date. GzArIa, alter the code to fit your form and try it. it will work

[Roelf]

ok, i have had some time looking into this and came up with the following:
for a table with name: Dates
a column with name: theDate
testdate: 5-17-2004

SELECT     *
FROM         (SELECT     TOP 2 *
                       FROM          Dates
                       WHERE      (theDate < '5-17-2004')
                       ORDER BY theDate DESC) DERIVEDTBL
UNION
SELECT     *
FROM         (SELECT     TOP 2 *
                       FROM          Dates
                       WHERE      (theDate > '5-17-2004')
                       ORDER BY theDate ASC) DERIVEDTBL

the trick is to select the 2 first rows where the date is after the selected date, that is easy. next you have to combine these results with the query where the 2 first rows are selected where the date is before the selected date, but for this selection the dates have to be ordered descending, otherwise the 2 first records whatsoever will be selected. There is the problem, because the union keyword does not allow the 2 querys to be sorted in different directions, so the trick is to create subquery's

ps, tested in MS-SQL server.