none
SQL - Getting data outside of a date range

    Question

  • Hi,

    I'm wondering if anyone can help with my query please:

    I have created a calender report using a recursive CTE query to show all bookings made against rooms for a care home. In my final SELECT list I have a date range between getdate() and getdate()+7 (to give a weekly calendar view). The problem is, any data that falls outside of this date range doesn't appear (which of course it wouldn't).

    The issue is, I have to still supply the full list of rooms. So I have rooms 1-30. These need to be displayed at all times regardless if there isn't any data for a particular room for the week.

    So the ideal scenario is:

    1. All rooms displayed regardless if a booking has been made in the week or not.

    Does anyone have any ideas?

    Thanks.

    Thursday, April 04, 2013 10:00 AM

Answers

  • For this sort of problem you want a calendar table.  See

    http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

    for what a calendar table is and some of its many uses.

    Once you have one, you can get a result that has a row for every room for every day between GetDate() and GetDate() + 7 just by (assuming you have a table of rooms)

    Select c.dt, r.room
    From Rooms r
    Inner Join Calendar c On c.dt Between GetDate() And DateAdd(day, 7, GetDate()) 

    Now you have a result with one row for every room for every day in the range you want, so just do a LEFT JOIN to get the other information you need.  You will end up with a row for every room for every date with the other information.  And if the room is not occupied for that date, it will have NULLs in the other information. So something like

    Select <data you want>
    From Rooms r
    Inner Join Calendar c On c.dt Between GetDate() And DateAdd(day, 7, GetDate()) 
    Left Join as_diary_detail dd On c.dt Between dd.diary_date And DateAdd(day, dd.duration, dd.diary_date)
    Left Join id_loc_attr la On dd.resource_ = la.place_ref;

    I know that probably not a complete solution to what you want.  Hopefully it will get you started.  If you want a complete query, please post the table definitions (in the form of CREATE TABLE statements), sample data (in the form on INSERT statements), the result you would want given that sample data and the release level of SQL Server you are running.

    Tom

    Thursday, April 04, 2013 4:17 PM

All replies

  • TRY LEFT JOIN

    where All Rooms table on LEFT and Booking table on RIGHT.

    Please POST DDL, Sample data and Required Reslt SET. to get better help.


    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you.
    Junaid Hassan.

    Thursday, April 04, 2013 10:16 AM
  • Thanks Junaid.

    The left join doesnt work (I believe) as it brings back ALL the dates in my calender report (I only want a 7 day calendar). If a room has been booked outside the 7 day date range, then the left join picks it up, which is fine, but then the calendar displays too many dates.

    With Test
    as
    (
    
    select	dd.diary_date [Start Date], 
    		dd.duration,
    		dd.resource_,
    		dd.type, 
    		isnull(dd.diary_date+dd.duration, getdate()) [End Date],
    		la.sub_attrib_code,
    		1 [Status] 
    from	as_diary_detail dd
    		left join ih_loc_attr la on dd.resource_=la.place_ref
    		union all
    select 
    		dateadd(dd, 1, [Start Date]),
    		duration,
    		resource_,
    		type,
    		[End Date],
    		sub_attrib_code,
    		1 [Status]
    from	Test
    where dateadd(dd, 1, [Start Date]) < [End Date]
    
    )
    
    
    select *
    from test 
    where t.sub_attrib_code like 'rm%'
    and [Start Date] between getdate() and getdate()+7
    order by [Start Date]

    Thanks,

    Lloyd

    Thursday, April 04, 2013 1:40 PM
  • For this sort of problem you want a calendar table.  See

    http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

    for what a calendar table is and some of its many uses.

    Once you have one, you can get a result that has a row for every room for every day between GetDate() and GetDate() + 7 just by (assuming you have a table of rooms)

    Select c.dt, r.room
    From Rooms r
    Inner Join Calendar c On c.dt Between GetDate() And DateAdd(day, 7, GetDate()) 

    Now you have a result with one row for every room for every day in the range you want, so just do a LEFT JOIN to get the other information you need.  You will end up with a row for every room for every date with the other information.  And if the room is not occupied for that date, it will have NULLs in the other information. So something like

    Select <data you want>
    From Rooms r
    Inner Join Calendar c On c.dt Between GetDate() And DateAdd(day, 7, GetDate()) 
    Left Join as_diary_detail dd On c.dt Between dd.diary_date And DateAdd(day, dd.duration, dd.diary_date)
    Left Join id_loc_attr la On dd.resource_ = la.place_ref;

    I know that probably not a complete solution to what you want.  Hopefully it will get you started.  If you want a complete query, please post the table definitions (in the form of CREATE TABLE statements), sample data (in the form on INSERT statements), the result you would want given that sample data and the release level of SQL Server you are running.

    Tom

    Thursday, April 04, 2013 4:17 PM
  • >> I have created a calender report using a recursive CTE query to show all bookings made against rooms for a care home. <<


    This is a bad way to program in SQL. Also, please stop using the old 1970's Sybase/UNIX getdate(); we have had CURRENT_TIMESTAMP for several years. 


    >> In my final SELECT list I have a date range between getdate() and getdate()+7 (to give a weekly calendar view). The problem is, any data that falls outside of this date range doesn't appear (which of course it wouldn't). <<

    Where is the DDL and sample data? 

    >> The issue is, I have to still supply the full list of rooms. So I have rooms 1-30. These need to be displayed at all times regardless if there isn't any data for a particular room for the week.<<

    Your mindset is not in RDBMS and SQL yet. This is a database language  so we use DDL and not procedural code. This DDL will maintain a full history of each toom, without any gaps in the dates. 

    CREATE TABLE Rooms
    (room_nbr INTEGER NOT NULL
       CHECK(room_nbr BETWEEN 1 AND 30),
     room_status CHAR(9) NOT NULL
       CHECK(room_status IN ('vacant, 'occupy', 'repairs', ..)),
     previous_room_end_date DATE NOT NULL
      CONSTRAINT Chained_Dates
      REFERENCES Rooms (room_end_date),
     room_start_date DATE NOT NULL,
     room_end_date DATE UNIQUE, -- null means room in use
     PRIMARY KEY (room_nbr, room_start_date),
      CONSTRAINT Occupancy_Order_Valid
     CHECK (room_start_date <= room_end_date),
     CONSTRAINT Chained_Dates 
     CHECK (DATEADD(DAY, 1, previous_room_end_date) = room_start_date)
    );

    -- disable the Chained_Dates constraint
    ALTER TABLE Rooms NOCHECK CONSTRAINT Chained_Dates

    -- insert a starter row
    INSERT INTO Rooms(room_nbr, room_status, previous_room_end_date, room_start_date, room_end_date)
    VALUES (1, 'vacant', '2010-01-01', '2010-01-02', '2010-01-05');

    -- enable the constraint in the table
    ALTER TABLE Rooms CHECK CONSTRAINT Chained_Dates

    -- this works
    INSERT INTO Rooms(room_nbr, previous_room_end_date, room_start_date, room_end_date)
    VALUES (1, 'repair', '2010-01-05', '2010-01-06', '2010-01-10');

    -- this fails
    INSERT INTO Rooms(room_nbr, previous_room_end_date, room_start_date, room_end_date)
    VALUES (1, 'occupy', '2010-01-09', '2010-01-11', '2010-01-15'); 

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

    Thursday, April 04, 2013 4:42 PM
  • There is something wrong with GETDATE() ? 

    You see it everywhere in T-SQL, I don't think i can remember seeing CURRENT_TIMESTAMP even once in the field

    Is there any chance, that one day the ANSI SQL committee gets together that we might see some of the more commonly used T-SQL functions like ISNULL, GETDATE and CONVERT become part of the ANSI standard simply through 'convention'?



    Thanks! Josh


    Thursday, April 04, 2013 11:46 PM
  • Just like to say thanks Tom - I did manage to get the calendar report to work by using the method you posted.

    Thanks to those that posted also.

    Lloyd

    Thursday, April 11, 2013 1:58 PM