-- in following "#t" table one can enter b as 10 and e as 2 that means e can be smaller than b and b can be smaller than e
or both could be equal.
-- for that i have used following logic in where clause i want to known is there any short way to do.
DECLARE
@decBeginStation DECIMAL(13,4)=-2,
@decEndStation DECIMAL(13,4)=10
CREATE table #t ( tid int, b decimal(13,4) not null, E decimal(13,4) not null);
Insert into #T (tid,B,E)
VALUES (1, -12, -2),
(2, -10, 0),
(3, -2, 8),
(4, 10, 0),
(5, 8, 18),
(6, 10, 20),
(7, 21, 11),
(8, 0, 3),
(9, 2, 4),
(10, 3, 10),
(11, -1, 10),
(12, -1, 11),
(13, 0, 11)
;
select * from #t AS t
WHERE (
NOT (
@decBeginStation >= t.b
AND @decBeginStation >= t.E
AND @decEndStation >= t.b
AND @decEndStation >= t.E
)
AND NOT (
@decBeginStation <= t.b
AND @decBeginStation <= t.E
AND @decEndStation <= t.b
AND @decEndStation <= t.E
)
)
drop table #t
If @EndStation is always greater than @BeginStation, then you can simplify like this. Note that I've also removed the NOTs and changed ANDs to ORs and >= to < and <= to >, simply because I find it easier to read without the NOTs.
WHERE (
@decBeginStation < t.b
OR @decBeginStation < t.E
)
AND (
@decEndStation > t.b
OR @decEndStation > t.E
)
John
January 24, 2020 at 12:17 pm
If @EndStation is always greater than @BeginStation, then you can simplify like this. Note that I've also removed the NOTs and changed ANDs to ORs and >= to < and <= to >, simply because I find it easier to read without the NOTs.
WHERE (
@decBeginStation < t.b
OR @decBeginStation < t.E
)
AND (
@decEndStation > t.b
OR @decEndStation > t.E
)John
back in 2005 (yes 15 years ago) itzik ben-gan showed us a trick on overlaps - mainly to try and get rid of that horrible "OR" bit in a query
if you have a table of "things that happened" and it includes a start and date date - the criteria for the search is "anything that was occurring during @start and @end
so we have several scenarios
Itzik's logic was so simple I still struggle - -
Select * from mytable where startdate<@end and enddate>@start
not sure if this helps the Original poster, but it's a lovely little trick when dealing with overlaps (be it milage, dates, or numbers)
MVDBA
January 24, 2020 at 9:41 pm
I would redo your DDL and follow ISO 11179 naming rules. You don't need a row identifier, so we can drop one of the columns. You also need to enforce the constraints on your data with a check clause. Also, did you know that "E" is used for defining floating-point constants, so it makes a lousy data element name.
CREATE TABLE Stations
(begin_station DECIMAL(13,4) NOT NULL,
end_station DECIMAL(13,4) NOT NULL,
CHECK (begin_station < end_station),
PRIMARY KEY (begin_station, end_station));
INSERT INTO Stations
VALUES
(-12, -2),(-10, 0),(-2, 8),(10, 0),
(8, 18),(10, 20),(21, 11),(0, 3),
(2, 4),(3, 10),(-1, 10),(-1, 11),( 0, 11);
CREATE PROCEDURE Overlaps (@in_begin_station DECIMAL(13,4), @in_end_station DECIMAL(13,4))
AS
SELECT begin_station, end_station
FROM Stations
WHERE (@in_begin_station < begin_station
OR @in_begin_station < end_station)
AND (@in_end_station > begin_station
OR @in_end_station > end_station)
There is also an OVERLAPS() function in ANSI/ISO Standard SQL.
Please post DDL and follow ANSI/ISO standards when asking for help.
January 29, 2020 at 11:55 am
the situation at my end is that begin can be less than equal to or greater than equal to. and by logic works for it , only thing is, it is a little bit big, so I thought someone might have a smaller version.
at most what I can restrict is that begin will not be equal to end.
yours sincerley
January 29, 2020 at 12:10 pm
the situation at my end is that begin can be less than equal to or greater than equal to. and by logic works for it , only thing is, it is a little bit big, so I thought someone might have a smaller version.
at most what I can restrict is that begin will not be equal to end.
yours sincerley
again - I pretty much think this covers you
Select * from mytable where startdate<@end and enddate>@start
MVDBA
January 30, 2020 at 9:41 am
no it does not, if @BeginStation is greater than @EndStation.
if b =0 and e =10 and @BeginStation = 18 and @EndStation = 8
then it will no fall in first block @BeginStation is never less than 'b' nor 'e'
WHERE (
@decBeginStation < t.b
OR @decBeginStation < t.E
)
AND (
@decEndStation > t.b
OR @decEndStation > t.E
)
it will only work if @BeginStation is less than @EndStation.
January 30, 2020 at 10:02 am
no it does not, if @BeginStation is greater than @EndStation.
if b =0 and e =10 and @BeginStation = 18 and @EndStation = 8
then it will no fall in first block @BeginStation is never less than 'b' nor 'e'
WHERE (
@decBeginStation < t.b
OR @decBeginStation < t.E
)
AND (
@decEndStation > t.b
OR @decEndStation > t.E
)
it will only work if @BeginStation is less than @EndStation.
so , easy solution - check the variables and swap them over before you run the query
declare @Tmp int
if @start>@end
begin
set @Tmp=@end
set @end=@start
set start=@tmp
end
MVDBA
January 30, 2020 at 10:06 am
Indeed, or multiplying everything by -1 may also work, although I haven't actually tested it.
SELECT t.*
FROM #t AS t
CROSS APPLY (
SELECTCASE
WHEN @decBeginStation > @decEndStation THEN -1
ELSE 1
END AS n
) m
WHERE (
@decBeginStation * m.n < t.b * m.n
OR @decBeginStation * m.n < t.E * m.n
)
AND (
@decEndStation * m.n > t.b * m.n
OR @decEndStation * m.n > t.E * m.n
)
John
January 30, 2020 at 10:16 am
Based on your sample data, this returns the same result.
WITH cteData AS (
SELECT t.tid, t.b, t.E, seq.st, seq.nd
FROM #t AS t
CROSS APPLY ( SELECT st = MIN( ord.v )
, nd = MAX( ord.v )
FROM ( VALUES ( t.b ), ( t.E ) ) AS ord ( v )
) AS seq -- This swaps the b/e to ensure that b is alway <= e
)
SELECT cte.tid, cte.b, cte.E
FROM cteData AS cte
WHERE @decBeginStation < cte.nd
AND @decEndStation > cte.st
ORDER BY cte.tid;
The trick is to re-order the b/e to ensure that b is always <= e.
However, you will need to check this against your data to see whether there is a performance gain or loss.
January 30, 2020 at 11:34 am
agreed,
but situation is such. we want to take this challenge , and I have posted that solution at the top, it works in all conditions, the only thing I wanted was if it could be optimized.
January 30, 2020 at 11:52 am
Only you can know that. You have your solution and several others provided by contributors to this thread. Have you compared the execution plans or done some load testing to see whether there's any improvement?
John
January 30, 2020 at 12:53 pm
ya, I have checked, I will update with an example why they do not fit into.
primarily they do not give result in one go,
first they find the small and big then they do the overlap check.
I want it to be done in one go because that could be faster than two passes
and my solution, gives result in one go, but it has more predicates(clauses). which I wanted to reduce if possible.
January 31, 2020 at 5:07 am
both are working, the only thing is first they multiply by using cross apply, that takes one pass, then where clause is applied to fillter, that makes the second pass.
in my case, if u see it does in one pass only. if there is any way to do it in one pass with fewer predicates then pls share.
January 31, 2020 at 8:59 am
What do you mean by "pass"? All the solutions posted so far read the table only once. Why is it so important to reduce the number of lines of code - is this a homework question?
John
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply