Overlapping query help

Question

Post reply

Overlapping query help

ski_freak

SSC Enthusiast

Points: 100
More actions
November 17, 2008 at 2:46 pm

#215341

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

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

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

ski_freak SSC Enthusiast Points: 100 More actions · Answer 1

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

winash SSChampion Points: 11446 More actions · Answer 2

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

ski_freak SSC Enthusiast Points: 100 More actions · Answer 3

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

Vladan SSC-Insane Points: 22072 More actions · Answer 4

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.

ski_freak SSC Enthusiast Points: 100 More actions · Answer 5

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)

Vladan SSC-Insane Points: 22072 More actions · Answer 6

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!

ski_freak SSC Enthusiast Points: 100 More actions · Answer 7

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

Garadin One Orange Chip Points: 29613 More actions · Answer 8

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]

ski_freak SSC Enthusiast Points: 100 More actions · Answer 9

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 10

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