Overlap dates query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Overlap dates query

Author

Topic

djavet
Starting Member

36 Posts

Posted - 2004-08-31 : 04:00:04

Hello,

I've a query for overlaps dates. It's working fine.

SELECT
  dbo.Building.ID,
  dbo.Building.VAB,
  dbo.Building.VBI
FROM
  dbo.Building
WHERE
  Building.VAB <= '01.01.2050' AND Building.VBI >= '02.04.2013'
ORDER BY
  VAB

I can resume like this:

Period of availability of building:
S= period start date (VAB)
E= period end date (VBI)

01 ---XXXXX--S---------E---------
02 ------XXXXX---------E---------
03 ---------XXXXX------E---------
04 ----------S--XXXXX--E---------
05 ----------S-----XXXXX---------
06 ----------S---------XXXXX-----
07 ----------S---------E--XXXXX--
08 ----------XXXXXXXXXXX---------
09------XXXXXXXXXXXXXXXXXXX-----

Results of availability for a certain period:
03 ---------XXXXX------E---------
04 ----------S--XXXXX--E---------
05 ----------S-----XXXXX---------
08 ----------XXXXXXXXXXX---------
09------XXXXXXXXXXXXXXXXXXX-----

Now, I need the same but without overlap dates.
I can't find the correct synthax with "BETWEEN".

Expected result with new query:
08 ----------sXXXXXXXXXE---------
09------XXXXXXXXXXXXXXXXXXX-----

Any suggestions?
Thx for your help and time.

Regards,
Dominique

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-08-31 : 05:49:57

Hi there, could you please provide some 'create table' statments+sample data in order for us to help you find a soltuion to your problem.

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

djavet
Starting Member

36 Posts

Posted - 2004-08-31 : 06:00:24

Ooopss...
My table "Building" structure (extract of my MS SQL 2002):
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[VAB] [datetime] NULL ,
[VBI] [datetime] NULL ,

My result I receive when I search overlap from 02.04.2004 to 01.01.2015:

172692	2003-08-01 00:00:00.000	9999-12-31 00:00:00.000
171150	2003-08-01 00:00:00.000	2006-04-01 00:00:00.000
171156	2003-08-01 00:00:00.000	2004-08-01 00:00:00.000
172505	2003-08-01 00:00:00.000	9999-12-31 00:00:00.000
172506	2003-08-01 00:00:00.000	9999-12-31 00:00:00.000
172399	2003-08-01 00:00:00.000	2006-04-01 00:00:00.000
172425	2003-08-01 00:00:00.000	2013-04-01 00:00:00.000
172983	2003-08-01 00:00:00.000	2007-04-01 00:00:00.000
172984	2003-08-01 00:00:00.000	2007-04-01 00:00:00.000
172985	2003-08-01 00:00:00.000	9999-12-31 00:00:00.000
172986	2003-08-01 00:00:00.000	2007-04-01 00:00:00.000
172987	2003-08-01 00:00:00.000	2007-04-01 00:00:00.000
172988	2003-08-01 00:00:00.000	2007-04-01 00:00:00.000
172989	2003-08-01 00:00:00.000	2007-04-01 00:00:00.000
171588	2003-08-01 00:00:00.000	2016-04-01 00:00:00.000

I dont wish in the new query the end date (VBI) wich is prior to 2050. Like 2006, 2007, 2016
How can I do that?

Thx a lot, Dominique

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-08-31 : 06:44:40

Hi Dominique

I don't mean to be a total idiot, but please could you provide some sample data (like 5-6 records) so that we can experiment with the queries to get an answer to your problem?

I think I know what you're after, but I would rather work on query based on your data.

All we need is a few insert settaments with some sample data.

Thanks.

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

djavet
Starting Member

36 Posts

Posted - 2004-08-31 : 07:26:38

reOooops....
I'm working to late yesterday!

Here's a 10 lines extracts:


INSERT INTO dbo.Building
  (ID,VAB,VBI)
VALUES(171055,'01.09.2003','01.04.2006');

INSERT INTO dbo.Building
  (ID,VAB,VBI)
VALUES(171056,'01.01.2004','31.12.9999');

INSERT INTO dbo.Building
  (ID,VAB,VBI)
VALUES(171057,'01.01.2004','31.12.9999');

INSERT INTO dbo.Building
  (ID,VAB,VBI)
VALUES(171058,'01.08.2004','31.12.9999');

INSERT INTO dbo.Building
  (ID,VAB,VBI)
VALUES(171059,'01.01.2004','01.04.2006');

INSERT INTO dbo.Building
  (ID,VAB,VBI)
VALUES(171060,'01.10.2003','31.12.9999');

INSERT INTO dbo.Building
  (ID,VAB,VBI)
VALUES(171061,'01.01.2004','01.04.2006');

INSERT INTO dbo.Building
  (ID,VAB,VBI)
VALUES(171062,'01.08.2004','31.12.9999');

INSERT INTO dbo.Building
  (ID,VAB,VBI)
VALUES(171063,'01.01.2004','01.04.2016');

INSERT INTO dbo.Building
  (ID,VAB,VBI)
VALUES(171064,'01.01.2004','01.01.2100');

INSERT INTO dbo.Building
  (ID,VAB,VBI)
VALUES(171065,'01.01.2004','01.04.2006');

INSERT INTO dbo.Building
  (ID,VAB,VBI)
VALUES(171066,'01.07.2004','01.04.2006');

INSERT INTO dbo.Building
  (ID,VAB,VBI)
VALUES(171067,'01.02.2004','31.12.9999');

INSERT INTO dbo.Building
  (ID,VAB,VBI)
VALUES(171068,'01.08.2004','31.12.9999');

INSERT INTO dbo.Building
  (ID,VAB,VBI)
VALUES(171069,'01.08.2004','31.12.9999');

INSERT INTO dbo.Building
  (ID,VAB,VBI)
VALUES(171070,'01.08.2004','31.12.9999');

INSERT INTO dbo.Building
  (ID,VAB,VBI)
VALUES(171071,'01.01.2004','31.12.9999');

INSERT INTO dbo.Building
  (ID,VAB,VBI)
VALUES(171072,'01.01.2004','31.12.9999');

INSERT INTO dbo.Building
  (ID,VAB,VBI)
VALUES(171073,'01.07.2004','31.12.9999');

INSERT INTO dbo.Building
  (ID,VAB,VBI)
VALUES(171074,'01.01.2004','31.12.9999');

COMMIT;

Thx, regards,
Dominique

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources