Solved

# Exclusion (opposite of intersection)

Posted on 2005-05-10
Medium Priority
3,083 Views
hi everybody,

i have two separate tables (Table 1 and 2). I want to get the data beyond the intersetion of each EISSN field range.

Table 1
EISSN              VolumeStart                  VolumeEnd

A                      3                                  12
B                      2                                  3

Table 2
EISSN              VolumeStart                  VolumeEnd

A                      4                                  16
B                      4                                  5

Desired Output Data

Result
EISSN              Volumes

A                      3
A                      4-12
B                      2-5

Also, i want to know if this is doable in a single query. Thanks.
0

LVL 26

Expert Comment

ID: 13967252
Could you explain in plain english the rules you apply to get the desired output with the sample data provided above ?
0

LVL 12

Expert Comment

ID: 13967544

I do not understand how you could get two records for A. Based on the set logic, the desired result should be:

Table 1     3------------12                      2-3
Table 2       4----------------16                    4-5
intersection   4-----------12                      null
exclusion      3,13-16                            2-5

I do not think that you can get desired result with simple SQL statement. You may need to write a SP.

GT
0

LVL 13

Expert Comment

ID: 13967551
Some special exclusion?
(3,12) excl (4,16) = [(3,3),(13,16)] you say [(3,3),(4,12)] ... left exclusion, right inclusion; (3,12) in 2 parts
(2, 3) excl (4, 5) = [(2,5)] you say (2,5) ... OK
0

LVL 13

Expert Comment

ID: 13968363

This for normal exclusion:

/*Prepare a table of all volumes, can be pre-run by ETL process or can include all available volumes*/
create table VolumeList(X int primary key clustered)
GO
declare @Min int,@Max int
select @Min=min(Volume) ,@Max=max(Volume)
from (
select VolumeStart Volume from Table1 union all
select VolumeEnd from Table1 union all
select VolumeStart from Table2 union all
select VolumeEnd from Table2
) X
set nocount on
while @Min<=@Max begin
insert VolumeList values (@Min)
set @Min=@Min+1
end
set nocount off
GO
/*Return ranges*/
select distinct EISSN
,case when X=1 then A else C end A
,case when X=1 then B else D end A
from
(
select A.EISSN
,min(case when C.X is null then A.X end) A
,min(case when B.X is null then A.X end) B
,max(case when C.X is null then A.X end) C
,max(case when B.X is null then A.X end) D
from
(
select EISSN,X from (
select A.EISSN,B.X
from Table1 A
join VolumeList B on B.X between A.VolumeStart and A.VolumeEnd
union all
select A.EISSN,B.X
from Table2 A
join VolumeList B on B.X between A.VolumeStart and A.VolumeEnd
) x
group by EISSN,X
having count(*)=1-- order by 1,2
) A
left join
(
select EISSN,X from (
select A.EISSN,B.X
from Table1 A
join VolumeList B on B.X between A.VolumeStart and A.VolumeEnd
union all
select A.EISSN,B.X
from Table2 A
join VolumeList B on B.X between A.VolumeStart and A.VolumeEnd
) x
group by EISSN,X
having count(*)=1
) B on A.EISSN=B.EISSN and A.X=B.X-1
left join
(
select EISSN,X from (
select A.EISSN,B.X
from Table1 A
join VolumeList B on B.X between A.VolumeStart and A.VolumeEnd
union all
select A.EISSN,B.X
from Table2 A
join VolumeList B on B.X between A.VolumeStart and A.VolumeEnd
) x
group by EISSN,X
having count(*)=1
) C on A.EISSN=C.EISSN and A.X=C.X+1
group by A.EISSN
) XX
cross join (select 1 X union all select 2) XY
0

LVL 15

Author Comment

ID: 13974386
Sorry for not elaborating my question and wrong output for EISSN='A', this s/b volume 3, 13-16

I need to extract affected volumes from the two separate table updates. I am using the EISSN field as the primary key in determining volume difference.

The first table comprises the initial volumes to be processed whereas the 2nd table lists the final volumes to be processed. Given this scenario, i want to extract the affected volumes, thus for the exmaple given, I want the volume 3, and volumes 13-16 as the result data for EISSN='A'

actually the output display does not matter to me .

the output could be

A   3    3
A   13  13
A   14  14
A   15  15
A   16  16
B    2   2
B    3   3
B    4   4
B    5   5

0

LVL 13

Accepted Solution

ispaleny earned 2000 total points
ID: 13976729
-- Create VolumeList table from my previous post and run the following query to get  "A   3    3" output

select EISSN,X,X from (
select A.EISSN,B.X
from Table1 A
join VolumeList B on B.X between A.VolumeStart and A.VolumeEnd
union all
select A.EISSN,B.X
from Table2 A
join VolumeList B on B.X between A.VolumeStart and A.VolumeEnd
) x
group by EISSN,X
having count(*)=1
order by 1,2
0

LVL 15

Author Comment

ID: 14016564
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
###### Suggested Courses
Course of the Month8 days, 16 hours left to enroll