How to select spesific data

Question

1.00/5 (2 votes)

See more:

first I'm using Access database and ole db conn.

I have table Booking :

+------------+--------------+
| no_booking | date_service |
+------------+--------------+
| AN02091701 | 02/09/2017 |
| AN02091702 | 02/09/2017 |
| AN02091703 | 02/09/2017 |
| AN02091703 | 02/09/2017 |
| AN03091701 | 03/09/2017 |
| AN03091702 | 03/09/2017 | ---? find AN03091702
| AN04091701 | 04/09/2017 |
| AN04091702 | 04/09/2017 |
| AN05091701 | 05/09/2017 |
| AN05091702 | 05/09/2017 |
| AN05091703 | 05/09/2017 |
| AN07091701 | 07/09/2017 |
+------------+--------------+

I want make bookingservice ..

i'm using date picker to select date as request member (exactly will be randomly)..

condition that i want --> in one day only have < 4 no_booking

next i try to find the date which have < 4 no_booking to create no_booking automatic.

assumtion that we don't now where the date have < 4 no_booking ...

how to find the top date that contain < 4 no_booking ( the date is 03/09/2017 because have 2 no_booking),

then select the lastindex of no_booking in 03/09/2017 ----> exactly AN03091702 . using fastest query ?

Hope I explain correctly,but sorry about my english ..

Thanks ....

What I have tried:

SQL

select no_booking,datebo from booking group by no_booking,datebo having count (datebo) < 3

Posted 30-Aug-17 3:28am

Khabibb Mubarakk

Updated 3-Sep-17 17:46pm

v6

Add a Solution

Comments

Richard Deeming 1-Sep-17 14:33pm

REPOST
You have already posted this:
https://www.codeproject.com/Questions/1203803/How-to-find-nearest-date-using-query-VB-NET[^]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jochen Arndt · Answer 1 · 2017-08-30T03:50:00

Solution 1

If you need to select only columns containing a specific value you must use a WHERE clause:

SQL

SELECT nobooking,datebo FROM booking WHERE nobooking='AN30081702' GROUP BY nobooking,datebo HAVING COUNT(datebo) < 3;

If the column name contains a space ("No Booking" instead of "nobooking") or a period, you have to enclose it by quotes or brackets. If you want to return all columns from a table, you can also use the * placeholder:

SQL

SELECT * FROM booking WHERE [No booking]='AN30081702' GROUP BY [No booking],[datebo.] HAVING COUNT([datebo.]) < 3;

As you can see it is a bad idea to uses spaces and dots in database table and column names. Avoid them (and SQL keywords too) as names if possible.

Posted 30-Aug-17 3:50am

Jochen Arndt

Updated 30-Aug-17 3:52am

v2

Comments

Khabibb Mubarakk 30-Aug-17 19:11pm

assumption that you don't how the no_boking,because will be so many data..
first select no booking where datebo having count < 3 :
so will be index like this :
--------------
+ No_booking +
--------------
+ AN30081701 + ---> this have date.bo (30/80/2017) top of date
+ AN30081702 + ---> (30/80/2017)
+ AN01091701 +
+ AN02091701 +
--------------
then select last index no_booking in the top of date
so will be select : AN30081702
I hope u understand my explanation.
thanks

Graeme_Grant 30-Aug-17 19:54pm

Use a UNION ALL to join results from two queries: 1 for no bookings, one for bookings. See this solution from earlier today: Search for a single value in multiple tables[^] - the principle is the same.

Khabibb Mubarakk 3-Sep-17 23:54pm

hay Grant ,,I want ask to you .
after i solved it using this query :

"select top 1 no_booking from booking where date_service in (select top 1 date_service from booking group by date_service having count (date_service) < 4) order by no_booking desc"

, i got new problem !
when the condition data like this :
+------------+--------------+
| no_booking | date_service |
+------------+--------------+
| AN02091701 | 02/09/2017 |
| AN02091702 | 02/09/2017 |
| AN02091703 | 02/09/2017 |
| AN02091704 | 02/09/2017 |
| AN03091701 | 03/09/2017 |
| AN03091702 | 03/09/2017 |
| AN03091703 | 03/09/2017 |
| AN03091704 | 03/09/2017 | --> date_service 04/09/2017 (is doesn't have no_booking)
| AN05091701 | 05/09/2017 |
| AN05091702 | 05/09/2017 |
| AN05091703 | 05/09/2017 |
| AN07091701 | 07/09/2017 |
+------------+--------------+
i know my query just select to AN05091703 on date 05/09/2017,and the next command i make next no_booking AN05091704 .
but i want too select or find date 04/09/2017,and then make new no_booking AN04091701 .
how to make correct query ?

Graeme_Grant 3-Sep-17 23:58pm

Use a CASE Statement[^] for a custom field to identify if it has a booking or not.

Khabibb Mubarakk 4-Sep-17 0:02am

but firstly i must find the date 04/09/2017 ???

Graeme_Grant 4-Sep-17 0:07am

Define it in the SELECT part of the SQL query.. all explained in the link provided.

Khabibb Mubarakk 4-Sep-17 0:10am

define ? it means we know the date which hasn't no_booking.
assumtion that we don't know it

Graeme_Grant 4-Sep-17 0:13am

Not if the case calculated field is labelled HasBooking (bool)... Again, all answered in the link provided. You just have to read up and do the work.

This is a new question so I'll be stopping here. Consider it closed. Any further information or question require starting a new one.

Khabibb Mubarakk 4-Sep-17 0:16am

oke Grant ..thank you

Jochen Arndt 31-Aug-17 3:22am

I have just answered the initial question based on the available information.
At that time the shown column names where "databo." and "no booking" and it was not clear that the ANS should be the last from the earliest date whith less than 3 bookings on that day (I hope that is what you want).

So you can use a UNION as suggested by Graeme_Grant. It might be also possible to get the required result by sorting first for date (earliest on top) and then for no_booking (recent on top).