I use sql 2012 but, one can answer up to 2019
create table t ( b decimal(13,4) not null, c decimal(13,4) not null);
Insert into T (B,C) values ( 1,200);
Insert into T (B,C) values ( 200 ,400);
Insert into T (B,C) values ( 600, 900);
create table t1 (b decimal(13,4) not null, c decimal(13,4) not null);
Insert into t1 (B,C) values ( 1,100);
Insert into t1 (B,C) values ( 200 ,500);
Insert into t1 (B,C) values ( 800, 1000);
-- "t" and "t1" table holds ranges which could be any thing where b<c, i have to find out where "t" has range but "t1" does not?
--ex "t" has 1,200 , "t1" 1,100 means i want to see 100 to 200 t2 does not have any thing.
Q) i have to find out the range where "t" has got range but "t1" does not? that means start range and end range will be passed
and i have to find out the range where "t1" is not done but "t" is done
-- case 1 table "t" and "t1" will have no overlap with in the table
-- case 2 they will have overlap with in the table.
yours sincerely
January 11, 2020 at 11:46 am
I can't say that I understand your question. What do you mean by: "--ex "t" has 1,200 , "t1" 1,100 means i want to see 100 to 99 t2 does not have any thing."
why would you want to see 100 to 99?
Can you explain your question more clearly?
January 12, 2020 at 7:08 am
sorry it was 100 to 200
create table t ( b decimal(13,4) not null, c decimal(13,4) not null);
Insert into T (B,C) values ( 1,200);
Insert into T (B,C) values ( 200 ,400);
Insert into T (B,C) values ( 600, 900);
create table t1 (b decimal(13,4) not null, c decimal(13,4) not null);
Insert into t1 (B,C) values ( 1,100);
Insert into t1 (B,C) values ( 200 ,500);
Insert into t1 (B,C) values ( 800, 1000);
result ( pls find where t has range and t1 does not)
100 200
600 800
January 12, 2020 at 2:52 pm
How does table t join to table t1? Are the rows intended to be in sequential 1-to-1 correspondence? If yes, why not create only 1 table instead of 2?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 13, 2020 at 5:22 am
1) two tables store range data related to two different activities, but for simplicity, I have removed other cols.
2) any range could be entered as long as decimal(13,4) supports.
3) I have to find out the range where "t1" activity is not done but "t" activity is done?
January 13, 2020 at 2:11 pm
1) two tables store range data related to two different activities, but for simplicity, I have removed other cols.
Currently there's no way to join rows from table t to rows in table t1. Are any of the columns you've removed present in both of the two tables?
2) any range could be entered as long as decimal(13,4) supports.
In your example "t" has 1,200 , "t1" 1,100 [and the expected answer is] "100 to 200". Doesn't the value 100 overlap? If any range could be entered then wouldn't you have to consider possible decimal values? Why isn't the expected range 100.0001 to 200?
3) I have to find out the range where "t1" activity is not done but "t" activity is done?
What if the "t" range completely encompasses the "t1" range? Doesn't it result in 2 non-contiguous ranges? Suppose "t" is 100,400 and "t1" is 200,300 what is/are the expected range(s)? Would it be both 100 to 199.9999 and 300.0001 to 400?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
I believe that this solves your problem. It's a variation on interval packing as discussed by Itzik Ben-Gan.
create table #t ( b decimal(13,4) not null, c decimal(13,4) not null);
Insert into #T (B,C)
values
(1, 200)
, (200, 400)
, (600, 900);
create table #t1 (b decimal(13,4) not null, c decimal(13,4) not null);
Insert into #t1 (B,C)
values
(1, 100)
, (200, 500)
, (800, 1000);
/****
This method uses half-closed intervals.
****/
WITH Prices AS
(
SELECT p.price
FROM #t AS t
CROSS APPLY (VALUES(t.b), (t.c)) p(price)
UNION
SELECT p.price
FROM #t1 AS t
CROSS APPLY (VALUES(t.b), (t.c)) p(price)
)
, intervals AS
(
SELECT LAG(p.price, 1) OVER(ORDER BY p.price) AS interval_start, p.price AS interval_end
FROM Prices AS p
)
SELECT i.interval_start AS b, i.interval_end AS c
FROM intervals AS i
INNER JOIN #t AS t
ON i.interval_start < t.c
AND t.b < i.interval_end
LEFT OUTER JOIN #t1 AS t1
ON i.interval_start < t1.c
AND t1.b < i.interval_end
WHERE t1.b IS NULL
NOTE: This uses half-closed intervals (the start point is included in the range, but the end point is not).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 15, 2020 at 11:44 am
thank u , i am working on it.
-- b and c col can get reversed range like insted of 2 to 5 one can enter 5 to 2 , so i have added some code for it.
similarly from overlap we have to show only one tid the last one so have added some code for it also.
-- b and e could be reversed
-- and show only latest tid from #t table in case of overlap
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)
;
create table #t1 (t1id int,b decimal(13,4) not null, E decimal(13,4) not null);
Insert into #t1 (t1id,B,E)
VALUES (1, -12, -2),
(2, -10, 0),
(3, 5, -2)
--(4, 0, 10),
--(5, 8, 18),
--(6, 10, 20),
-- (7, 11, 21),
-- (8, 0, 3),
-- (9, 2, 4),
-- (10, 3, 10),
-- (11, -1, 10),
-- (12, -1, 11),
-- (13, 0, 11)
;
/****
This method uses half-closed intervals.
****/
select * into
#tt 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
)
)
SELECT * into #tt1
FROM #t1 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
)
)
; WITH Prices AS
(
SELECT p.price
FROM #tt AS t
CROSS APPLY (VALUES(t.b), (t.E)) p(price)
UNION
SELECT p.price
FROM #tt1 AS t
CROSS APPLY (VALUES(t.b), (t.E)) p(price)
UNION
SELECT @decBeginStation price
UNION
SELECT @decEndStation price
)
, intervals AS
(
SELECT LAG(p.price) OVER(ORDER BY p.price) AS interval_start, p.price AS interval_end
FROM Prices AS p WHERE P.price >=@decBeginStation AND P.price <=@decEndStation
)
--SELECT * FROM INTERVALS ORDER BY INTERVAL_START
SELECT * FROM (
SELECT
ROW_NUMBER() OVER(PARTITION BY I.interval_start ORDER BY t.tid desc ) RN, T.TID,
i.interval_start AS b, i.interval_end AS E
FROM intervals AS i
INNER JOIN #tt AS t
ON i.interval_start < t.E
AND t.b < i.interval_end
LEFT OUTER JOIN #tt1 AS t1
ON i.interval_start < t1.E
AND t1.b < i.interval_end
WHERE t1.b IS NULL
) T
WHERE T.RN =1
drop table #t
drop table #t1
drop table #tt
drop table #tt1
January 22, 2020 at 10:35 am
-- 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
-- for that i have used following logic in where clause i want to known is there any short way to do it with out putting small value in "b" and bigger in "e"
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply