-
Aug 30th, 2017, 06:16 AM
#1
Thread Starter
Lively Member
[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 "
-
Aug 30th, 2017, 06:22 AM
#2
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)
Originally Posted by khabib28
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?
-
Aug 30th, 2017, 06:45 AM
#3
Thread Starter
Lively Member
Re: How to find nearest date using query
I want find no booking AN30081702 ..WHERE IN THE DATE(duplicate) having count < 3
-
Aug 30th, 2017, 06:56 AM
#4
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)
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...
-
Aug 30th, 2017, 07:10 AM
#5
Thread Starter
Lively Member
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..
-
Aug 30th, 2017, 08:32 AM
#6
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.
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...
-
Sep 1st, 2017, 11:58 PM
#7
Thread Starter
Lively Member
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.
-
Sep 2nd, 2017, 07:29 AM
#8
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|