how to return a set from a range

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)

how to return a set from a range

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2002-04-23 : 22:27:16

if I have
startdate='20020423'
enddate='20020428'

how could I figure out which records cross or are within
start & enddate ie

tbl
sdate edate
'20020423', '20020423'
'20020421', '20020429'
'20020424', '20020425'
'20020428', '20020429'

Thanks in advance

slow down to move faster...

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-04-23 : 23:04:14

can u give ur table stru
so any one give a better suggestion

======================================
Ask to your self before u ask someone

skillile
Posting Yak Master

208 Posts

Posted - 2002-04-23 : 23:15:03

This is basically what I am trying to do. What dates cross
or are within the stime & etime.

create table #times (stime smalldatetime, etime smalldatetime)

insert #times values ('20020423', '20020423')
insert #times values ('20020421', '20020429' )
insert #times values ('20020424', '20020425' )
insert #times values ('20020428', '20020429' )

declare @start smalldatetime
declare @end smalldatetime

set @start='2002423'
set @end='2002439'

--select all the date ranges in #times that touch or
are inside of @start & @end

slow down to move faster...

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-04-23 : 23:32:48

quote:

declare @start smalldatetime
declare @end smalldatetime

set @start='2002423'
set @end='2002439'

i think this should not work
try this

declare @start smalldatetime
declare @end smalldatetime

set @start='20020423'
set @end='20020430'

select * from #times where convert(varchar,stime,103) between convert(varchar,@start,103) and convert(varchar,@end,103)

or

select * from #times where convert(varchar,etime,103) between convert(varchar,@start,103) and convert(varchar,@end,103)

======================================
Ask to your self before u ask someone

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-04-24 : 02:27:58

If you're building a billing engine, you should probably consider 3 seprate cases:
1. activity range is totally within billing range - charge as per activity range
2. activity range crosses billing range - charge portion of activity range
3. billing range is totally within activity range - charge as per billing range.

It's easy to accidentally forget the 3rd option, as neither end of the activity range is "between" the billing range....

Cheers

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

skillile
Posting Yak Master

208 Posts

Posted - 2002-04-24 : 08:13:33

I am trying to build a schedule conflict funtion that takes
the requested start and stop date/time and compares that range
to all of the previously scheduled events to find a conflict.

Has anyone ever done this or a similar routine?

slow down to move faster...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources