none
How to find un-available date on database ? RRS feed

  • Question

  • I use access db, i use this query :
    Code:
    "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 on table booking 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 .
    assumtion we don't know un-available date..
    how to make correct query ?

    KHABIB MUBARAK

    Monday, September 4, 2017 4:34 AM

All replies

  • Hi again Khabib :) Glad to help you again. i updated your query with  of yesterday (the other forum post)  the "if not exists statement"

    I changed the query we did yesterday: If "4/09/2017" (the selected date) is not existed then we take the record which is <4/09/2017 in this case 3-09-2017 AN03091704

    In other cases it will remain the same and check for <4 no bookings (like we told yesterday in the other forum) if you select 5/09/2017 it wil take no booking AN05091703

    i hope this helps you :) 

    friendly regards 

    Laurens

     

    declare @table table(
    	no_booking nvarchar(50),
    	date_service date
    )
    
    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 '
    
    declare @selecteddate date='4-9-2017'
    
    
    
    if not exists(
    select date_service from @table 
    where date_service =@selecteddate
    
    )
    Begin 
    select top 1 no_booking,date_service from @table 
    		where date_service in 
    		(select top 1 date_service from @table 
    		where date_service <@selecteddate
    		group by date_service 
    		having count (date_service) < 4 
    		order by date_service desc) 
    	
    		order by no_booking desc
    end
    else
    begin
    		select top 1 no_booking,date_service from @table 
    		where date_service in 
    		(select top 1 date_service from @table 
    		where date_service >=@selecteddate
    		group by date_service 
    		having count (date_service) < 4 ) 
    		order by no_booking desc
    end




    • Edited by laurens vdb Monday, September 4, 2017 5:09 PM
    Monday, September 4, 2017 5:05 PM
  • Khabib, 

    You can find your exact answer on your problem in this thread, it is marked as answer. 

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/b053bf57-9a32-4dd1-8969-b1066d1da0c8/how-to-select-specific-data-using-query-?forum=vbgeneral#2639d115-053b-4300-9165-0401a7595d33

    Be aware it is a SQL transact code, adding code for that is offending this VB forum. 


    Success
    Cor


    Monday, September 4, 2017 5:37 PM
  • With all do respect Cor lightert that was his question of yesterday..... He clearly says that he wants to upgrade his sql query he did yesterday (the previous forum post he asked).

    You just linked a forum post of his question from yesterday.... that's not answer at all.

    You didn't even read his question or his problem... 

    "Be aware it is a SQL transact code, adding code for that is offending this VB forum. "

    He posted in the wrong forum it's clearly a sql question...






    • Edited by laurens vdb Monday, September 4, 2017 5:50 PM
    Monday, September 4, 2017 5:45 PM
  • Lauren,

    Many had taken some time. But he dropped in his own reply which was completely not VB in this forum and marked that as answer without telling anything more. 

    Most forum contributors take some time like you did now. At least we want some feedback. 

    If you read that thread you can see that I wrote that for his reply there was another forum.

    Because I got the idea he did not pay attentions to replies I avoided this time to give the link.

     


    Success
    Cor


    Monday, September 4, 2017 5:53 PM
  • declare @selecteddate date='4-9-2017' / 
    where date_service =@selecteddate

    what it's mean? it mean you know the date (04/09/2017) hasn't no_booking?

    if right then you use where clause to compare with selecteddate..

    i want make automatic no_booking.. in this case assumtion we don't know the date hasn't no_booking..


    KHABIB MUBARAK

    Tuesday, September 5, 2017 2:25 AM
  • i'm sorry about miss room on this forums.. next i will be aware.. and your link give to me.. its different problem..

    i'm sorry


    KHABIB MUBARAK

    Tuesday, September 5, 2017 2:27 AM
  • But you need a selected date (parameter) to calculate what no_bookings you want. Otherwise you can get wrong no_bookings back. you will need to put a variable from vb and give to the query , what i made for you is an example. 

    please don't double post on the forums

    declare @selecteddate is a parameter 


    • Edited by laurens vdb Tuesday, September 5, 2017 6:42 AM
    Tuesday, September 5, 2017 6:42 AM
  • But you need a selected date (parameter) to calculate what no_bookings you want. Otherwise you can get wrong no_bookings back. you will need to put a variable from vb and give to the query , what i made for you is an example. 

    please don't double post on the forums

    declare @selecteddate is a parameter 

    i want make automatic no_booking. how to declare @selecteddate when we don't know ( the date hasn't available on date_service)...

    ..

    first i have datetimepicker  ---> to create no_booking but this is create date_service  by request customer .haaa in this case using where clause to checking date_service available or not using value from datetimepicker.. if available will be create next no_booking (ANxxxxxx02 or more by last the index). if not available create no_booking (ANxxxxxx01 --> first time) ..

    second i have swichbutton --> to create no_booking by nearest date from current date where the date_service contains < 4 no_booking.. i don't have value from vb or i won't give value to check it .

    Example : i use this query after checking using the top query(my thread) where not find value.

    "select top 1 date_service from booking order by date_service desc"

    but this query will be only find last index of no_booking and after it i can create new no_booking (ANxxxxxx01). can't find the date hasn't available on database



    KHABIB MUBARAK


    Tuesday, September 5, 2017 9:54 AM
  • Khabib , 

    Apperently you have a datepicker controller in the UI,  the date you select from that datepicker include it as a parameter.

    Tuesday, September 5, 2017 10:47 AM
  • please read again my explanation in the comment..

    i use date picker ONLY   ON MY FIRST Method.. second method i didn't use it..

    This is my code for First method using datetimepicker to create no_booking..

     Private Sub dp1_ValueChanged(sender As Object, e As EventArgs) Handles dp1.ValueChanged
            Call koneksi()
            Dim tambah As Long
            Dim tgl = Format(Date.Now, "ddMMyy")
            Dim Dpick As String = Format(dp1.Value, "ddMMyy")
            sql = "select no_booking from booking where date_service = #" & dp1.Value.Date & "# order by no_booking desc"
            cmd = New OleDbCommand(sql, conn)
            dr = cmd.ExecuteReader
            dr.Read()
    
            If Not dr.HasRows Then
                txtnobooking.Text = "AN" + Dpick + "01"
            ElseIf dr.HasRows Then
                If Microsoft.VisualBasic.Right(dr.GetString(0), 2) > 3 Then
                    MsgBox("No Booking is Full")
                    Exit Sub
                Else
                    Dim st = Microsoft.VisualBasic.Mid(dr.GetString(0), 3, 6)
                    Dim dtl As Date = Date.ParseExact(st, "ddMMyy", System.Globalization.DateTimeFormatInfo.InvariantInfo)
                    Dim latestdate = Format(dtl, "ddMMyy")
                    tambah = Microsoft.VisualBasic.Right(dr.GetString(0), 2) + 1
                    dafnoantrean.Text = "AN" + latestdate + Microsoft.VisualBasic.Right("00" & tambah, 2)
                End If
            End If
        End Sub

    This is my code for Second method to create no_booking ..

     Public Sub nobookingautomatic()
            Call koneksi()
            Dim tambah As Long
            Dim tgl = Format(Date.Now, "ddMMyy")
            sql = "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"
            cmd = New OleDbCommand(sql, conn)
            dr = cmd.ExecuteReader
            dr.Read()
            If Not dr.HasRows Then
                sql = "select top 1 no_booking from booking order by no_booking desc"
                cmd = New OleDbCommand(sql, conn)
                dr = cmd.ExecuteReader
                dr.Read()
                If Not dr.HasRows Then
                    txnobooking.Text = "AN" + tgl + "01" 
                Else
                    Dim st = Microsoft.VisualBasic.Mid(dr.GetString(0), 3, 6)
                    Dim dtl As Date = Date.ParseExact(st, "ddMMyy", System.Globalization.DateTimeFormatInfo.InvariantInfo)
                    Dim latestdate = Format(dtl, "ddMMyy")
                    Dim dtb As DateTime = dtl.AddDays(1)
                    Dim nextdate = Format(dtb, "ddMMyy")
                    dafnoantrean.Text = "AN" + nextdate + "01"
                End If
            Else
                Dim st = Microsoft.VisualBasic.Mid(dr.GetString(0), 3, 6)
                Dim dtl As Date = Date.ParseExact(st, "ddMMyy", System.Globalization.DateTimeFormatInfo.InvariantInfo)
                Dim latestdate = Format(dtl, "ddMMyy")
                tambah = Microsoft.VisualBasic.Right(dr.GetString(0), 2) + 1
                txnobooking.Text = "AN" + latestdate + Microsoft.VisualBasic.Right("00" & tambah, 2)
            End If
           ' date service
            Dim sst = Microsoft.VisualBasic.Mid((tbnobooking.Text), 3, 6)
            Dim sdt As Date = Date.ParseExact(sst, "ddMMyy", System.Globalization.DateTimeFormatInfo.InvariantInfo)
            dp1.Text = sdt
        End Sub



    Tuesday, September 5, 2017 6:29 PM
  • Khabib 

    The easiest think to do is to provide a date picker for the customer

    "a in this case using where clause to checking date_service available or not using value from datetimepicker.. "

    Why wouldn't you use the datepicker as parameter your query will otherwise be completely wrong


    • Edited by laurens vdb Tuesday, September 5, 2017 8:24 PM
    Tuesday, September 5, 2017 6:48 PM
  • Khabib 

    The easiest think to do is to provide a date picker for the customer

    a in this case using where clause to checking date_service available or not using value from datetimepicker.. 

    Why wouldn't you use the datepicker as parameter your query will otherwise be completely wrong

    let's see my code .. i have update it

    KHABIB MUBARAK

    • Marked as answer by khabib mubarak Tuesday, September 12, 2017 10:29 AM
    • Unmarked as answer by khabib mubarak Tuesday, September 12, 2017 10:29 AM
    Tuesday, September 5, 2017 7:13 PM
  • Hi KHABIB,

    Since this forum is discussing and asking questions about the Visual Basic programming language, IDE, libraries, samples, and tools. And your issue is more related to how to spell query statement with access database. It will be more appropriate to ask your question at Access Developer Forum.

    Thank you for participating in the forum activities and thank you for your understanding.

    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 12, 2017 8:17 AM
  • Hi KHABIB,

    Since this forum is discussing and asking questions about the Visual Basic programming language, IDE, libraries, samples, and tools. And your issue is more related to how to spell query statement with access database. It will be more appropriate to ask your question at Access Developer Forum.

    Thank you for participating in the forum activities and thank you for your understanding.

    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    I see not one VBA statement in this thread, it is all VB for .Net

    Success
    Cor

    Tuesday, September 12, 2017 9:16 AM