CODEWITHSUNDEEP
sqlsql-servert-sql
Lee Branch
Lee Branch

Reputation: 21

Find differences between 2 similar tables (Difference in counts)

I have a 2 similar tables, one that is a live table that gets updated monthly, and the other, the same data but a snapshot table, as in shows the data as it was when in came in a particular, with an extra column called Live_History_Month to determine the month_year the data came in.

I am trying to see if there has been any movement with the counts of a particular column so I can further investigate. I have created the following SQL code

select codetoinvestigate
,Year_Month
,count(*)
from tbl1
where Live_History_Month = Year_Month
group by codetoinvestigate
,Year_Month


select codetoinvestigate
,Year_Month
,count(*)
from tbl2
group by codetoinvestigate
,Year_Month

Now how do I link these so I can see if any differences between the counts of the specified column so I can make an informed decision that I need to investigate.

Just to confirm tbl1 is the snapshot table and tbl2is the table with the current latest data.

Thank you

Upvotes: 2

Views: 60

Answers (5)

KMM
KMM

Reputation: 135

On SQL Server 2012 or later? Try:

select      codetoinvestigate
            ,Year_Month
            ,count(*)
from        tbl2
group by    codetoinvestigate
            ,Year_Month

EXCEPT 

select      codetoinvestigate
            ,Year_Month
            ,count(*)
from        tbl1
where       Live_History_Month = Year_Month
group by    codetoinvestigate
            ,Year_Month

This will return differences found in the snapshot table (tbl1) that don't match your current latest data (tbl2).

Upvotes: 0

GrabnaMax
GrabnaMax

Reputation: 47

If you just want to see the changes then why don´t you make a Minus?

select codetoinvestigate
,Year_Month
,count(*)
from tbl1
where Live_History_Month = Year_Month
group by codetoinvestigate
,Year_Month

minus

select codetoinvestigate
,Year_Month
,count(*)
from tbl2
group by codetoinvestigate
,Year_Month

then you only get the rows where there was a change

Upvotes: 0

dnoeth
dnoeth

Reputation: 60482

This will return different counts and missing codes 

with t1 as
 (
    select codetoinvestigate
    ,Year_Month
    ,count(*) as cnt
    from tbl1
    where Live_History_Month = Year_Month
    group by codetoinvestigate
    ,Year_Month
 ),
t2 as
 (
    select codetoinvestigate
    ,Year_Month
    ,count(*) as cnt
    from tbl2
    group by codetoinvestigate
    ,Year_Month
 )

select coalesce(t1.codetoinvestigate,  t2.codetoinvestigate)
   ,coalesce(t1.Year_Month, t2.Year_Month)
   ,t1.cnt
   ,t2.cnt
   ,case when t1.codetoinvestigate is null 
           then 'missing code t1'
          when t2.codetoinvestigate is null 
            then 'missing code t2'
          when t1.cnt <> t2.cnt 
            then 'count different'
        etc...
    end
from t1 full join t2
  on t1.codetoinvestigate = t2.codetoinvestigate
 and t1.Year_Month = t2.Year_Month
 and t1.cnt <> t2.cnt

To check for different counts only, switch to an Inner Join

Upvotes: 1

Fahmi
Fahmi

Reputation: 37483

You can try using join two table 

select A.codetoinvestigate,A.Year_Month, A.cnt1, B.cnt2 from 
    (
    select codetoinvestigate
    ,Year_Month
    ,count(*) as cnt1
    from tbl1
    where Live_History_Month = Year_Month
    group by codetoinvestigate
    ,Year_Month
    ) A inner join 
    (
    select codetoinvestigate
    ,Year_Month
    ,count(*) cnt2
    from tbl2
    group by codetoinvestigate
    ,Year_Month
    )B on A.codetoinvestigate=B.codetoinvestigate and A.Year_Month=B.Year_Month

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use UNION ALL :

select t1.codetoinvestigate, t1.Year_Month, count(*) cnt, 'tbl1' as table_name
from tbl1 t1
where t1.Live_History_Month = Year_Month
group by t1.codetoinvestigate, t1.Year_Month
union all
select t2.codetoinvestigate, t2.Year_Month, count(*), 'tbl2' 
from tbl2 t2
group by t2.codetoinvestigate, t2.Year_Month;

By this way you can filter out the table name to get the difference.

Upvotes: 0

Related Questions