Getting different scenarios from minus table

Question

I have this two tables:

Table A

ID  TYPE    SDATE   EDATE   RATING

1   M   1/1/2010    1/1/2011    A
1   M   1/3/2010    1/4/2011    B
2   A   7/2/2010    1/31/2015   C
3   M   3/1/2011    1/20/2012   B
4   A   3/1/2011    1/20/2012   B
5   M   3/1/2009    3/1/2009    F
6   M   12/31/2006  12/31/9999  A
7   A   1/1/2006    12/31/9999  B

Table B

ID  TYPE    SDATE   EDATE   RATING

1   M   1/1/2010    1/1/2011    A
2   A   7/2/2010    1/31/2015   C
2   A   9/2/2010    1/31/2015   C
3   M   3/1/2011    1/20/2012   B
4   A   3/1/2011    1/20/2012   C
6   M   12/31/2006  12/31/2015  A
7   A   2/1/2006    12/31/9999  B
8   M   1/2/2010    1/2/2012

When I perform a table A minus table B, it gets me the next result:

ID  TYPE    SDATE   EDATE   RATING

1   M   1/3/2010    1/4/2011    B
4   A   3/1/2011    1/20/2012   B
5   M   3/1/2009    3/1/2009    F
6   M   12/31/2006  12/31/9999  A
7   A   1/1/2006    12/31/9999  B

I have already identified the case where A.id is not in B.id, but how do I check when it is different in some other column?

I'm not allowed to create anything on the schema, so I have to do it through queries.

So far, this is what I have, but it gives me data that is not correct.



    with aminusb as
    (
        select
            *
        from A
        minus
        select
            *
        from B
    )
    select
        diff.*
    from
        aminusb diff
        ,B
    where
        diff.id = B.id
        and
            diff.start_date = B.start_date
        and
            diff.end_date = B.end_date
        and
            diff.rating <> B.rating

Do you know how could I get the different scenarios?

When everything is equal but, rating.
When everything is equal but, end date.
When nothing is equal but, it has the same id.

I need to get a list of the records that fulfill every scenario so people can check it on detail.

Thanks.

You might try unpivoting A-B and B-A, join the results on ID and ColumnName and filter out ones having the same Value. After that you will have a list of differences by columns. — Nikola Markovinović
@NikolaMarkovinović Hi, I tried with something like this, but it seems that it doesn't work for oracle 10g. with aminusb as ( SELECT * FROM A MINUS SELECT * FROM B ) select diff from aminusb diff,B where diff.id = B.id and diff.start_date = B.start_date and diff.end_date = B.end_date and diff.rating <> B.rating unpivot ( rating_cd for as in ( SELECT DISTINCT (id) FROM A ) ) — StrayChild01
@NikolaMarkovinović Well, at least we tried. Thanks for your help. — StrayChild01

Naval Naval · Accepted Answer · 2012-06-11T03:49:46

This would give uncommon rows:

 Select startdate, enddate, rating 
   from TableA 
Intersect 
 Select startdate, enddate, rating 
   from TableB

To Show All records:

 Select TableA.startdate, TableA.enddate, TableA.rating
  from TableA 
UNION 
 Select startdate, enddate, rating 
 from TableB

Case 1:

 Select TableA.*, TableB.* 
   from tableA,TableB 
  Where TableA.StartDate = tableB.StartDate  
    And tableA.Enddate=TableB.Enddate 
    And TableA.Rating <>TableB.Rating

And TableA.Rating = TableB.Rating

Case 2:

 Select TableA.*, TableB.*
   from tableA,TableB 
  Where TableA.StartDate = tableB.StartDate 
    And tableA.Enddate<>TableB.Enddate
    And TableA.Rating = TableB.Rating

Case 3:

 Select TableA.*, TableB.* 
   from tableA,TableB 
  Where TableA.StartDate <> tableB.StartDate 
    And tableA.Enddate<>TableB.Enddate 
    And TableA.Rating <>TableB.Rating
    And TableA.ID =TableB.ID

Getting different scenarios from minus table

1 Answers