Results 1 to 2 of 2

Thread: How to find un-available date on database ?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    May 2017
    Posts
    75

    How to find un-available date on database ?

    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 ?

  2. #2
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,208

    Re: How to find un-available date on database ?

    im not conversant with ms access, but i gave a try to it
    i assume that your booking table must have an primary key column and it's autoincremented field and the field name of key column is ID.

    *** NOTE THESE ***
    There are 2 queries which will facilitate to create the date sequence tables.
    Code:
    save this as query by name qdxNumber10 ( and this code is not myne )
    SELECT DISTINCT Abs([id] Mod 10) AS N
    FROM MsysObjects;
    create another query which will give you the date serials, you can manipulate this to suite your needs
    Code:
    save this query by name DateBank
    SELECT DateSerial(Year(Date()),1,1+[qdxNumber10_0].[N]+[qdxNumber10_1].[N]*10+[qdxNumber10_2].[N]*100) AS [Date]
    FROM qdxNumber10 AS qdxNumber10_0, qdxNumber10 AS qdxNumber10_1, qdxNumber10 AS qdxNumber10_2
    WHERE DateSerial(Year(Date()),1,1+qdxNumber10_0.N+qdxNumber10_1.N*10+qdxNumber10_2.N*100)<=DateSerial(Year(Date()),12,31);
    Code:
    now create a new query which will store your daywise , last booking invoce , booking count and booking id
    save it by name DayWiseMaxBooking
    SELECT Booking.date_service, Max(Booking.ID) AS MaxOfID, COUNT(Booking.ID) AS BookCount
    FROM Booking
    GROUP BY Booking.date_service;
    Code:
    now you will get datewise including with no bookings done date
    save this query as MonthlyBookings
    
    SELECT DateBank.Date, Booking.no_booking AS LastBookingId, DayWiseMaxBooking.BookCount
    FROM  (DateBank LEFT JOIN DayWiseMaxBooking ON  DateBank.Date=DayWiseMaxBooking.date_service) LEFT JOIN Booking ON  DayWiseMaxBooking.MaxOfID=Booking.ID
    WHERE DateBank.Date Between #9/1/2017# And #9/30/2017 23:59:0#
    GROUP BY DateBank.Date, Booking.no_booking, bookCount;
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

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