February 12, 2016 at 1:05 am
hi,
we are entering range like
pointfrom pointto
1 5
5 6
7 9
10 11
11 12
to find overlap i use
(@pointfrom >= A.ChainageFrom AND @pointfrom <= A.ChainageTo) OR
(@pointto >= A.ChainageFrom AND @pointto <= A.ChainageTo) OR
(A.ChainageFrom >= @pointfrom AND A.ChainageFrom < =@pointto) OR
(A.ChainageTo >= @pointfrom AND A.ChainageTo <= @pointto)
but this time requirement is pointfrom can > or = to previous "range's pointto" or pointto can be < or = to next range's pointfrom
(@pointfrom >= A.ChainageFrom AND @pointfrom < A.ChainageTo) OR
(@pointto > A.ChainageFrom AND @pointto <= A.ChainageTo) OR
(A.ChainageFrom >= @pointfrom AND A.ChainageFrom < @pointto) OR
(A.ChainageTo > @pointfrom AND A.ChainageTo <= @pointto)
yours sincerley
February 12, 2016 at 1:22 am
Is there a question in here, or are you just sharing what you did?
If there is a question, then please post:
* CREATE TABLE statements for the table
* INSERT statements with a few rows of sample data
* Expected results, with an explanation
* The work you have done so far, its results, and what you are strill struggling with
February 12, 2016 at 1:32 am
is the second one correct or not.
i have to find out where it is overlapping in second case.
Pointfrom and pointto is passed in query as param.
where point from is always < point to
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)
select * from
(@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 scincerley
February 12, 2016 at 2:06 am
rajemessage 14195 (2/12/2016)
is the second one correct or not.i have to find out where it is overlapping in second case.
Your description is unclear. Are you able to introduce clarity by providing the details requested by Hugo?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 12, 2016 at 2:10 am
--requirement is pointfrom can > or = to previous "range's pointto" or pointto can be < or = to next range's pointfrom
--@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)
February 12, 2016 at 3:07 am
So what exactly is the expected output from the sample data you posted? And what exactly is the problem with the query you posted?
February 12, 2016 at 3:42 am
where ever @pointfrom and @pointTo is overlaping with records ,considering the given critaria should come.
February 12, 2016 at 6:07 am
rajemessage 14195 (2/12/2016)
where ever @pointfrom and @pointTo is overlaping with records ,considering the given critaria should come.
Provide the output you expect, based on the data you provided.
Not an English explanation of the output, but the actual data.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 12, 2016 at 6:14 am
--
-- Create a table with content. (Containing periods of hours, days, months and 24 hour periods)
--
DECLARE @test_a table(a_start datetime
, a_stop datetime
, aname varchar(300))
insert into @test_a VALUES('20160202','20160203','One day')
,('20160203','20160204','Next day')
,('20160201','20160202','previous day')
,('20160201 23:59:59.999','20160202 23:59:59.999','which day is this')
,('20160203 15:00','20160204 15:00','24 hours')
,('20160201','20160301','One month')
,('20160203 14:00','20160203 15:00','One Hour')
,('20160203 15:00','20160203 16:00','Next Hour')
--
-- Create a second table. (With the same content
--
DECLARE @test_b table(b_start datetime, b_stop datetime, bname varchar(300))
insert into @test_b select * from @test_a
-- Code similar to rajemessage 14195
select * from @test_a cross join @test_b where
(
(b_start >= a_start AND b_start <= a_stop) OR
(b_stop >= a_start AND b_stop <= a_stop) OR
(a_start >= b_start AND a_start < =b_stop) OR
(a_stop >= b_start AND a_stop <= b_stop)
) and aname <> bname
-- Code similar to rajemessage 14195
select * from @test_a cross join @test_b where
(
(b_start >= a_start AND b_start < a_stop) OR
(b_stop > a_start AND b_stop <= a_stop) OR
(a_start >= b_start AND a_start < b_stop) OR
(a_stop > b_start AND a_stop <= b_stop)
) and aname <> bname
---------------------------------------------------------------------------------
-- Use one of the following constructions : --
---------------------------------------------------------------------------------
-- Non overlapping:
select * from @test_a cross join @test_b where
(a_stop <= b_start or b_stop <= a_start) and aname <> bname
-- Overlapping
select * from @test_a cross join @test_b where
NOT (a_stop <= b_start or b_stop <= a_start) and aname <> bname
------------------------------------------------------------------------------------------------
-- Overlapping simplyfied: (Preferred syntax).
select * from @test_a cross join @test_b where
(a_stop > b_start AND b_stop > a_start) and aname <> bname
------------------------------------------------------------------------------------------------
--
-- Overlapping simplyfied:
-- Overlap is true when stop and start are the same.
--
select * from @test_a cross join @test_b where
(a_stop >= b_start AND b_stop >= a_start) and aname <> bname
The above code shows how to test for overlapping or non overlapping.
You can define that there should be a gap between two datetimes.
A datetime field should be considered a point in time. It can be a starting point of for example a day a month or a 24 hour period, but this should be defined exactly beforehand. Otherwise it should be considered a point in time.
For overlap you can define that there should be a gap between two periods. Which is the last example.
If the stop time is dat date and this is defined as an inclusive date. 1 should be added to the date to get the end of the date. I myself consider this a bad usage of a datetime field. Because then the period ends 24 hours after the point in time given by the stop date.
--
-- If the stop date is not a time but a date including the date self.
-- A day should be added to the stop date.
-- This works for dates, but also for times where 24 hours is added.
--
-- Overlapping (stop day inclusive):
--
select * from @test_a cross join @test_b where
(dateadd(day,1,a_stop) > b_start AND dateadd(day,1,b_stop) > a_start) and aname <> bname
This code is less generic, because suppose the time is given in hours and not in days, in that case an hour should be added. Suppose the time is given in minutes and not in hours, in that case a minute should be added. Same for seconds. But also the same for years, months, weeks etc.
It is far better to define the endtime (stop) as the point in time where the period stops.
(Edit) Your query (for overlap) would become:
(A.ChainageTo > @pointto AND @pointfrom > A.ChainageFrom)
(Edit) Your query (for NON overlap) would become:
(A.ChainageTo <= @pointto OR @pointfrom <= A.ChainageFrom)
Please inform us if this helps.
Ben
< should be read as < (The site is changing this ???)
> should be read as >
*)
The table_b can be replaced by again the table_a or with parameters or constants.
February 12, 2016 at 6:18 am
i want all records from #t
which are overlaping with entered @pointfrom = 2 and @pointTo = 10
--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
February 12, 2016 at 6:36 am
For your last example:
select * from #t as a where
A.t > @pointfrom AND @pointto > A.f
Ben
February 12, 2016 at 9:27 am
rajemessage 14195 (2/12/2016)
i want all records from #twhich are overlaping with entered @pointfrom = 2 and @pointTo = 10
Since you are unwilling to provide the very simple answer we asked you, multiple times, I am done in this topic.
Good luck with your issue. I really hope that you do find someone who is able and willing to help you based on your vague and incomplete explanations. And I truly hope that the answer they provide will actually work and not backfire.
February 12, 2016 at 10:22 am
Okay, since you insist on us taking a shot in the dark, here is mine:
'
-- Using your sample data posted above
declare @pointfrom int = 2,
@pointto int = 10;
select * from #t;
select a.*
from #t a
where
a.t >= @pointfrom and
a.f <= @pointto;
Output from the first query:
ft
15
56
79
1011
1112
Output from the second query: -- is this what you are looking for?
ft
15
56
79
1011
February 12, 2016 at 7:14 pm
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.
February 15, 2016 at 6:00 am
Jeff Moden (2/12/2016)
[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:
Yeah, that's what you get when you want to help people, but also want to use your time efficiently.
With your valuable help, this is bound to happen once in a while.
So this happened because of your helpfullness.:-D:-D:-D
So we had some overlap in our anwsers, just being true to the question.
ben
(In a previous anwser I explained why the exact matches are excluded. It is my strong preverence, I did include some code there how I normally tread this sort of things.)
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply