I am trying to audit values between 2 different databases.
ie: We send out dividend checks every year, and it is based on how much
our customers spend with us. Therefore, once a year we allocate our
profits to the customers by dumping customer information from the
billing database to the accounting database. Now the check gets issued
from the accounting database after that and the 1099 capital gains gets
issued after the check. The time that elapses between the checks and
1099's is the problem, because people move, they change their names etc.
etc. What I want to do is audit the accounting data against the current
billing data. The records have a common account number to join, but I
don't know how to form a select (or whatever query it may be) accross
databases. I've only worked the select from within a single database.
I would appreciate some help, I am very new to SQL and my experience is
limited to the SELECT statement. However, I am willing and determined
to learn new and different things. :)
Thanks in advance,
BenA guess at what you may need...
SELECT
A.CustomerId,
A.CustomerName,
A.CustomerAddress,
B.CustomerName,
B.CustomerAddress,
A.Dividend
FROM
MyServer.FirstDatabase.dbo.FirstList A
LEFT JOIN MyServer.SecondDatabase.dbo.SecondList B
ON A.CustomerId = B.CustomerId
WHERE
A.Dividend <> 0.00 AND
A.Deceased = 0
ORDER BY
A.CustomerName,
B.CustomerName,
A.CustomerAddress,
B.CustomerAddress
I've deliberately left the fields as something obvious (in case anyone
asks about naming standards etc) so that you can see what basic work
you would need to do. Please note, I've used aliases against the table
names as it's always good to do this for ease of reading. I've assumed
that you want everything from the first list (A) and only the records
from the second list (B) - hence the left join.
I've also added a 'Deceased' clause so you can see how to add
additional statements in the WHERE part of the statment. Obviously you
will need to substitute the various fields and databases for your own.
Hope that helps
Ryan
Ben Wehrspann wrote:
> Hello-
> I am trying to audit values between 2 different databases.
> ie: We send out dividend checks every year, and it is based on how
much
> our customers spend with us. Therefore, once a year we allocate our
> profits to the customers by dumping customer information from the
> billing database to the accounting database. Now the check gets
issued
> from the accounting database after that and the 1099 capital gains
gets
> issued after the check. The time that elapses between the checks and
> 1099's is the problem, because people move, they change their names
etc.
> etc. What I want to do is audit the accounting data against the
current
> billing data. The records have a common account number to join, but
I
> don't know how to form a select (or whatever query it may be) accross
> databases. I've only worked the select from within a single
database.
> I would appreciate some help, I am very new to SQL and my experience
is
> limited to the SELECT statement. However, I am willing and
determined
> to learn new and different things. :)
> Thanks in advance,
> Ben|||Ryan-
Thank you very much! Here's what I wrote:
selectCustMast114.dbo.cap_credit.ccracct_num,
CustMast114.dbo.cap_credit.ccracct_ssein,
CustMast114.dbo.cap_credit.ccracct_name,
CustMast114.dbo.cm_address.add_1,
CustMast114.dbo.cm_address.add_2,
Accounting.dbo.cccheck.ccchk_capcredno,
Accounting.dbo.cccheck.ccchk_ssein,
Accounting.dbo.cccheck.ccchk_name,
Accounting.dbo.cccheck.ccchk_add1,
Accounting.dbo.cccheck.ccchk_add2
fromCustMast114.dbo.cap_credit,
CustMast114.dbo.cm_address,
Accounting.dbo.cccheck
whereCustMast114.dbo.cap_credit.add_uid=CustMast114.dbo .cm_address.add_uid
and
CustMast114.dbo.cap_credit.ccracct_num=Accounting. dbo.cccheck.ccchk_capcredno
andAccounting.dbo.cccheck.ccchk_disbursdt > '2004'
and
(CustMast114.dbo.cap_credit.ccracct_ssein <>
Accounting.dbo.cccheck.ccchk_ssein
orCustMast114.dbo.cm_address.add_1 <> Accounting.dbo.cccheck.ccchk_add1
orCustMast114.dbo.cm_address.add_2 <> Accounting.dbo.cccheck.ccchk_add2
)
order by CustMast114.dbo.cap_credit.ccracct_num
Is there an easy way to tag the entries (rows) individually as to which
of the "or" tests that it failed? Or possibly group them by which of the
"or" tests they failed?
Thanks in advance,
Ben
Ryan wrote:
> A guess at what you may need...
> SELECT
> A.CustomerId,
> A.CustomerName,
> A.CustomerAddress,
> B.CustomerName,
> B.CustomerAddress,
> A.Dividend
> FROM
> MyServer.FirstDatabase.dbo.FirstList A
> LEFT JOIN MyServer.SecondDatabase.dbo.SecondList B
> ON A.CustomerId = B.CustomerId
> WHERE
> A.Dividend <> 0.00 AND
> A.Deceased = 0
> ORDER BY
> A.CustomerName,
> B.CustomerName,
> A.CustomerAddress,
> B.CustomerAddress
> I've deliberately left the fields as something obvious (in case anyone
> asks about naming standards etc) so that you can see what basic work
> you would need to do. Please note, I've used aliases against the table
> names as it's always good to do this for ease of reading. I've assumed
> that you want everything from the first list (A) and only the records
> from the second list (B) - hence the left join.
> I've also added a 'Deceased' clause so you can see how to add
> additional statements in the WHERE part of the statment. Obviously you
> will need to substitute the various fields and databases for your own.
> Hope that helps
> Ryan
>
> Ben Wehrspann wrote:
>>Hello-
>>
>>I am trying to audit values between 2 different databases.
>>
>>ie: We send out dividend checks every year, and it is based on how
> much
>>our customers spend with us. Therefore, once a year we allocate our
>>profits to the customers by dumping customer information from the
>>billing database to the accounting database. Now the check gets
> issued
>>from the accounting database after that and the 1099 capital gains
> gets
>>issued after the check. The time that elapses between the checks and
>
>>1099's is the problem, because people move, they change their names
> etc.
>>etc. What I want to do is audit the accounting data against the
> current
>>billing data. The records have a common account number to join, but
> I
>>don't know how to form a select (or whatever query it may be) accross
>
>>databases. I've only worked the select from within a single
> database.
>>I would appreciate some help, I am very new to SQL and my experience
> is
>>limited to the SELECT statement. However, I am willing and
> determined
>>to learn new and different things. :)
>>
>>Thanks in advance,
>>
>>Ben
>|||Ben Wehrspann (ben@.jtt.net) writes:
> Is there an easy way to tag the entries (rows) individually as to which
> of the "or" tests that it failed? Or possibly group them by which of the
> "or" tests they failed?
You would have to throw in columns like this:
assindiff = CASE WHEN CustMast114.dbo.cap_credit.ccracct_ssein <>
Accounting.dbo.cccheck.ccchk_ssein
THEN 1
ELSE 0
END
Note that once you have your base query, you can manipulated like this:
SELECT a, b, COUNT(*)
FROM (SELECT a = ..., b = ...
FROM tbl
WHERE ...) AS x
GROUP BY a, b
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
