Date range stored proc - code posted

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)

Date range stored proc - code posted

Author

Topic

abarsami
Yak Posting Veteran

68 Posts

Posted - 2002-07-31 : 14:04:06

Here is a sample of the data:

from_date to_date

2002-01-10 15:15:18.000 2002-01-10 15:15:20.000
2002-01-10 15:15:20.000 2002-01-10 15:15:23.000
2002-01-10 15:15:22.000 2002-01-10 15:20:30.000
2002-01-10 15:25:15.000 2002-01-10 15:30:30.000
2002-01-10 16:25:15.000 2002-01-10 17:30:30.000

Need a query that finds the following things:

- for each of these records...
Find any records where the from_date of each record is greater than the from_date and less than to_date for all records.

Find any records where the to_date of each record is greater than the from_date and less than to_date for all records.

Without using a cursor.

So this query would return:
only the 2nd row because '2002-01-10 15:15:22.000' is in between
'2002-01-10 15:15:20.000' and '2002-01-10 15:15:23.000'

My stored Proc:

CREATE TABLE #Track (
client_id int null,
track_page_loc varchar(100) null,
track_enter_d datetime null,
track_exit_d datetime null,
track_ip_address varchar(40) null,
track_yn bit null
)

insert into #Track
select client_id,track_page_loc,track_enter_d,track_exit_d,track_ip_address,NULL from track_users

update tu set tu.track_yn=1
from #Track tu inner join track_users t on
(tu.client_id=t.client_id and tu.track_page_loc=t.track_page_loc and tu.track_enter_d=t.track_enter_d
and tu.track_exit_d=t.track_exit_d and tu.track_ip_address=t.track_ip_address)
where tu.track_enter_d > t.track_enter_d and tu.track_enter_d < t.track_exit_d

select * from #Track where track_yn is not null

drop table #Track

--It always returns nothing

Thanks

Edited by - abarsami on 07/31/2002 18:46:38

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-08-01 : 00:19:25

quote:

Need a query that finds the following things:

- for each of these records...
Find any records where the from_date of each record is greater than the from_date and less than to_date for all records.

Find any records where the to_date of each record is greater than the from_date and less than to_date for all records.

Is that two things you're trying to find or just one? what's the application? What's the question you're trying to answer?

is this what you want?
------------------------------------------------
--create table
create table FromTo (fromdate datetime, todate datetime)

--insert values into table
insert into FromTo (fromdate, todate)
select convert(datetime, '2002-01-10 15:15:18.000'), convert(datetime, '2002-01-10 15:15:20.000')
insert into FromTo (fromdate, todate)
select convert(datetime, '2002-01-10 15:15:20.000'), convert(datetime, '2002-01-10 15:15:23.000')
insert into FromTo (fromdate, todate)
select convert(datetime, '2002-01-10 15:15:22.000'), convert(datetime, '2002-01-10 15:20:30.000')
insert into FromTo (fromdate, todate)
select convert(datetime, '2002-01-10 15:25:15.000'), convert(datetime, '2002-01-10 15:30:30.000')
insert into FromTo (fromdate, todate)
select convert(datetime, '2002-01-10 16:25:15.000'), convert(datetime, '2002-01-10 17:30:30.000')

--Find any records where the from_date of each record is greater than the from_date and less than to_date for all records.
select a.fromdate, a.todate
from FROMTO a cross join FROMTO b
where (a.fromdate <> b.fromdate and a.todate <> b.todate)
and (a.fromdate > b.fromdate and a.fromdate <b.todate)

--Find any records where the to_date of each record is greater than the from_date and less than to_date for all records.
select a.fromdate, a.todate
from FROMTO a cross join FROMTO b
where (a.fromdate <> b.fromdate and a.todate <> b.todate)
and (a.todate < b.todate and a.todate > b.fromdate)
-----------------------------------------------------

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

abarsami
Yak Posting Veteran

68 Posts

Posted - 2002-08-01 : 17:05:47

it works, but it takes too long... can't I use something else besides a cross join.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-08-04 : 20:37:20

perhaps if you explain what you're actually trying to do, I might be able to see another approach...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources