Exclusion (opposite of intersection)

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.                    
LVL 15
gladxmlAsked:
Who is Participating?
 
ispalenyCommented:
-- 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
 
HilaireCommented:
Could you explain in plain english the rules you apply to get the desired output with the sample data provided above ?
0
 
geotigerCommented:

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


ERSSN                 A                                    B
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ispalenyCommented:
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
 
ispalenyCommented:

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
 
gladxmlAuthor Commented:
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

Thanks for your time.
0
 
gladxmlAuthor Commented:
thanks for your help guys
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.