Select Records Based on Data in the Same Table

Question

Post reply

Select Records Based on Data in the Same Table

david.c.holley

SSCrazy

Points: 2423
More actions
May 4, 2010 at 6:45 am

#386844

I need to identify in records in a table that are invalid when compared to other records in the same table. The table has two fields Arrival Date and Departure Date. I need to identify records whose Arrival or Departure Dates overlap those of other records.
For example, if you have the following data room #2501 should be selected as there are records that overlap each other. Room #2505 would be fine as no records overlap. An Arrival Date can fall on the same day as a Departure date and vise versa.
Room Arrival Departure
2501 4/15/10 4/25/10
2501 4/25/10 4/25/10
2501 4/24/10 4/27/10
2501 4/25/10 4/27/10
2505 4/15/10 4/25/10
2505 4/25/10 4/25/10
2505 4/25/10 4/27/10
[sql]
/****** Object: Table [dbo].[tblReservations] Script Date: 05/04/2010 08:32:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblReservations](
[lngReservationId] [int] IDENTITY(1000,1) NOT NULL,
[intRoom] [nvarchar](15) NOT NULL,
[dteArrivalDate] [datetime] NOT NULL,
[dteDepartureDate] [datetime] NULL,
CONSTRAINT [PK_tblReservations] PRIMARY KEY CLUSTERED
(
[lngReservationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
[/sql]

Viewing 3 posts - 1 through 2 (of 2 total)

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

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

declare @t_temp table

(

[lngReservationId] [int] IDENTITY(1000,1) NOT NULL PRIMARY KEY,

[intRoom] [nvarchar](15) NOT NULL,

[dteArrivalDate] [datetime] NOT NULL,

[dteDepartureDate] [datetime] NULL

)

insert into @t_temp (intRoom, dteArrivalDate, dteDepartureDate)

select 2501, '4/15/10', '4/25/10' union

select 2501, '4/25/10', '4/25/10' union

select 2501, '4/24/10', '4/27/10' union

select 2501, '4/25/10', '4/27/10' union

select 2505, '4/15/10', '4/25/10' union

select 2505, '4/25/10', '4/25/10' union

select 2505, '4/25/10', '4/27/10';

select t.intRoom,

t.lngReservationId as badRecord,

t2.lngReservationId as conflict

from @t_temp t

join @t_temp t2

on t2.lngReservationId <> t.lngReservationId

and t2.intRoom = t.intRoom

and ((t.dteArrivalDate > t2.dteArrivalDate and t.dteArrivalDate < t2.dteDepartureDate)

or (t.dteDepartureDate > t2.dteArrivalDate and t.dteDepartureDate < t2.dteDepartureDate))

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

david.c.holley SSCrazy Points: 2423 More actions · Answer 2

Thanks. Works like a charm. Now I just need to figure out how to output the results as a table that Access can use. (Thanks in advance for posting help with that, but I'd prefer to figure it out on my own for the educational benefits of it.)