Results 1 to 8 of 8

Thread: [RESOLVED] How to find nearest date using query

  1. #1

    Thread Starter
    Lively Member
    Join Date
    May 2017
    Posts
    75

    Resolved [RESOLVED] How to find nearest date using query

    ----------------------------
    + Date. + No booking +
    ----------------------------
    + 29/08/2017 + AN29081701 +
    + 29/08/2017 + AN29081702 +
    + 29/08/2017 + AN29081703 +
    + 30/08/2017 + AN30081701 +
    + 30/08/2017 + AN30081702 +
    + 01/09/2017 + AN01091701 +
    ----------------------------

    I'm using access database n oledb conn..
    how to find nearest date where count < 3..
    I want show date 30/08/2017 and no booking AN30081702..

    What I have tried:

    Sql="select nobooking,date from booking group by nobooking,date having count (date) < 3 "

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: How to find nearest date using query

    Thread moved to the 'Database Development' forum - which is where you should always post SQL questions (while SQL can be used in VB.Net, it is certainly not specific to VB.Net)

    Quote Originally Posted by khabib28 View Post
    how to find nearest date where count < 3..
    I want show date 30/08/2017 and no booking AN30081702..
    I'm afraid it isn't clear what you mean here... could you explain a bit more?

  3. #3

    Thread Starter
    Lively Member
    Join Date
    May 2017
    Posts
    75

    Re: How to find nearest date using query

    I want find no booking AN30081702 ..WHERE IN THE DATE(duplicate) having count < 3

  4. #4
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: How to find nearest date using query

    Code:
    SELECT nobooking, [date]
    FROM
    booking
    LEFT JOIN (
       SELECT [date]
         FROM booking
        GROUP BY [date]
         HAVING count([date])<3
    ) sq ON booking.[date] = sq.[date]
    ORDER BY [date] DESC, bookingid DESC
    really shouldnt have a field named date perhaps dtbooking would be better?

    can use top 1 if you only want a match
    Added an order by assuming the pk id field name to show in reverse order

    (I've not tested whether this works but should give you an idea)

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  5. #5

    Thread Starter
    Lively Member
    Join Date
    May 2017
    Posts
    75

    Re: How to find nearest date using query

    Oke dtbooking and no booking in one table an i use no booking as PK..
    Actually i want to select last index no booking where dtbooking having count < 3 .
    In this data will be select AN30081702
    I'm still confuse..

  6. #6
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: How to find nearest date using query

    My recommendation .. not sure its what you need..?
    table booking
    bookingid - pk int auto
    bookingnro - text/string
    dtbooking - date

    As to the sql I put up :
    the sub query does the check for the last index booking nro where it's not there more than two times
    This is then joined back to the table on the date...
    However as you have stated (and I forgot sorry) you needed the dup nros...

    try the following:
    Code:
    SELECT nobooking, [date]
    FROM
    booking
    LEFT JOIN (
       SELECT nobooking,[date]
         FROM booking
        GROUP BY nobooking,[date]
         HAVING count([date])<3
    ) sq ON booking.[date] = sq.[date] AND booking.nobooking = sq.nobooking
    ORDER BY [date] DESC, bookingid DESC
    This joins on both the booking nro and the date with 0 - 2 bookings using the nro.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  7. #7

    Thread Starter
    Lively Member
    Join Date
    May 2017
    Posts
    75

    Re: How to find nearest date using query

    i got the answer but i don't know how performa.. fastest or not?
    Code:
    "select top 1 no_booking from booking where date_service in (select top 1 date_service from pendaftaran group by date_service having count (date_service) < 4) order by no_booking desc"
    Last edited by khabib28; Sep 2nd, 2017 at 12:48 AM.

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: [RESOLVED] How to find nearest date using query

    That is probably as fast as you can get, or at least close enough that you wouldn't notice the difference.

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width