none
How to select specific data using query ? RRS feed

  • Question

  • 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  |
    | 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  |
    | AN06091701 |  06/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 ....

    Saturday, September 2, 2017 4:45 AM

Answers

  • I got the answer but i don't know this query fastest or not to find data that i want --

    "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"


    KHABIB MUBARAK


    Saturday, September 2, 2017 12:11 PM

All replies

  • Hi, 

    I made a new sample on our website for you.

    http://www.vb-tips.com/oledbdparametersdatetimepicker.ASPX


    Success
    Cor

    Saturday, September 2, 2017 11:04 AM
  • I got the answer but i don't know this query fastest or not to find data that i want --

    "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"


    KHABIB MUBARAK


    Saturday, September 2, 2017 12:11 PM
  • I got the answer but i don't know this query fastest or not to find data that i want --

    "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"


    KHABIB MUBARAK


    Does it make sense to use time for you. 

    If you use my sample than the data returned is 

    dim datarow = dt(3)

    If you want to use only SQL transact instead of VB, you should use the forum for that. 


    Success
    Cor


    • Edited by Cor Ligthert Saturday, September 2, 2017 1:40 PM
    Saturday, September 2, 2017 1:38 PM
  • Here is how to see if the query in question is acceptable performance-wise. Load in your test database (yes, you should have a test and production database) with more data than you expect over time. Run the query, does it return data in a timely manner? If not consider creating indexes for the field (like date_service) you will perform common search and filters on for your application.

    Consider specifying only the field(s) required for the current operation which you are not doing currently in the outer select.

    Create the query and save it in the database, run the performance analyzer on the query.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, September 2, 2017 2:03 PM
    Moderator
  • Karen,

    It triggered me. 

    However,  what does fastest mean in the question. 

    1. The fastest way to write the code
    2. The fastest way the code runs a query

    Both depends of course on the knowledge of the one who does it.

    However, mostly you go for 2 and I for 1.

    :-) 

    The speed is of course not only depending from the code. If you really want speed, you would not use MS-Access.


    Success
    Cor

    Saturday, September 2, 2017 6:10 PM
  • Karen,

    It triggered me. 

    However,  what does fastest mean in the question. 

    1. The fastest way to write the code
    2. The fastest way the code runs a query

    Both depends of course on the knowledge of the one who does it.

    However, mostly you go for 2 and I for 1.

    :-) 

    The speed is of course not only depending from the code. If you really want speed, you would not use MS-Access.


    Success
    Cor

    SQL-Server over MS-Access any day :-)

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, September 2, 2017 8:11 PM
    Moderator
  • Hi kalib i runned and test your query (i didn't had a acces db so i did on my mssql server) your query needs a date parameter in order to function well based on your requirements.  This can be done with sql and is actually a sql question :)

    First of all your query needs a date parameter , because it now gives a wrong result (no_booking is AN02091703 and not AN02091702) back.If I read your question correct you want from a selected date that has <4 no_bookings , then select the last index. We also need to assume that when we select a date and that bookings are greater then 4 (which you don't want ) we go next date that has < 4 bookings. 

    assume 5/09/2017 is the selected date (which is going to be your parameter!), but it has 4 bookings and we want to selected the one with <4 bookings , in the case we go to next date which is 6/9/2017 and have 1 one booking (AN06091701). 

    declare @table table(
    	no_booking nvarchar(50),
    	date_service date
    )
    
    
    --+------------+--------------+
    --| no_booking | date_service |
    --+------------+--------------+
    --| AN02091701 |  02/09/2017  |
    --| AN02091702 |  02/09/2017  |
    --| AN02091703 |  02/09/2017  |
    --| AN03091701 |  03/09/2017  |
    --| AN03091702 |  03/09/2017  |  
    --| AN04091701 |  04/09/2017  |
    --| AN04091702 |  04/09/2017  |
    --| AN05091701 |  05/09/2017  |
    --| AN05091702 |  05/09/2017  |
    --| AN05091703 |  05/09/2017  |
    --| AN06091701 |  06/09/2017  |
    --| AN07091701 |  07/09/2017  |
    --+------------+--------------+
    
    insert into @table(no_booking,date_service)  
    select 'AN02091701','02/09/2017 '
    union select 'AN02091702','02/09/2017'
    union select 'AN02091703', '02/09/2017'
    union select 'AN03091701','03/09/2017'
    union select 'AN03091702','03/09/2017'
    union select 'AN04091701',' 04/09/2017'
    union select 'AN04091702',' 04/09/2017'
    union select 'AN05091701',' 05/09/2017'
    union select 'AN05091702',' 05/09/2017'
    union select 'AN05091703',' 05/09/2017'	
    union select 'AN05091704',' 05/09/2017'
    union select 'AN06091701','06/09/2017 '
    union select 'AN07091701','07/09/2017 '
    
    select top 1 no_booking,date_service from @table 
    where date_service in 
    (select top 1 date_service from @table 
    where date_service >='5-9-2017'
    group by date_service 
    having count (date_service) < 4 ) 


     

    But when we now say for example the selected date is 3/09/2017  then we get AN03091702 the one you need. 

    declare @table table(
    	no_booking nvarchar(50),
    	date_service date
    )
    
    
    --+------------+--------------+
    --| no_booking | date_service |
    --+------------+--------------+
    --| AN02091701 |  02/09/2017  |
    --| AN02091702 |  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  |
    --| AN06091701 |  06/09/2017  |
    --| AN07091701 |  07/09/2017  |
    --+------------+--------------+
    
    insert into @table(no_booking,date_service)  
    select 'AN02091701','02/09/2017 '
    union select 'AN02091702','02/09/2017'
    union select 'AN02091703', '02/09/2017'
    union select 'AN03091701','03/09/2017'
    union select 'AN03091702','03/09/2017'
    union select 'AN04091701',' 04/09/2017'
    union select 'AN04091702',' 04/09/2017'
    union select 'AN05091701',' 05/09/2017'
    union select 'AN05091702',' 05/09/2017'
    union select 'AN05091703',' 05/09/2017'	
    union select 'AN05091704',' 05/09/2017'
    union select 'AN06091701','06/09/2017 '
    union select 'AN07091701','07/09/2017 '
    
    select top 1 no_booking,date_service from @table 
    where date_service in 
    (select top 1 date_service from @table 
    where date_service >='3-9-2017'
    group by date_service 
    having count (date_service) < 4 ) 
    order by no_booking desc


    • Edited by laurens vdb Sunday, September 3, 2017 9:43 PM
    Sunday, September 3, 2017 9:40 PM
  • Karen,

    It triggered me. 

    However,  what does fastest mean in the question. 

    1. The fastest way to write the code
    2. The fastest way the code runs a query

    Both depends of course on the knowledge of the one who does it.

    However, mostly you go for 2 and I for 1.

    :-) 

    The speed is of course not only depending from the code. If you really want speed, you would not use MS-Access.


    Success
    Cor

    SQL-Server over MS-Access any day :-)

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    I Think you are can help me.. visit this link 

    here

    I really need help to solved my problem..

    please thank you...


    KHABIB MUBARAK

    Tuesday, September 5, 2017 6:35 PM