comparing two datasets

Hello - I would like to perform something lika a "diff" between two datasets
in my report.
Lets say that DS1 holds all customers and DS2 holds customers owning a car.
How do I find them customers not owning a car?
DavidPerhaps you could create a 3rd data set that would be some combination of the
queries used to create the 1st and 2nd data sets?
DS1 -
select customername
from customers
DS2 -
select name
from carowners
DS3 -
select customername
from customers
where customername in (
select name
from carowners
)
just a thought... i am not sure whast your schema looks like - there may be
a simpler solution or my solution may be too simple.
--
~lb
"David" wrote:
> Hello - I would like to perform something lika a "diff" between two datasets
> in my report.
> Lets say that DS1 holds all customers and DS2 holds customers owning a car.
> How do I find them customers not owning a car?
> David
>|||Problem is that one of the datasets is tabular data from a textfile, I
cannot use SQL to query it, but otherwise your answer still helps me to
define the problem better, thx :)
Sorry for not pointing out the flat file-stuff in the first place.
David
"lonnye" <lonnye@.discussions.microsoft.com> wrote in message
news:B3B4E2AF-A516-44EC-BE2E-EF5D98EC83AD@.microsoft.com...
> Perhaps you could create a 3rd data set that would be some combination of
> the
> queries used to create the 1st and 2nd data sets?
> DS1 -
> select customername
> from customers
> DS2 -
> select name
> from carowners
> DS3 -
> select customername
> from customers
> where customername in (
> select name
> from carowners
> )
> just a thought... i am not sure whast your schema looks like - there may
> be
> a simpler solution or my solution may be too simple.
> --
> ~lb
>
> "David" wrote:
>> Hello - I would like to perform something lika a "diff" between two
>> datasets
>> in my report.
>> Lets say that DS1 holds all customers and DS2 holds customers owning a
>> car.
>> How do I find them customers not owning a car?
>> Davidsqlsql

Comparing 2 different databases [NEWBIE]

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,

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

Compare strings in SQL SERVER 2000

hi,
We have SQL SERVER 2000 database with a table of customers. We would
like to compare the names of customers for double names. We want to
find same names or names that are look like.
for example:
ID NAME ADDRESS
1 John Smith
100 John Smitth
We consider that it is a very useful we have a ranking for these names.
Have you any idea, how can do this ?.Loop LIKE operator in the BOL
SELECT <columns>FROM Table WHERE col LIKE '%Smith%'
Note: In above case SQL Server will not be able to use an index if you have
one on this column
<akoutoulakis@.gmail.com> wrote in message
news:1131368418.663514.264320@.g47g2000cwa.googlegroups.com...
> hi,
> We have SQL SERVER 2000 database with a table of customers. We would
> like to compare the names of customers for double names. We want to
> find same names or names that are look like.
> for example:
> ID NAME ADDRESS
> 1 John Smith
> 100 John Smitth
> We consider that it is a very useful we have a ranking for these names.
> Have you any idea, how can do this ?.
>|||> Loop LIKE operator in the BOL
Sorry ,should be Lookup LIKE operator in the BOL
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23qjQhv54FHA.3540@.TK2MSFTNGP10.phx.gbl...
> Loop LIKE operator in the BOL
> SELECT <columns>FROM Table WHERE col LIKE '%Smith%'
>
> Note: In above case SQL Server will not be able to use an index if you
> have one on this column
>
>
> <akoutoulakis@.gmail.com> wrote in message
> news:1131368418.663514.264320@.g47g2000cwa.googlegroups.com...
>> hi,
>> We have SQL SERVER 2000 database with a table of customers. We would
>> like to compare the names of customers for double names. We want to
>> find same names or names that are look like.
>> for example:
>> ID NAME ADDRESS
>> 1 John Smith
>> 100 John Smitth
>> We consider that it is a very useful we have a ranking for these names.
>> Have you any idea, how can do this ?.
>|||On 7 Nov 2005 05:00:18 -0800, akoutoulakis@.gmail.com wrote:
>hi,
>We have SQL SERVER 2000 database with a table of customers. We would
>like to compare the names of customers for double names. We want to
>find same names or names that are look like.
>for example:
>ID NAME ADDRESS
> 1 John Smith
> 100 John Smitth
>We consider that it is a very useful we have a ranking for these names.
>Have you any idea, how can do this ?.
Hi akoutoulakis,
Searching for duplicates:
SELECT Name, COUNT(*) AS NumOfDups
FROM YourTable
GROUP BY Name
HAVING COUNT(*) > 1
Showing all info for duplicates:
SELECT a.ID, a.Name, a.Address
FROM YourTable AS a
WHERE EXISTS
(SELECT *
FROM YourTable AS b
WHERE b.Name = a.Name
AND b.ID <> a.ID)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you for a help.

Compare strings in SQL SERVER 2000

hi,
We have SQL SERVER 2000 database with a table of customers. We would
like to compare the names of customers for double names. We want to
find same names or names that are look like.
for example:
ID NAME ADDRESS
1 John Smith
100 John Smitth
We consider that it is a very useful we have a ranking for these names.
Have you any idea, how can do this ?.Loop LIKE operator in the BOL
SELECT <columns>FROM Table WHERE col LIKE '%Smith%'
Note: In above case SQL Server will not be able to use an index if you have
one on this column
<akoutoulakis@.gmail.com> wrote in message
news:1131368418.663514.264320@.g47g2000cwa.googlegroups.com...
> hi,
> We have SQL SERVER 2000 database with a table of customers. We would
> like to compare the names of customers for double names. We want to
> find same names or names that are look like.
> for example:
> ID NAME ADDRESS
> 1 John Smith
> 100 John Smitth
> We consider that it is a very useful we have a ranking for these names.
> Have you any idea, how can do this ?.
>|||> Loop LIKE operator in the BOL
Sorry ,should be Lookup LIKE operator in the BOL
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23qjQhv54FHA.3540@.TK2MSFTNGP10.phx.gbl...
> Loop LIKE operator in the BOL
> SELECT <columns>FROM Table WHERE col LIKE '%Smith%'
>
> Note: In above case SQL Server will not be able to use an index if you
> have one on this column
>
>
> <akoutoulakis@.gmail.com> wrote in message
> news:1131368418.663514.264320@.g47g2000cwa.googlegroups.com...
>|||On 7 Nov 2005 05:00:18 -0800, akoutoulakis@.gmail.com wrote:

>hi,
>We have SQL SERVER 2000 database with a table of customers. We would
>like to compare the names of customers for double names. We want to
>find same names or names that are look like.
>for example:
>ID NAME ADDRESS
> 1 John Smith
> 100 John Smitth
>We consider that it is a very useful we have a ranking for these names.
>Have you any idea, how can do this ?.
Hi akoutoulakis,
Searching for duplicates:
SELECT Name, COUNT(*) AS NumOfDups
FROM YourTable
GROUP BY Name
HAVING COUNT(*) > 1
Showing all info for duplicates:
SELECT a.ID, a.Name, a.Address
FROM YourTable AS a
WHERE EXISTS
(SELECT *
FROM YourTable AS b
WHERE b.Name = a.Name
AND b.ID <> a.ID)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you for a help.

Compare strings in SQL SERVER 2000

hi,
We have SQL SERVER 2000 database with a table of customers. We would
like to compare the names of customers for double names. We want to
find same names or names that are look like.
for example:
ID NAME ADDRESS
1 John Smith
100 John Smitth
We consider that it is a very useful we have a ranking for these names.
Have you any idea, how can do this ?.
Loop LIKE operator in the BOL
SELECT <columns>FROM Table WHERE col LIKE '%Smith%'
Note: In above case SQL Server will not be able to use an index if you have
one on this column
<akoutoulakis@.gmail.com> wrote in message
news:1131368418.663514.264320@.g47g2000cwa.googlegr oups.com...
> hi,
> We have SQL SERVER 2000 database with a table of customers. We would
> like to compare the names of customers for double names. We want to
> find same names or names that are look like.
> for example:
> ID NAME ADDRESS
> 1 John Smith
> 100 John Smitth
> We consider that it is a very useful we have a ranking for these names.
> Have you any idea, how can do this ?.
>
|||> Loop LIKE operator in the BOL
Sorry ,should be Lookup LIKE operator in the BOL
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23qjQhv54FHA.3540@.TK2MSFTNGP10.phx.gbl...
> Loop LIKE operator in the BOL
> SELECT <columns>FROM Table WHERE col LIKE '%Smith%'
>
> Note: In above case SQL Server will not be able to use an index if you
> have one on this column
>
>
> <akoutoulakis@.gmail.com> wrote in message
> news:1131368418.663514.264320@.g47g2000cwa.googlegr oups.com...
>
|||On 7 Nov 2005 05:00:18 -0800, akoutoulakis@.gmail.com wrote:

>hi,
>We have SQL SERVER 2000 database with a table of customers. We would
>like to compare the names of customers for double names. We want to
>find same names or names that are look like.
>for example:
>ID NAME ADDRESS
> 1 John Smith
> 100 John Smitth
>We consider that it is a very useful we have a ranking for these names.
>Have you any idea, how can do this ?.
Hi akoutoulakis,
Searching for duplicates:
SELECT Name, COUNT(*) AS NumOfDups
FROM YourTable
GROUP BY Name
HAVING COUNT(*) > 1
Showing all info for duplicates:
SELECT a.ID, a.Name, a.Address
FROM YourTable AS a
WHERE EXISTS
(SELECT *
FROM YourTable AS b
WHERE b.Name = a.Name
AND b.ID <> a.ID)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thank you for a help.

Compare Report

Hi there,

I want to set up a report tha will take my 30 best customers of Q1 2006 and that will display also the facts of Q1 2007.

Can I use a parameter to select these customers on 1 report instead of 30 different reports.

Thx

Is this a report built off of an MDX query or SQL?

For SQL, just use the SELECT TOP n syntax.

For MDX, you may have to use a query expression.

If you could elaborate on the structure of the data and what would define a best customer that would help.

cheers,

Andrew

|||

Yes, you can create a report with one parameter which will be multi-valued and its available values from a query which uses TOP 30 in its SQL text.

Shyam