Datetime spell/period - 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

General SQL Server Forums

New to SQL Server Programming

Datetime spell/period

Author

Topic

Clas
Starting Member

33 Posts

Posted - 2008-09-24 : 06:48:10

Hi !

How to select datetimespell which have at least one day in "measuringperiod"
There are about 250.000 spell in database.

CREATE TABLE #spell
(
spellid INT,
startspell DATE,
stopspell DATE
)
insert into #spell
values (1, '1/2/1824', '6/4/1851')
insert into #spell
values (2, '7/5/1889', '3/2/1899')
insert into #spell
values (3, '12/3/1852', '9/10/1863')
insert into #spell
values (4, '1/2/1890', '8/8/1897')

SELECT * FROM #spell

DECLARE @measuringstart DATE
DECLARE @measuringstop DATE
SET @measuringstart = '1/1/1850'
SET @measuringstop = '1/1/1890'

SELECT ......?

Spell 1 is part of the "measuringperiod" (at least one day)
Spell 2 is part of the "measuringperiod" (at least one day)
Spell 3 is part of the "measuringperiod" (at least one day)
Spell 4 is NOT part of the "measuringperiod" (at least one day)

Result:
Spell 1
Spell 2
Spell 3

DROP TABLE #spell

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-24 : 07:15:10

[code]DECLARE @Spell TABLE
(
spellID INT,
spellStart DATE,
spellStop DATE
)

SET DATEFORMAT MDY

INSERT @Spell
VALUES (1, '1/2/1824', '6/4/1851'),
(2, '7/5/1889', '3/2/1899'),
(3, '12/3/1852', '9/10/1863'),
(4, '1/2/1890', '8/8/1897')

SELECT *
FROM @Spell

DECLARE @measuringStart DATE,
@measuringStop DATE

SELECT @measuringStart = '1/1/1850',
@measuringStop = '1/1/1890'

SELECT *
FROM @Spell
WHERE spellStart <= @measuringStop
AND spellStop >= @measuringStart[/code]

E 12°55'05.63"
N 56°04'39.26"

Clas
Starting Member

33 Posts

Posted - 2008-09-24 : 07:34:18

Thanks !
I were only focus to compare:
spellStart - measuringStart
spellStop - measuringStop

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-24 : 08:16:44

[code]INSERT @Spell
VALUES (1, '1/2/1824', '6/4/1851'),
(2, '7/5/1889', '3/2/1899'),
(3, '12/3/1852', '9/10/1863'),
(4, '1/2/1890', '8/8/1897')
[/code]
SQL Server 2008 syntax?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 08:27:56

quote:
Originally posted by madhivanan

INSERT @Spell
VALUES (1, '1/2/1824', '6/4/1851'),
(2, '7/5/1889', '3/2/1899'),
(3, '12/3/1852', '9/10/1863'),
(4, '1/2/1890', '8/8/1897')

SQL Server 2008 syntax?

Madhivanan

Failing to plan is Planning to fail

yup it is

in sql 2008 you can insert several sets of values through a single INSERT like this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources