Comparing Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Comparing Query

Author

Topic

JVisconti
Starting Member

47 Posts

Posted - 2010-04-22 : 08:37:16

I have the following query, I am using it to compare to tables in my database. I need to see if all the values of a field called dbaaudit are in another table'f field called F03_ACCT_N:


--Check to see if all DBA names in MonetaryAudit are accounted for in FNMS_03
declare @a_date as datetime
set @a_date = '04/19/2010'


--insert into aciaudit
select distinct 'DBA in Audit (Monetary) but missing from MDS (FNMS_03)',
fnms03id,assoc,chain,mid,dbaaudit,0,0,dbaFNMS,'','',''
from aciaudit_monetary 
left outer join aciboarding_fnms_03 on substring(f03_mid_nu,3,8) = mid
where (dbaaudit not in aciboarding_fnms_03) and (file_date = @a_date)

any help would be greatly appreciated

Jon

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 08:52:07

whats the related column in aciboarding_fnms_03 for dbaudit?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JVisconti
Starting Member

47 Posts

Posted - 2010-04-22 : 08:58:30

That field is called F03_ACCT_N.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 09:02:24

[code]
--Check to see if all DBA names in MonetaryAudit are accounted for in FNMS_03
declare @a_date as datetime
set @a_date = '04/19/2010'

--insert into aciaudit
select distinct 'DBA in Audit (Monetary) but missing from MDS (FNMS_03)',
fnms03id,assoc,chain,mid,dbaaudit,0,0,dbaFNMS,'','',''
from aciaudit_monetary
left outer join aciboarding_fnms_03 on substring(f03_mid_nu,3,8) = mid
and(dbaaudit =F03_ACCT_N) and (file_date = @a_date)
where F03_ACCT_N is null
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JVisconti
Starting Member

47 Posts

Posted - 2010-04-22 : 09:05:15

Thanks! That got me working, doing some other edits to get specifics, but this is a great step stone.

Thanks again.

Jon

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 09:06:47

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources