Overlapping query help

  • I have a query I am trying to write and this is my first attempt at something like this.

    I need to check the range of dates. For example:

    If I have the following

    CheckIn date (datetime - only date is needed)

    CheckOut date (datetime - only date is needed)

    runno (varchar)

    runtype

    CheckedIn (Y/N)

    I would like the follwing results for the data entered. Basically if they overlap at all I need to return a 1 or if there is a better way I am open to that.

    CheckIn = 1/5/2008

    CheckOut = 1/8/2008

    Check In Date Entered Check Out Date Entered Result (Expr1)

    1/5/2008 1/8/2008 1

    1/4/2008 1/8/2008 1

    1/8/2008 (any date greater than 1/8/2008) 0

    1/1/2008 1/5/2008 1

    any date that falls before or after the CheckIn and CheckOut dates would produce a 0 as well

    Here is what I have so far (doesnt work as I need it)

    IF (SELECT Count(Reservation)

    FROM Reservation

    WHERE RunNo = @RunNo AND

    RunType = @RunType AND

    PetCheckedIn = 'Y' AND

    Reservation.Deleted != 'Y' AND

    RunType = @RunType AND

    (CheckInDate 0

    RETURN 1

    ELSE

    RETURN 0

  • Ooops.. the query didnt paste correctly

    IF (SELECT Count(Reservation)

    FROM Reservation

    WHERE RunNo = @RunNo AND

    RunType = @RunType AND

    PetCheckedIn = 'Y' AND

    Reservation.Deleted != 'Y' AND

    RunType = @RunType AND

    (CheckInDate 0

    RETURN 1

    ELSE

    RETURN 0

  • Read the article: http://www.sqlservercentral.com/articles/Best+Practices/61537/ for information on how to post data/code that will get you better assistance in answering your query

    And it looks like your query still didn't paste properly (can't find an expression or closing brackets for the "(CheckInDate 0" part of the query...

  • Funny both times it looked like it posted correctly. Never had this problem on other forums. Trying again.

    IF (SELECT Count(Reservation)

    FROM KRIS.dbo.Reservation

    WHERE RunNo = @RunNo AND

    RunType = @RunType AND

    PetCheckedIn = 'Y' AND

    Reservation.Deleted != 'Y' AND

    RunType = @RunType AND

    (CheckInDate <= @CheckInDate AND

    CheckOutDate >= @CheckOutDate) ) > 0

    RETURN 1

    ELSE

    RETURN 0

  • Hi,

    please read the info about posting data (link in previous reply) and post structure of table Reservations along with some test data and required result from the supplied test data. Blind guess is the only thing we could do now, and that wouldn't help you much.

  • ReservationIDint(identity - key)

    CheckInDatedatetime

    CheckInTimedatetime(not needed in this query)

    CheckOutDatedatetime

    CheckOutTimedatetime(not needed in this query)

    Reservationchar(1)(Y if they have a reservation, N if not)

    RunType varchar(50)

    RunNo varchar(50)

    PetCheckedInchar(1)(Y if they are boarding, N if not)

    Deleted char(1)(Y if reservation has been removed/deleted, N if not)

    It will be a while before I can post data, I have deleted the data from the db for testing other parts of the application. I have supplied the table definition.

    RunType and RunNo (can't have a duplicate)

    Thank you for all your help,

    Mark

    (sorry for not posting more info earlier - I will add data just as soon as I can)

  • I know it's disgusting how we repeat the same thing over and over, but please, could you read this : How to post data ?

    It saves a lot of time if the table definition is supplied as CREATE TABLE statement and data as INSERT INTO statement... and all the other things that are mentioned in that article are helpful as well. We are mostly people who have a job and read forums if we have a few minutes of free time. If we have to spend this time writing code to create testing environment, when will think about solutions? Thanks for understanding!

  • CREATE TABLE [dbo].[Reservation](

    [ReservationID] [int] IDENTITY(1,1) NOT NULL,

    [ClientID] [int] NOT NULL,

    [PetID] [int] NOT NULL,

    [CheckInDate] [datetime] NULL,

    [CheckInTime] [datetime] NULL,

    [CheckOutDate] [datetime] NULL,

    [CheckOutTime] [datetime] NULL,

    [Reservation] [char](1) NULL CONSTRAINT [DF_Reservation_Reservation] DEFAULT ('N'),

    [RunType] [varchar](50) NULL,

    [RunNo] [varchar](50) NULL,

    [Deposit] [money] NULL CONSTRAINT [DF_Reservation_Deposit] DEFAULT ((0.0)),

    [BoardRate] [smallmoney] NULL CONSTRAINT [DF_Reservation_BoardRate] DEFAULT ((0.00)),

    [PetCheckedIn] [char](1) NULL CONSTRAINT [DF_Reservation_PetCheckedIn] DEFAULT ('N'),

    [Deleted] [char](1) NULL CONSTRAINT [DF_Reservation_Deleted] DEFAULT ('N'),

    CONSTRAINT [PK_Reservation] PRIMARY KEY CLUSTERED

    (

    [ReservationID] ASC

    CREATE PROCEDURE [dbo].[AddReservation]

    (

    @ClientIDint,

    @PetIDint,

    @CheckInDatedatetime,

    @CheckInTimedatetime,

    @CheckOutDatedatetime,

    @CheckOutTimedatetime,

    @RunTypevarchar(50),

    @RunNovarchar(50),

    @Depositmoney,

    @Reservationchar(1),

    @ModifiedDatedatetime,

    @ModifiedByvarchar(50)

    )

    AS

    /* See if we have that reservation in the db */

    IF (SELECT COUNT(*) FROM KRIS.dbo.Reservation

    WHERE

    ClientID = @ClientID AND

    PetID = @PetID AND

    Reservation = @Reservation AND

    CheckInDate = @CheckInDate AND

    CheckOutDate = @CheckOutDate AND Deleted = 'N') > 0

    RETURN(-1)

    /* return if err <> 0 */

    if @@ERROR <> 0

    BEGIN

    RETURN @@ERROR

    END

    /* Insert a new reservation */

    INSERT KRIS.dbo.Reservation(

    ClientID,

    PetID,

    CheckInDate,

    CheckInTime,

    CheckOutDate,

    CheckOutTime,

    Reservation,

    RunType,

    RunNo,

    Deposit

    )VALUES(

    @ClientID,

    @PetID,

    @CheckInDate,

    @CheckInTime,

    @CheckOutDate,

    @CheckOutTime,

    @Reservation,

    @RunType,

    @RunNo,

    @Deposit

    )

    /* return if err <> 0 */

    IF @@ERROR <> 0

    BEGIN

    RETURN @@ERROR

    END

    SELECT @@IDENTITY as 'ReservationID';

    /* SET NOCOUNT ON */

    IF @@ERROR = 0

    BEGIN

    RETURN @@IDENTITY

    END

    ELSE

    BEGIN

    RETURN @@ERROR

    END

    I couldnt add the data here without it wrapping so its in the attachment. Let me know if you need anything else. I am in process of adding more data but had to work on another front end problem first.

    I am some what of a newbie in the DB world. So if you have other tips besides what I am asking, feel free to comment.

    Thanks again

  • Getting closer. The data you provided has 2 problems.

    1. There is only 1 line. Whenever providing sample data, you should provide a minimum of 3-5 lines, and a maximum of however much data it takes to adequately cover your issue / ensure the solution works. In this instance, you'd want a couple rowsets that did meet your criteria, and a couple that did not.

    2. The data is not in any usable format. The data should be in the form of an insert statement so all we have to do to populate the table is run the statement. This is gone over in that article in detail.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • OK gotcha. (I'm learning slowly)

    ok assumption that the user wants the:

    CheckInDate = 11/11/2008

    CheckOutDate = 11/14/2008

    RunType = Large

    RunNo = L100

    The order I have the data file:

    Insert Allowed

    ---------------

    1 N

    2 Y

    3 Y

    4 Y

    5 N

    6 N

  • The data should actually match the table definition... instructions in the atricle everyone has posted would really make this easy.

    Msg 515, Level 16, State 2, Line 3

    Cannot insert the value NULL into column 'ClientID', table 'tempdb.dbo.Reservation'; column does not allow nulls. INSERT fails.

    The statement has been terminated.

    Msg 515, Level 16, State 2, Line 6

    Cannot insert the value NULL into column 'ClientID', table 'tempdb.dbo.Reservation'; column does not allow nulls. INSERT fails.

    The statement has been terminated.

    Msg 515, Level 16, State 2, Line 9

    Cannot insert the value NULL into column 'ClientID', table 'tempdb.dbo.Reservation'; column does not allow nulls. INSERT fails.

    The statement has been terminated.

    Msg 515, Level 16, State 2, Line 12

    Cannot insert the value NULL into column 'ClientID', table 'tempdb.dbo.Reservation'; column does not allow nulls. INSERT fails.

    The statement has been terminated.

    Msg 515, Level 16, State 2, Line 15

    Cannot insert the value NULL into column 'ClientID', table 'tempdb.dbo.Reservation'; column does not allow nulls. INSERT fails.

    The statement has been terminated.

    Msg 515, Level 16, State 2, Line 18

    Cannot insert the value NULL into column 'ClientID', table 'tempdb.dbo.Reservation'; column does not allow nulls. INSERT fails.

    The statement has been terminated.

    The reason why we want some reasonable volume of data is so we can give you a tested answer.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • In this case ClientID and the times are not needed. I modified everything. Should be able to still use the previous data file. Thanks for your patience.

    CREATE TABLE [dbo].[Reservation](

    [ReservationID] [int] IDENTITY(1,1) NOT NULL,

    [CheckInDate] [datetime] NULL,

    [CheckOutDate] [datetime] NULL,

    [Reservation] [char](1) NULL CONSTRAINT [DF_Reservation_Reservation] DEFAULT ('N'),

    [RunType] [varchar](50) NULL,

    [RunNo] [varchar](50) NULL,

    [PetCheckedIn] [char](1) NULL CONSTRAINT [DF_Reservation_PetCheckedIn] DEFAULT ('N'),

    [Deleted] [char](1) NULL CONSTRAINT [DF_Reservation_Deleted] DEFAULT ('N'),

    CONSTRAINT [PK_Reservation] PRIMARY KEY CLUSTERED

    (

    [ReservationID] ASC

    INSERT KRIS.dbo.Reservation(

    CheckInDate ,

    CheckOutDate ,

    Reservation ,

    RunType ,

    RunNo ,

    Deleted

    )VALUES(

    @CheckInDate ,

    @CheckInTime ,

    @CheckOutDate ,

    @CheckOutTime ,

    'B',

    @RunType ,

    @RunNo,

    'N'

    )

  • Just an observation, but you really should evaluate if the Primary Key (Reservation ID) is actually the best choice for your clustered index on the table. You should review the types of queries run againast the table and see if there is a better candadate for the clustered index. For instance, if you find that most of your queries do date ranges on the same columns, this may be a better choice than the Primary Key.

  • Could explain in a little more detail exactly what you are trying to accomplish? Also, could you explain in more detail what is supposed to happen with the following from one of your earlier posts?

    ok assumption that the user wants the:

    CheckInDate = 11/11/2008

    CheckOutDate = 11/14/2008

    RunType = Large

    RunNo = L100

    The order I have the data file:

    Insert Allowed

    ---------------

    1 N

    2 Y

    3 Y

    4 Y

    5 N

    6 N

    I am just a little lost at what needs to occur and this extra info will help me get a handle on the problem.

  • Lynn,

    First of all thank you for your suggestion. As a newbie I can use all the help I can get.

    What I am trying to accomplish if a reservation is attempted with any overlapping dates that is not allowed.

    If a reservation is made where the checkindate is the same as anothers check out date, then that should be allowed. If it overlaps by anything other than that, it should not be allowed.

    Same concept if you were trying to book a hotel room but the only difference is in this case there is a RunType and RunNo where in a hotel situation there is only a room number.

    Hope this helps.

    Thank you for your assistence

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply