Query to find missing records
Posted by: Deepak Wahi
Date: January 29, 2013 03:02PM
Please help. My query creates 2 tables from two different servers. I want to compare the two tables to see what claims are missing between the two table. My query is below but I keep getting an error. What am I doing wrong?
Select cr.rsv_typ_cd, c.rpt_num_txt, c.clm_num, c.lif_stts_cd, cr.rins_role_cd, sum(cr.Rsv_amt)as 'LTDV rsv_amt'
from t_clm c inner join t_clm_rsv cr
On (c.clm_num = cr.clm_num and c.xtrct_dt = cr.xtrct_dt)
where c.xtrct_dt ='12/31/2012' and
cr.rsv_typ_cd in ('GAAP')
and c.lif_stts_cd in ('APP', 'SUS')
and cr.feed_cd in ('0001', '0003')
Group by rsv_typ_cd, c.rpt_num_txt, c.clm_num, c.lif_stts_cd, cr.rins_role_cd
select rsv_typ_cd as 'grdb rsv_typ_cd' , xprn_num_txt, clm_num as 'grdb clm_num', RINS_ROLE_NM as 'grdb rins_role_nm', sum(rsv_amt)as 'GRDB RSV_AMT'
where val_dt = '12/31/2012'
and rsv_typ_cd in ('GAAP', 'STAT')
and feed_num = '1050'
group by rsv_typ_cd, xprn_num_txt, clm_num, RINS_ROLE_NM
order by rsv_typ_cd, clm_num
from #temp2 d inner join #temp3 dr
On (d.clm_num = dr.grdb clm_num)
where d.clm_num <> dr.grdb clm_num)