?
Solved

Exclusion (opposite of intersection)

Posted on 2005-05-10
7
Medium Priority
?
3,083 Views
Last Modified: 2008-02-01
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
Comment
Question by:gladxml
7 Comments
 
LVL 26

Expert Comment

by:Hilaire
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

by:geotiger
ID: 13967544

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
 
LVL 13

Expert Comment

by:ispaleny
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 13

Expert Comment

by:ispaleny
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

by:gladxml
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

Thanks for your time.
0
 
LVL 13

Accepted Solution

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

by:gladxml
ID: 14016564
thanks for your help guys
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question