none
Cannot figure out SQL query

    Question

  • Am hoping someone can help me work out how to perform this query.

    I have a hotel database that has a record for every day and every room.

    I am trying to run a query to find rooms in the table that are available between specific dates, but I need them to be available on every date within the range. If it is booked for one day in between the range it cannot count.

    string customerDateIn = checkIn.Date.ToString("MM.dd.yyyy"); string customerDateOut = checkOut.Date.ToString("MM.dd.yyyy"); TimeSpan diff = checkOut - checkIn; var days = diff.TotalDays.ToString(); MessageBox.Show(days); SqlCommand available = new SqlCommand("SELECT COUNT(DISTINCT RoomNumber)

    FROM Reservations WHERE Type = " + i + " AND Available='Y' AND date >='"

    + customerDateIn + "' AND date <='" + customerDateOut + "';", c);

    roomTypesAv[i] = ((int)available.ExecuteScalar());

    Can anyone amend this query so that it only counts towards the count if the room availability is set to 'Y' on every single date within the range?

    Thanks!

    i = the room type which changes during the loop


    • Edited by Fearghal Wednesday, November 20, 2013 10:57 AM
    Wednesday, November 20, 2013 10:52 AM

Answers

  • With simple execute command it might be difficult to achieve what you are looking.

    Can you put the logic in to a stored proc and call that with the dates ?

    Below is one sample query , as you didn't provide any table info have dummy tables, change the query according to your table structure.

    Create table Reservations ( RoomNumber int,type int,Available char(1),date date)
    insert into Reservations values (1,1,'Y','26 nov 2013')
    insert into Reservations values (1,1,'Y','27 nov 2013')
    insert into Reservations values (1,1,'Y','28 nov 2013')
    insert into Reservations values (1,1,'Y','29 nov 2013')
    insert into Reservations values (2,1,'Y','26 nov 2013')
    insert into Reservations values (2,1,'Y','27 nov 2013')
    insert into Reservations values (2,1,'Y','29 nov 2013')
    
    go
    declare @customerDateIn  date ='26 nov 2013', @customerDateOut date='28 nov 2013'
    
    ;with cte
    as 
    (
    select @customerDateIn as customerDateIn
    union all
    select Dateadd(DAY,1,customerDateIn) from cte where customerDateIn<@customerDateOut
    )
    select B.RoomNumber from cte A 
    left join Reservations B
    ON A.customerDateIn=B.date
    WHERE B.Type = 1 AND B.Available='Y'
    group by B.RoomNumber
    having COUNT(*)=  (select COUNT(*) from cte)
    


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, November 20, 2013 12:37 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. Here is my guess done without any help from you. It would also help if you posted SQL. There is no generic, magical “type” in RDBMS; it is a particular “<something>_type”; this is basic data modeling. We do not use assembly languages flags, etc. 

    Here is a correct schema. Do you know what a Calendar table is? 

    CREATE TABLE Reservations
    (room_nbr CHAR(4) NOT NULL,
     check_in_date DATE NOT NULL,
     PRIMARY KEY (room_nbr, check_in_date),
     check_out_date DATE,
     CHECK (check_in_date <= check_out_date));

    You do not know about the BETWEEN predicate, so you still write SQL like you did C or BASIC with theta operators. Do you know what a Calendar table is? Find the calendar dates in the reporting period which are also in the reservation period. If the count of days match, then this room was fully occupied.

    CREATE PROCEDURE Get_Room_Cnt
    (@in_start_date DATE, @in_end_date DATE)
    AS
    SELECT @in_start_date AS start_date, @in_end_date AS end_date,
           COUNT(DISTINCT R.room_nbr) AS full_occupancy_cnt 
      FROM Reservations AS R, Calendar AS C
     WHERE C.cal_date BETWEEN @in_start_date AND @in_end_date 
       AND C.cal_date BETWEEN R.check_in_date 
               AND COALESCE (R.check_out_date, CURRENT_TIMESTAMP)
    HAVING COUNT(C.cal_date)
           = DATEDIFF(DAYS, @in_start_date, @in_end_date);

    Untested; you posted no sample data as per Netiquette.  Also, SQL uses <> and not !=.  This tells me that you have not learned to think in SQL yet. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, November 20, 2013 4:15 PM

All replies

  • Below is not the cleaner way to achieve it, but this is how you can get the count

    SELECT * FROM (SELECT roomno, MIN(date) AS mindate, MAX(date) AS maxdate
    FROM dbo.reservation
    WHERE available = 'Y'
    AND date between '2013-11-20' AND '2013-11-23'
    GROUP BY roomno) a
    WHERE mindate = '2013-11-20' and maxdate = '2013-11-23'


    Hope this will help

    EDITED: I have created a SQL Fiddler example for the same

    http://sqlfiddle.com/#!6/9a861/2

    • Edited by Anuj Tripathi Wednesday, November 20, 2013 11:32 AM SQL Fiddler example
    Wednesday, November 20, 2013 11:02 AM
  • You can try to use a subquery with a COUNT for each room to check if there is a not available day in a period. If count = 0, all days are available. Something like:

    SELECT COUNT(*) FROM Reservations

    WHERE Available='N' AND (date >='"

    + customerDateIn + "' AND date <='" + customerDateOut) AND Room = n;

    Wednesday, November 20, 2013 11:15 AM
  • With simple execute command it might be difficult to achieve what you are looking.

    Can you put the logic in to a stored proc and call that with the dates ?

    Below is one sample query , as you didn't provide any table info have dummy tables, change the query according to your table structure.

    Create table Reservations ( RoomNumber int,type int,Available char(1),date date)
    insert into Reservations values (1,1,'Y','26 nov 2013')
    insert into Reservations values (1,1,'Y','27 nov 2013')
    insert into Reservations values (1,1,'Y','28 nov 2013')
    insert into Reservations values (1,1,'Y','29 nov 2013')
    insert into Reservations values (2,1,'Y','26 nov 2013')
    insert into Reservations values (2,1,'Y','27 nov 2013')
    insert into Reservations values (2,1,'Y','29 nov 2013')
    
    go
    declare @customerDateIn  date ='26 nov 2013', @customerDateOut date='28 nov 2013'
    
    ;with cte
    as 
    (
    select @customerDateIn as customerDateIn
    union all
    select Dateadd(DAY,1,customerDateIn) from cte where customerDateIn<@customerDateOut
    )
    select B.RoomNumber from cte A 
    left join Reservations B
    ON A.customerDateIn=B.date
    WHERE B.Type = 1 AND B.Available='Y'
    group by B.RoomNumber
    having COUNT(*)=  (select COUNT(*) from cte)
    


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, November 20, 2013 12:37 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. Here is my guess done without any help from you. It would also help if you posted SQL. There is no generic, magical “type” in RDBMS; it is a particular “<something>_type”; this is basic data modeling. We do not use assembly languages flags, etc. 

    Here is a correct schema. Do you know what a Calendar table is? 

    CREATE TABLE Reservations
    (room_nbr CHAR(4) NOT NULL,
     check_in_date DATE NOT NULL,
     PRIMARY KEY (room_nbr, check_in_date),
     check_out_date DATE,
     CHECK (check_in_date <= check_out_date));

    You do not know about the BETWEEN predicate, so you still write SQL like you did C or BASIC with theta operators. Do you know what a Calendar table is? Find the calendar dates in the reporting period which are also in the reservation period. If the count of days match, then this room was fully occupied.

    CREATE PROCEDURE Get_Room_Cnt
    (@in_start_date DATE, @in_end_date DATE)
    AS
    SELECT @in_start_date AS start_date, @in_end_date AS end_date,
           COUNT(DISTINCT R.room_nbr) AS full_occupancy_cnt 
      FROM Reservations AS R, Calendar AS C
     WHERE C.cal_date BETWEEN @in_start_date AND @in_end_date 
       AND C.cal_date BETWEEN R.check_in_date 
               AND COALESCE (R.check_out_date, CURRENT_TIMESTAMP)
    HAVING COUNT(C.cal_date)
           = DATEDIFF(DAYS, @in_start_date, @in_end_date);

    Untested; you posted no sample data as per Netiquette.  Also, SQL uses <> and not !=.  This tells me that you have not learned to think in SQL yet. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, November 20, 2013 4:15 PM