How to Check if Item is available for certain Range of Date

Question

I am Building a C# Inventory app using SQL DB. Using this app you can select an Item to be booked and if its available it will booked. Sounds pretty Easy eh!, that's what I thought as well. Here is the problem. This App Needs to check for two conditions.

1) If The Item is available between certain date range (Eg: August 18th to August 23rd)

2) If the required Quantity is available (Eg: 2 or 3).

I have one Database thats hold item name and initial quantities. Another to hold current bookings.Which has following data.

ID   Cottage    quantity   From Date    To Date     Item name
 2   Woodcastle    2       2016-08-18   2016-08-24   Kayaks

Now if I select 1 Kayak(initial quantity 3) to book from 2016-08-19 to 2016-08-23. How do I do that with SQL?

Heres what I am doing so far but no luck

selecteditem = items_listbox.SelectedItem.ToString();
       from_date = from_datepicker.Value.ToString();
       to_date = to_datepicker.Value.ToString();
        quantity = quantity_need.Text;
        from_date = Convert.ToDateTime(from_date).ToString("yyyy-MM-dd");
        to_date = Convert.ToDateTime(to_date).ToString("yyyy-MM-dd");
        int init_i = 0;
        int taken_i = 0;
        int dropped_i = 0;
        string select_init = "SELECT initial_quantity from inventory_items Where item_name LIKE '" + selecteditem + "'; "+ "SELECT COALESCE(SUM(Quantity), 0) from inventory_bookings Where item_name LIKE '" + selecteditem + "' AND '" + from_date + "' between date_from and date_to; "+ "SELECT COALESCE(SUM(Quantity), 0) from inventory_bookings Where item_name LIKE '" + selecteditem + "' AND '" + to_date + "' between date_from and date_to";
        conn = new MySqlConnection( connectionstring);
        MySqlCommand init = new MySqlCommand(select_init, conn);
        try
        {
            conn.Open();
            reader = init.ExecuteReader();
            while (reader.Read())
            {
                init_i = reader.GetInt32(0);
            }
            reader.NextResult();
            while (reader.Read())
            {
               taken_i = reader.GetInt32(0);
            }
            reader.NextResult();
            while (reader.Read())
            {
                dropped_i = reader.GetInt32(0);
            }
            int quantity_avail= init_i - taken_i - dropped_i;
            if (quantity_avail >= Convert.ToInt32(quantity_need.Text))
            {
                checkresult_lbl.Text= "Currently There are "+ quantity_avail + " "+ selecteditem +"/s available in your Inventory, Please Proceed to book";
                Booking_btn.Enabled = true;
            }
           else
            {
                checkresult_lbl.Text = "Currently There are not enough available Items in your Inventory, Please change date or Quantity";
            }
         }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        finally
        {
            conn.Close();
        }

Answer 1

I was able to figure this out with this Logic.

Statement 1: "SELECT initial_quantity from inventory_items Where item_name LIKE '" + selecteditem + "'; "

Statement 2: "SELECT COALESCE(SUM(Quantity), 0) from inventory_bookings Where item_name LIKE '" + selecteditem + "' AND '" + from_date + "'<= date_to AND date_from < '"+to_date+"'"

Statement #1 selects initial value quantity of the item. (Lets Say 3).

Statement #2 selects the how many Items are out for selected days (Lets Say 2)

Then it checks if(3-2) is more than or equal to needed quantity and then book it.