rajemessage 14195 (2/12/2016)
[font="Arial Black"]i want all records from #twhich are overlaping with entered @pointfrom = 2 and @pointTo = 10[/font]
--requirement is "f" can > or = to previous "range's "t" or "t" can be < or = to next range's "f"
--"f" is always less than "t"
--@Pointfrom and @pointto is passed in query as param.
--where @pointfrom is always < @pointto
create table #t (f int, t int)
insert into #t values (1,5)
insert into #t values (5,6)
insert into #t values (7,9)
insert into #t values (10,11)
insert into #t values (11,12)
declare @pointfrom int, @pointto int
select @pointfrom=1 , @pointto=2
select * from #t as a where
(@pointfrom >= A.f AND @pointfrom < A.t) OR
(@pointto > A.f AND @pointto <= A.t) OR
(A.f>= @pointfrom AND A.f< @pointto) OR
(A.t> @pointfrom AND A.t <= @pointto)
yours sincerley
Using the #t test table from above, the answer is remarkably simple... like this.
--===== Declare variables as you did before
DECLARE @PointFrom INT
,@PointTo INT
;
SELECT @PointFrom = 2
,@PointTo = 10
;
--===== The solution is surprisingly simple
SELECT *
FROM #t
WHERE t >= @PointFrom
AND f <= @PointTo
;
For more information on how and why the code works, please see the following article.
[font="Arial Black"]http://www.sqlservercentral.com/articles/T-SQL/105968/[/font][/url]
[font="Arial Black"]EDIT: Just noticed that Lynn posted almost exactly the same code.[/font]
Well done on your "shot in the dark", Lynn. 🙂
[font="Arial Black"]EDIT EDIT: Also just noticed that Ben was also right there (excluding exact matches).[/font] That's what I get for not reading further down. :blush:
--Jeff Moden
Change is inevitable... Change for the better is not.