Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New Coder
    Join Date
    Aug 2004
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL query problem!

    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

  • #2
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,062
    Thanks
    4
    Thanked 8 Times in 8 Posts
    If your code is in VBScript use the DateAdd function to add two days to a date (or subtract) do it like so
    Code:
    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

  • #3
    New Coder
    Join Date
    Aug 2004
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    missunderstood

    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

  • #4
    Regular Coder
    Join Date
    Aug 2002
    Location
    Texas
    Posts
    287
    Thanks
    0
    Thanked 0 Times in 0 Posts
    select whateverColumns

    from yourtable

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


    You supply the required values of begDate and endDate

    fv

  • #5
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:

    Code:
    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.
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #6
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,062
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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.

    Code:
    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 & "#"
    Last edited by miranda; 09-03-2004 at 02:47 AM.

  • #7
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    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.
    I am the luckiest man in the world

  • #8
    New Coder
    Join Date
    Aug 2004
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ...

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

    any suggestions,
    thanks,...

  • #9
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,062
    Thanks
    4
    Thanked 8 Times in 8 Posts
    GzArIa,

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

    Code:
    <% 
    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
    Code:
    <% 
    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

  • #10
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    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
    Code:
    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.
    I am the luckiest man in the world


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •