Comparing within a Self Join

Greetings All,
Have a table with the following sample data
select * from t1
PatientName RxFillDate
JACK 01/01/2006
JACK 02/01/2006
JACK 03/01/2006
JILL 04/04/2006
JILL 05/25/2006
JILL 06/25/2006
Here is what my objective is..
I want to output the PatientNames who have a Difference of more than 30 days
in any of their RxFillDate.
So in the above Sample I would just get JILL as the Output , Since the
difference
between one pair of he RxFillDate ( 04/04/2006 and 05/25/2006 ) is more than
30 Days.
Hope I explained it clearly - Any help will be appreciated.
Thanks in advance,
AbTry:
select distinct
x.PatientName
from
t1 x
join
t2 y on y.PatientName = x.PatientName
and y.RxFillDate > x.RxFillDate +30
and not exists
(
select
*
from
t1 z
where
z.PatientName = x.PatientName
and z.RxFillDate between x.RxFillDate and y.RxFillDate
)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ab" <Ab@.discussions.microsoft.com> wrote in message
news:17A7AA22-364E-46CE-97E6-7FC128C888AE@.microsoft.com...
Greetings All,
Have a table with the following sample data
select * from t1
PatientName RxFillDate
JACK 01/01/2006
JACK 02/01/2006
JACK 03/01/2006
JILL 04/04/2006
JILL 05/25/2006
JILL 06/25/2006
Here is what my objective is..
I want to output the PatientNames who have a Difference of more than 30 days
in any of their RxFillDate.
So in the above Sample I would just get JILL as the Output , Since the
difference
between one pair of he RxFillDate ( 04/04/2006 and 05/25/2006 ) is more than
30 Days.
Hope I explained it clearly - Any help will be appreciated.
Thanks in advance,
Ab|||Tom, Thanks for your reply
Will this also work for in situation as below ( maybe i should have
mentioned it before)

> select * from t1
> PatientName RxFillDate
> JACK 01/01/2006
> JACK 02/01/2006
> JACK 03/01/2006
> JILL 04/04/2006
> JILL 05/25/2006
> JILL 06/25/2006
> JILL 06/28/2006
>ROCKY 04/01/2006
>MARK 05/03/2006
In the above - again Patient JILL would showup in the output..
"Tom Moreau" wrote:

> Try:
> select distinct
> x.PatientName
> from
> t1 x
> join
> t2 y on y.PatientName = x.PatientName
> and y.RxFillDate > x.RxFillDate +30
> and not exists
> (
> select
> *
> from
> t1 z
> where
> z.PatientName = x.PatientName
> and z.RxFillDate between x.RxFillDate and y.RxFillDate
> )
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Ab" <Ab@.discussions.microsoft.com> wrote in message
> news:17A7AA22-364E-46CE-97E6-7FC128C888AE@.microsoft.com...
> Greetings All,
> Have a table with the following sample data
> select * from t1
> PatientName RxFillDate
> JACK 01/01/2006
> JACK 02/01/2006
> JACK 03/01/2006
> JILL 04/04/2006
> JILL 05/25/2006
> JILL 06/25/2006
> Here is what my objective is..
> I want to output the PatientNames who have a Difference of more than 30 da
ys
> in any of their RxFillDate.
> So in the above Sample I would just get JILL as the Output , Since the
> difference
> between one pair of he RxFillDate ( 04/04/2006 and 05/25/2006 ) is more th
an
> 30 Days.
> Hope I explained it clearly - Any help will be appreciated.
> Thanks in advance,
> Ab
>|||She should show up, since there was at least one period of time where she
waited longer than 30 days to get her meds. What's the exact requirement?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ab" <Ab@.discussions.microsoft.com> wrote in message
news:4BAC8463-D694-43F3-83F7-AB0E538F3954@.microsoft.com...
Tom, Thanks for your reply
Will this also work for in situation as below ( maybe i should have
mentioned it before)

> select * from t1
> PatientName RxFillDate
> JACK 01/01/2006
> JACK 02/01/2006
> JACK 03/01/2006
> JILL 04/04/2006
> JILL 05/25/2006
> JILL 06/25/2006
> JILL 06/28/2006
>ROCKY 04/01/2006
>MARK 05/03/2006
In the above - again Patient JILL would showup in the output..
"Tom Moreau" wrote:

> Try:
> select distinct
> x.PatientName
> from
> t1 x
> join
> t2 y on y.PatientName = x.PatientName
> and y.RxFillDate > x.RxFillDate +30
> and not exists
> (
> select
> *
> from
> t1 z
> where
> z.PatientName = x.PatientName
> and z.RxFillDate between x.RxFillDate and y.RxFillDate
> )
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Ab" <Ab@.discussions.microsoft.com> wrote in message
> news:17A7AA22-364E-46CE-97E6-7FC128C888AE@.microsoft.com...
> Greetings All,
> Have a table with the following sample data
> select * from t1
> PatientName RxFillDate
> JACK 01/01/2006
> JACK 02/01/2006
> JACK 03/01/2006
> JILL 04/04/2006
> JILL 05/25/2006
> JILL 06/25/2006
> Here is what my objective is..
> I want to output the PatientNames who have a Difference of more than 30
> days
> in any of their RxFillDate.
> So in the above Sample I would just get JILL as the Output , Since the
> difference
> between one pair of he RxFillDate ( 04/04/2006 and 05/25/2006 ) is more
> than
> 30 Days.
> Hope I explained it clearly - Any help will be appreciated.
> Thanks in advance,
> Ab
>|||select distinct t1.PatientName
from t1
where dateadd(day, 30, RxFillDate) < (
select min(t2.RxFillDate) from t1 t2
where t1.PatientName = t2.PatientName
and t1.RxFillDate < t2.RxFillDate)|||Thanks for your postings...But somehow I could not get the correct results.
Also I have the requirement now in much more detail...
Thanks for your responses and help.
We have a table with following information.
MemberName FillDate DaysSupply
PETER GAMBINI 09/22/05 30
PETER GAMBINI 09/24/05 30
PETER GAMBINI 12/25/05 30
MARIA ROSA 10/03/05 15
MARIA ROSA 10/18/05 30
MARIA ROSA 11/18/05 30
I am trying to find out Members with multiple Claims who had any lapses in
the Fill Dates plus a lag of 15 Days.
For eg: Member PETER GAMBINI in the 2nd record has a
Fill Date of 09/24/05 with Days Supply as 30.
and his next FillDate is 12/25/05
So ( 12/25/05 - 09/24/05 ) - 30 > 15 (Lag Days) is TRUE so Member PETER
GAMBINI would show up in my Select and hence in the Report.
The above is not true for MARIA ROSA so she would not be selected in my Quer
y.
because
...
MARIA ROSA 10/03/05 15
MARIA ROSA 10/18/05 30
MARIA ROSA 11/18/05 30
...
Her First FillDate Days Supply is 15 so
(10/18/05 - 10/03/05 ) - 15 > 15 is False
Next iteration
Her First FillDate Days Supply is 30 so
(11/18/05 - 10/08/05 ) - 30 > 15 is False
So she would not show up in the result my Query.
Hope this helps in understanding the problem..thanks in advance.
"Alexander Kuznetsov" wrote:

> select distinct t1.PatientName
> from t1
> where dateadd(day, 30, RxFillDate) < (
> select min(t2.RxFillDate) from t1 t2
> where t1.PatientName = t2.PatientName
> and t1.RxFillDate < t2.RxFillDate)
>|||The example below should be fine, you just need to replace the literal 30
with DaysSupply.
select distinct t1.PatientName
from t1
where dateadd(day, t1.DaysSupply, t1.RxFillDate) < (
select min(t2.RxFillDate) from t1 t2
where t1.PatientName = t2.PatientName
and t1.RxFillDate < t2.RxFillDate)
"Kanti Gala" <KantiGala@.discussions.microsoft.com> wrote in message
news:9BFEAEFA-9830-4DC9-A327-02564E4D10FE@.microsoft.com...
> Thanks for your postings...But somehow I could not get the correct
results.
> Also I have the requirement now in much more detail...
> Thanks for your responses and help.
> We have a table with following information.
> MemberName FillDate DaysSupply
> PETER GAMBINI 09/22/05 30
> PETER GAMBINI 09/24/05 30
> PETER GAMBINI 12/25/05 30
> MARIA ROSA 10/03/05 15
> MARIA ROSA 10/18/05 30
> MARIA ROSA 11/18/05 30
> I am trying to find out Members with multiple Claims who had any lapses in
> the Fill Dates plus a lag of 15 Days.
> For eg: Member PETER GAMBINI in the 2nd record has a
> Fill Date of 09/24/05 with Days Supply as 30.
> and his next FillDate is 12/25/05
> So ( 12/25/05 - 09/24/05 ) - 30 > 15 (Lag Days) is TRUE so Member PETER
> GAMBINI would show up in my Select and hence in the Report.
> The above is not true for MARIA ROSA so she would not be selected in my
Query.
> because
> ...
> MARIA ROSA 10/03/05 15
> MARIA ROSA 10/18/05 30
> MARIA ROSA 11/18/05 30
> ...
> Her First FillDate Days Supply is 15 so
> (10/18/05 - 10/03/05 ) - 15 > 15 is False
> Next iteration
> Her First FillDate Days Supply is 30 so
> (11/18/05 - 10/08/05 ) - 30 > 15 is False
> So she would not show up in the result my Query.
> Hope this helps in understanding the problem..thanks in advance.
>
> "Alexander Kuznetsov" wrote:
>

Comparing varchar value in int type column

Hi,

I have a varchar(255) field on the control_value table which contains 10,159,711. These values are organization_ids (type int) separated by a common.

I am trying to exclude these organization IDs with the following statement:

DECLARE @.exclude_clients varchar(255)

SELECT @.exclude_clients = value

FROM control_value

WHERE parameter = 'client_excluded'

select * from organization org

where org.organization_id not in (@.exclude_clients)

I get the following error:

Server: Msg 245, Level 16, State 1, Line 7

Syntax error converting the varchar value '10,159,711' to a column of data type int.

Is there anyway around this?

You couldn't use variables as part of in clause.

But you could use dynamic SQL for with task:

Code Snippet

DECLARE @.exclude_clients varchar(255)

SELECT @.exclude_clients = value

FROM control_value

WHERE parameter = 'client_excluded'

declare @.query varchar(1000)

set @.query ='

select * from organization org

where org.organization_id not in ('+@.exclude_clients+')'

EXECUTE(@.query)

Another approach - split @.exclude_clients into table, then use join clause. You could use ideas from this link http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

|||

You can't use 'NOT IN' in this way - i.e. use it to identify multiple values from a comma-separated list provided as a single parameter.

The NOT IN condition as written will cause the entire @.exclude_clients string to be compared with org.organsation_id. However due to datatype precedence, SQL Server is attempting to convert the string to an integer (i.e. the same datatype as org.organsation_id) before the comparison - which is why you're experiencing the error. If the value of @.exclude_clients was '45' then the query wouldn't cause an error.

One way of performing the task would be to build up your SELECT statement dynamically, see below.

Chris

Code Snippet

DECLARE @.exclude_clients VARCHAR(255)

SELECT @.exclude_clients = value

FROM control_value

WHERE parameter = 'client_excluded'

/*

SELECT *

FROM organization org

WHERE org.organization_id NOT IN (@.exclude_clients)

*/

DECLARE @.sql VARCHAR(4000)

SET @.sql = 'SELECT * FROM organisation org WHERE org.organisation_id NOT IN ('

+ @.exclude_clients + ')'

EXEC (@.sql)

|||

Code Snippet

DECLARE @.T1 table(exclude_id int)

insert into @.t1
select 10 union
select 159 union
select 711

or if control_value is a table then

insert into @.t1
select value
from control_value
where parameter= 'client_excluded'

Then you can just use @.t1 as a table in your other query(ies)

select * from organization org
where org.organization_id not in (select exclude_id from @.t1)

|||Thanks Konstantin Kosinsky and Chris Howarth. The dynamic query works fine and it really helpful.

Comparing VarCHAR FIELD with NULL

Hi, I have the following query

SELECT *
FROM PABX
INNER JOIN LOGIN ON (PABX.COD_CLIENTE = LOGIN.COD_CLIENTE)
AND LEFT(LOGIN.TELEFONE1,3) = LEFT(PABX.NRTELEFONE,3)
LEFT JOIN AUXILIAR ON (AUXILIAR.ORIGEM=LOGIN.LOCALIDADE)
WHERE
pabx.COD_cliente = 224 and
SUBSTRING(PABX.NRTELEFONE,4,1) NOT IN ('9', '8', '7')
AND LOGIN.UF = RIGHT(PABX.LOCALIDADE,2)
AND LOGIN.LOCALIDADE <> PABX.LOCALIDADE
AND PABX.CLASSIFICA IS NULL
AND PABX.LOCALIDADE <> AUXILIAR.DESTINO
AND (BLOQUEADO = 0 OR BLOQUEADO IS NULL)

But It has a problem because when AUXILIAR.DESTINO returns null (it means there is no registry) the condition AND PABX.LOCALIDADE <> AUXILIAR.DESTINO doesn't work, like 'SAO PAULO' is different from 'NULL' but for my query no it's not even equal, and this condition ommit the results....how can I solve it ?

PS: Both auxiliar.destino and pabx.localidade is varchar(255)

Thanks

what happens now?

SELECT *
FROM PABX
INNER JOIN LOGIN ON (PABX.COD_CLIENTE = LOGIN.COD_CLIENTE)
AND LEFT(LOGIN.TELEFONE1,3) = LEFT(PABX.NRTELEFONE,3)
LEFT JOIN AUXILIAR ON (AUXILIAR.ORIGEM=LOGIN.LOCALIDADE)
AND PABX.LOCALIDADE <> AUXILIAR.DESTINO
WHERE
pabx.COD_cliente = 224 and
SUBSTRING(PABX.NRTELEFONE,4,1) NOT IN ('9', '8', '7')
AND LOGIN.UF = RIGHT(PABX.LOCALIDADE,2)
AND LOGIN.LOCALIDADE <> PABX.LOCALIDADE
AND PABX.CLASSIFICA IS NULL

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

It works, thanks a LOT

I lov u...

|||

you can also change PABX.LOCALIDADE <> AUXILIAR.DESTINO to

PABX.LOCALIDADE <> COALESCE(AUXILIAR.DESTINO,'')

|||

Wich one do you think it's better ?

Thanks

Comparing values between 2 matrices (matrix)

Hello
I have two matrices. One contains sales data for the current year, the other prior year. Both matrices use different data sets

I'd like to compare the two - possibly by creating a third matrix that subtracts prior year from current year.

Any ideas? When I create a third matrix and substitute a formula like =sum(values, "Data source for matrix 1") - sum(values, "Data source for matrix 2"), the resultant matrix subtracts the grand total from the first matrix - not the individual "cell".

Any suggestions are appreciated.
Thanks

I would like to know the official answer to this too...

see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1673719&SiteID=1 for simular question...

comparing two varchar variable

Table One has for column id = 'AAA-BBB-CCC' , 'AAA-BBB-DDD'
Table Two has column id = 'AAA-BBB-CCCI' , 'AAA-BBB-DDDI'
I want to return all Table One.id which appear in table Two.id by comparing
the varchar column e.g
select One.id from One, Two
where One.id like (Two.id + '%')
Can someone show me a better way. ThanksMaybe this?
select * from one where id in (select id from two)
Ben Nevarez
"mtv" <mtv@.discussions.microsoft.com> wrote in message
news:22EE0FB6-FDFD-4824-A5C8-D4352A83FC4D@.microsoft.com...
> Table One has for column id = 'AAA-BBB-CCC' , 'AAA-BBB-DDD'
> Table Two has column id = 'AAA-BBB-CCCI' , 'AAA-BBB-DDDI'
> I want to return all Table One.id which appear in table Two.id by
> comparing
> the varchar column e.g
> select One.id from One, Two
> where One.id like (Two.id + '%')
> Can someone show me a better way. Thanks|||Thank you Ben but I believe it will not work. Ids in table Two has one
additional letter in the end.
cheers
mtv
"Ben Nevarez" wrote:

> Maybe this?
> select * from one where id in (select id from two)
> Ben Nevarez
>
> "mtv" <mtv@.discussions.microsoft.com> wrote in message
> news:22EE0FB6-FDFD-4824-A5C8-D4352A83FC4D@.microsoft.com...
>
>|||Would something like this help?
=====
CREATE TABLE test1
(
colA VARCHAR(10)
)
GO
CREATE TABLE test2
(
colB VARCHAR(10)
)
GO
INSERT INTO test1 SELECT 'AAABBBCCC'
INSERT INTO test1 SELECT 'XXXYYYZZZ'
INSERT INTO test2 SELECT 'AAABBBCCCD'
GO
SELECT
test1.colA, test2.colB
FROM
test1
INNER JOIN test2 ON test1.colA = LEFT (test2.colB, 9)
=====
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"mtv" <mtv@.discussions.microsoft.com> wrote in message
news:22EE0FB6-FDFD-4824-A5C8-D4352A83FC4D@.microsoft.com...
> Table One has for column id = 'AAA-BBB-CCC' , 'AAA-BBB-DDD'
> Table Two has column id = 'AAA-BBB-CCCI' , 'AAA-BBB-DDDI'
> I want to return all Table One.id which appear in table Two.id by
> comparing
> the varchar column e.g
> select One.id from One, Two
> where One.id like (Two.id + '%')
> Can someone show me a better way. Thanks

Comparing Two Tables Without a Cursor...HELP!

I have a project I am working on that has two tables. One is a reference
table, and the other is a table that stores incoming data from an outside
vendor. The customer wants us to write a "solution" that will compare the
incoming data table to the lookup table, and find records that don't match.
Any records in the incoming data table should have corresponding records in
the lookup table, and specific columns need to match. The "solution" will
create a list of records with anomolies.
I had planned to write a cursor to march through the lookup table and check
for records in the incoming data table, but the customer feels cursors are a
bad idea, and does not want us to use them.
Are there alternatives to cursors?
BV.Please post DDL along with
1) definition of matching rows (e.g., incoming.col1 = lookup.col1 and
incoming.col2 = lookup.col2...)
2) sample data [match and mismatch]
3) which data you're returning - in most cases, the desired result is
the incoming data w/o corresponding lookup values. however, you're
initial approach sounds like you want lookups that aren't in the
incoming data. whichever, a cursor is indeed unnecessary and probably bad
w/o the above all you'll get is guesses, like this
-- use NOT EXISTS
select * -- list columns in real code
from incoming i
where not exists (
select *
from lookup
where col1 = i.col1
and col2 = i.col2
)
BenignVanilla wrote:
> I have a project I am working on that has two tables. One is a reference
> table, and the other is a table that stores incoming data from an outside
> vendor. The customer wants us to write a "solution" that will compare the
> incoming data table to the lookup table, and find records that don't match
.
> Any records in the incoming data table should have corresponding records i
n
> the lookup table, and specific columns need to match. The "solution" will
> create a list of records with anomolies.
> I had planned to write a cursor to march through the lookup table and chec
k
> for records in the incoming data table, but the customer feels cursors are
a
> bad idea, and does not want us to use them.
> Are there alternatives to cursors?
> BV.
>|||"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:Odbq1JkFGHA.516@.TK2MSFTNGP15.phx.gbl...
> Please post DDL along with
> 1) definition of matching rows (e.g., incoming.col1 = lookup.col1 and
> incoming.col2 = lookup.col2...)
> 2) sample data [match and mismatch]
> 3) which data you're returning - in most cases, the desired result is the
> incoming data w/o corresponding lookup values. however, you're initial
> approach sounds like you want lookups that aren't in the incoming data.
> whichever, a cursor is indeed unnecessary and probably bad
Trey, thanks for helping me clarify. Here goes...In the sample data below, I
have a lookup table with a list of people, and an Incoming table, with new
records. I need to be able to run a query whereby I search for either name,
address, or phone in the lookup table, and compare the records in the
Incoming table to ensure the incoming data is accurate.
Using the data below, let's assume I am searching by name. The results of
this run of the "solution" would return a hit on the Bob row, as the
incoming address does not match what is in the lookup table.
My plan was to cursor through the lookup table, fetching rows from the
incoming data table, and generate my report output into a temp table, then
return the contents of the temp table.
Does this help clarify my issue?
Lookup Table
Name Address Phone
Bob Maine 410-555-1212
Tom New Jersey 908-555-1234
Jane Delware 402-555-4392
Incoming Table
Name Address Phone
Bob Georgia 410-555-1212
Tom New Jersey 908-555-1234
Jane Delware 402-555-4392|||You're approaching the problem from a procedural perspective; try to
think relationally. You do not need a cursor for this; a simple SQL
join will do it for you.
SELECT inc.Name, inc.Address, inc.Phone
FROM Incoming inc LEFT JOIN Reference ref
ON inc.Name = ref.Name AND inc.Address = ref.Address AND inc.Phone =
ref.Phone
WHERE ref.Name is NULL
You can use NOT EXISTS as suggested by Trey above, and it may perform
faster; I'm just used to reading LEFT JOIN's.
HTH,
Stu|||"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1136951096.202363.67240@.z14g2000cwz.googlegroups.com...
> You're approaching the problem from a procedural perspective; try to
> think relationally. You do not need a cursor for this; a simple SQL
> join will do it for you.
> SELECT inc.Name, inc.Address, inc.Phone
> FROM Incoming inc LEFT JOIN Reference ref
> ON inc.Name = ref.Name AND inc.Address = ref.Address AND inc.Phone =
> ref.Phone
> WHERE ref.Name is NULL
> You can use NOT EXISTS as suggested by Trey above, and it may perform
> faster; I'm just used to reading LEFT JOIN's.
>
Stu, this was our option #2, I just wasn't sure it was a good idea from
performance angle, so I thought I'd avoid it. My concern is that the
customer wants to be able to do the comparison via different comparisons of
fields, by name, by address, etc. So I'll need to write different
statements, which is not a big deal, I am just worried that if one of these
fields is not indexed, and I am joining millions of rows to millions of
rows...what will that do to the server?
BV.|||Stu wrote:
> You're approaching the problem from a procedural perspective; try to
> think relationally. You do not need a cursor for this; a simple SQL
> join will do it for you.
> SELECT inc.Name, inc.Address, inc.Phone
> FROM Incoming inc LEFT JOIN Reference ref
> ON inc.Name = ref.Name AND inc.Address = ref.Address AND inc.Phone =
> ref.Phone
> WHERE ref.Name is NULL
> You can use NOT EXISTS as suggested by Trey above, and it may perform
> faster; I'm just used to reading LEFT JOIN's.
> HTH,
> Stu
instead of joining the tables and compare the cols it is sometimes
easieer (especially with many many columns to compare) to use the
checksum() functions of SQL Server.
you can do things like
select * from incoming x where checksum(name,address,phone) in (select
checksum(name,address,phone) from lookup)
to compare many many rows very easily. I dont know if this violates some
best practices or something, but checksum() helped me out very often.
hth
Gregor Stefka|||As long as you are joining on the key fields every time, performance should
not be an issue with this approach. I am assuming that the key fields have
to be equal before you begin looking at any other criteria. Adding extra
criteria should not prevent you from using indexes (assuming the extra
criteria does not include an additional table).
"BenignVanilla" <bvanilla@.tibetanbeefgarden.com> wrote in message
news:JOudnY6-w907uFjeRVn-sw@.giganews.com...
> "Stu" <stuart.ainsworth@.gmail.com> wrote in message
> news:1136951096.202363.67240@.z14g2000cwz.googlegroups.com...
> Stu, this was our option #2, I just wasn't sure it was a good idea from
> performance angle, so I thought I'd avoid it. My concern is that the
> customer wants to be able to do the comparison via different comparisons
of
> fields, by name, by address, etc. So I'll need to write different
> statements, which is not a big deal, I am just worried that if one of
these
> fields is not indexed, and I am joining millions of rows to millions of
> rows...what will that do to the server?
> BV.
>|||Performance tuning is always an issue, especially with a very large
database; however, a JOIN will ALWAYS perform better than a cursor.
I'm not saying that cursors don't have their place; this just ain't one
of them.
If you're worried about performance, be sure that your indexes are
onthe appropriate joining columns, and you may consider reducing your
isolation level (since it sounds as if you are reporting on batched
data, rather than live data).
Stu

Comparing two tables with different fields

I have two tables which only have one key field in common. I want to synchronise them in the sense that the key items from one table also end up in the second table. I dont know how to do this. This is the situation on the tables:

table1
Fruit | color | taste |
Apple | Red | sweet |
pear | brown | sour |
orange| orange | sweet|
kiwi | brown | sweet

table2
Fruit | Costprice | retailprice
Apple | 1 | 2
Pear | 2 | 4

Field1 is present in both tables but has more records in the first table than in the second. I want items in table1 that aren't present in table 2 to be added to table2. In other words, if more fruit is listed in table A I want it to be added to table 2. In this case, the orange and the kiwi should be added to the second table:

table2
Fruit | Costprice | retailprice
Apple | 1 | 2
Pear | 2 | 4
orange| NULL | NULL
kiwi | NULL | NULL

Con someone help me?insert into table2 (fruit)
select fruit from table1
where not exists (select 1 from table2 where fruit=table1.fruit)

or

insert into table2 (fruit)
select fruit from table1
where fruit not in (select fruit from table2)

Comparing two tables in two different databases

I want to compare the records in a table on my live database server with the same table that is my test database server. How can I do this?

Note that both databases are mirrors of each other but contain slightly different data and are on different servers.

There a couple of tools on the market like RedGate Data Compare.

The SQL Server 2005 program directory also contains a commandline utility called tablediff.exe which does just what you want.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Comparing two Tables in SQL Server 2000

Hi,
In my SQL Server 2000, I have 2 Tables TableA and TableB.
TableA and TableB have exactly same schema, and they all have 50
datacolumns.
However, the data in TableA and Tables are not the same, they are not copy
to each other.
Now comes my question, since they both have many columns, how do I compare
the data for the two tables?
Thanks for help.
JasonHello,
Take a look into :-
http://www.sql-server-performance.c...pare_review.asp
http://www.sqlteam.com/article/comparing-tables
http://weblogs.sqlteam.com/jeffs/ar...11/10/2737.aspx
THanks
Hari
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:Oxik5IjrHHA.4108@.TK2MSFTNGP06.phx.gbl...
> Hi,
> In my SQL Server 2000, I have 2 Tables TableA and TableB.
> TableA and TableB have exactly same schema, and they all have 50
> datacolumns.
> However, the data in TableA and Tables are not the same, they are not copy
> to each other.
> Now comes my question, since they both have many columns, how do I compare
> the data for the two tables?
> Thanks for help.
>
> Jason
>|||Jason,
SchemaCrawler can compare a schema against a reference schema.
SchemaCrawler outputs details of your schema (tables, views,
procedures, and more) in a diff-able plain-text format (text, CSV, or
XHTML). SchemaCrawler can also output data for comparison (including
CLOBs and BLOBs) in the same plain-text formats. You can use a
standard diff program to diff the current output with a reference
version of the output.
SchemaCrawler is free, open-source, cross-platform (operating system
and database) tool, written in Java, that is available at
SourceForge:
http://schemacrawler.sourceforge.net/
You will need to provide a JDBC driver for your database. No other
third-party libraries are required. A lot of examples are available
with the download to help you get started.
Sualeh Fatehi.|||On Jun 13, 11:15 pm, "Jason Huang" <JasonHuang8...@.hotmail.com> wrote:
> Hi,
> In mySQLServer2000, I have 2 Tables TableA and TableB.
> TableA and TableB have exactly same schema, and they all have 50
> datacolumns.
> However, the data in TableA and Tables are not the same, they are not copy
> to each other.
> Now comes my question, since they both have many columns, how do Icompare
> the data for the two tables?
> Thanks for help.
> Jason
Jason - check out xSQL Data Compare at http://www.xsqlsoftware.com -
it does exactly what you want and it is free (to be more accurate you
have the full edition free for 2 weeks and after the two week period
is over then the Lite edition is free forever).
Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com

Comparing two Tables in SQL Server 2000

Hi,
In my SQL Server 2000, I have 2 Tables TableA and TableB.
TableA and TableB have exactly same schema, and they all have 50
datacolumns.
However, the data in TableA and Tables are not the same, they are not copy
to each other.
Now comes my question, since they both have many columns, how do I compare
the data for the two tables?
Thanks for help.
Jason
Hello,
Take a look into :-
http://www.sql-server-performance.com/sql_data_compare_review.asp
http://www.sqlteam.com/article/comparing-tables
http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx
THanks
Hari
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:Oxik5IjrHHA.4108@.TK2MSFTNGP06.phx.gbl...
> Hi,
> In my SQL Server 2000, I have 2 Tables TableA and TableB.
> TableA and TableB have exactly same schema, and they all have 50
> datacolumns.
> However, the data in TableA and Tables are not the same, they are not copy
> to each other.
> Now comes my question, since they both have many columns, how do I compare
> the data for the two tables?
> Thanks for help.
>
> Jason
>
|||Jason,
SchemaCrawler can compare a schema against a reference schema.
SchemaCrawler outputs details of your schema (tables, views,
procedures, and more) in a diff-able plain-text format (text, CSV, or
XHTML). SchemaCrawler can also output data for comparison (including
CLOBs and BLOBs) in the same plain-text formats. You can use a
standard diff program to diff the current output with a reference
version of the output.
SchemaCrawler is free, open-source, cross-platform (operating system
and database) tool, written in Java, that is available at
SourceForge:
http://schemacrawler.sourceforge.net/
You will need to provide a JDBC driver for your database. No other
third-party libraries are required. A lot of examples are available
with the download to help you get started.
Sualeh Fatehi.
|||On Jun 13, 11:15 pm, "Jason Huang" <JasonHuang8...@.hotmail.com> wrote:
> Hi,
> In mySQLServer2000, I have 2 Tables TableA and TableB.
> TableA and TableB have exactly same schema, and they all have 50
> datacolumns.
> However, the data in TableA and Tables are not the same, they are not copy
> to each other.
> Now comes my question, since they both have many columns, how do Icompare
> the data for the two tables?
> Thanks for help.
> Jason
Jason - check out xSQL Data Compare at http://www.xsqlsoftware.com -
it does exactly what you want and it is free (to be more accurate you
have the full edition free for 2 weeks and after the two week period
is over then the Lite edition is free forever).
Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com

Comparing two Tables in SQL Server 2000

Hi,
In my SQL Server 2000, I have 2 Tables TableA and TableB.
TableA and TableB have exactly same schema, and they all have 50
datacolumns.
However, the data in TableA and Tables are not the same, they are not copy
to each other.
Now comes my question, since they both have many columns, how do I compare
the data for the two tables?
Thanks for help.
JasonHello,
Take a look into :-
http://www.sql-server-performance.com/sql_data_compare_review.asp
http://www.sqlteam.com/article/comparing-tables
http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx
THanks
Hari
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:Oxik5IjrHHA.4108@.TK2MSFTNGP06.phx.gbl...
> Hi,
> In my SQL Server 2000, I have 2 Tables TableA and TableB.
> TableA and TableB have exactly same schema, and they all have 50
> datacolumns.
> However, the data in TableA and Tables are not the same, they are not copy
> to each other.
> Now comes my question, since they both have many columns, how do I compare
> the data for the two tables?
> Thanks for help.
>
> Jason
>|||Jason,
SchemaCrawler can compare a schema against a reference schema.
SchemaCrawler outputs details of your schema (tables, views,
procedures, and more) in a diff-able plain-text format (text, CSV, or
XHTML). SchemaCrawler can also output data for comparison (including
CLOBs and BLOBs) in the same plain-text formats. You can use a
standard diff program to diff the current output with a reference
version of the output.
SchemaCrawler is free, open-source, cross-platform (operating system
and database) tool, written in Java, that is available at
SourceForge:
http://schemacrawler.sourceforge.net/
You will need to provide a JDBC driver for your database. No other
third-party libraries are required. A lot of examples are available
with the download to help you get started.
Sualeh Fatehi.|||On Jun 13, 11:15 pm, "Jason Huang" <JasonHuang8...@.hotmail.com> wrote:
> Hi,
> In mySQLServer2000, I have 2 Tables TableA and TableB.
> TableA and TableB have exactly same schema, and they all have 50
> datacolumns.
> However, the data in TableA and Tables are not the same, they are not copy
> to each other.
> Now comes my question, since they both have many columns, how do Icompare
> the data for the two tables?
> Thanks for help.
> Jason
Jason - check out xSQL Data Compare at http://www.xsqlsoftware.com -
it does exactly what you want and it is free (to be more accurate you
have the full edition free for 2 weeks and after the two week period
is over then the Lite edition is free forever).
Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com

Comparing two tables and spitting out an XLS

Hi,

I'm trying to figure out a way to compare two tables, table one has more
entries than table two, I want SQL to compare table one to table two and
spit out and XLS of the enries that exist in table one, but not in table
two.

So far I can't even get my query right...heh

select * from table1 a
left join tabl2 b on a.column=b.column
where a.column exists not b.column

am I missing an "in" in the select portion on my query?

thanks alot for any help.Your name (fake.email@.address.com) writes:

Quote:

Originally Posted by

I'm trying to figure out a way to compare two tables, table one has more
entries than table two, I want SQL to compare table one to table two and
spit out and XLS of the enries that exist in table one, but not in table
two.

SELECT a.*
FROM a
WHERE NOT EXISTS (SELECT *
FROM b
WHERE a.keycol = b.keycol)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Comparing two tables

IS there an easy way to compare two tables in sqlserver? I only need to display items that are different between table a and b.
thanks in advance for your help!One quick way is to use EM to script each table then compare both files.|||I was referring to the data in the tables. Sorry for the confusion.|||If you know perl, you should do it with a script like that:

#!C:/Perl/bin/perl.exe
# SYNTAXE : perl script.pl server db user pwd

use DBI;

my @.param = @.ARGV;
my $server = $param[0];
my $database = $param[1];
my $user = $param[2];
my $password = $param[3];

my $dsn = "Driver={SQL Server};Server=$server;Database=$database;Uid=$use r;Pwd=$password;" ;
my $dbh = DBI->connect("dbi:ADO:$dsn") or die "Impossible connection: $DBI::errstr";

my $sth1 = $dbh->prepare( q{
SELECT * FROM table1
}) or die "Can't prepare statement: $DBI::errstr";

my $sth2 = $dbh->prepare( q{
SELECT * FROM table2
}) or die "Can't prepare statement: $DBI::errstr";

my $rc1 = $sth1->execute
or die "Can't execute statement: $DBI::errstr";
my $rc2 = $sth1->execute
or die "Can't execute statement: $DBI::errstr";

while ( @.row1 = $sth1->fetchrow_array and @.row2 = $sth2->fetchrow_array )
{
# do your tests here
}

$rc = $dbh->disconnect;|||Sorry, I don't know pearl. Is there anything on sqlserver?|||Don't see what, except a sql script that you have to write (like the one in perl) :)|||IS there an easy way to compare two tables in sqlserver? I only need to display items that are different between table a and b.

thanks in advance for your help!What exactly do you mean by "items"? Do your tables have Primary Keys (if not, this problems gets incredibly ugly)? What exactly do you want displayed when a difference is found?

The best bet might be to compose a sample set of data that contains a pair of 5 row tables with one row "missing" in each of them, and the output that you'd like to get from comparing them.

-PatP|||You mean like

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1) PRIMARY KEY, Col2 char(1))
CREATE TABLE myTable00(Col1 int IDENTITY(1,1) PRIMARY KEY, Col2 char(1))
GO

INSERT INTO myTable99(Col2)
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'c' UNION ALL
SELECT 'd'

INSERT INTO myTable00(Col2)
SELECT 'a' UNION ALL
SELECT 'x' UNION ALL
SELECT 'c' UNION ALL
SELECT 'z'
GO

SELECT a.Col1, a.Col2, b.Col2
FROM myTable99 a
INNER JOIN myTable00 b
ON a.Col1 = b.Col1
WHERE a.Col2 <> b.Col2
GO

SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myTable00
GO|||Try this:
http://www.sqlservercentral.com/scripts/contributions/596.asp|||Ummm ... isn't it much easier than that? How about

SELECT value1, value2, value3, value4, value5
FROM table1
WHERE value1 NOT IN (SELECT value1 FROM table2)

Comparing two tables

Hi,
I have two identical tables in a database, the only difference between
to two is the data that is in each table. Is there a way of comparing
the two tables together in a query and listing the differences that are
in table one? I have been trying to do this with joins but without much
success.
Any help would be much appreciated.
Thanks
SimonLook up OUTER JOIN in Books Online.
For a more accurate solution, please post DDL, sample data and expected
results.
ML
http://milambda.blogspot.com/|||Take a look at this
http://sqlservercode.blogspot.com/2...-price-tag.html
you basically join on the key field and do a BINARY_CHECKSUM
Denis the SQL Menace
http://sqlservercode.blogspot.com/
accyboy1981 wrote:
> Hi,
> I have two identical tables in a database, the only difference between
> to two is the data that is in each table. Is there a way of comparing
> the two tables together in a query and listing the differences that are
> in table one? I have been trying to do this with joins but without much
> success.
> Any help would be much appreciated.
> Thanks
> Simon|||accyboy1981 wrote:

> Hi,
> I have two identical tables in a database, the only difference between
> to two is the data that is in each table. Is there a way of comparing
> the two tables together in a query and listing the differences that are
> in table one? I have been trying to do this with joins but without much
> success.
> Any help would be much appreciated.
> Thanks
> Simon
SQL Server 2005 has new utiltity tablediff which you can find at
C:\Program Files\Microsoft SQL Server\90\COM>
It is used in replication to find change data between tables on
publisher and subscribter , but we can also use it if we want to
compare tables with data.
Also look at except operator in SQL Server 2005
Regards
Amish shah

Comparing two tables

I have two tables of "trade" data, one comes from out accounting
system, the other from text files sent to us by the counterparties
(brokers). I attacked the problem by creating two views that map the
data into a common format. I then used both of these in a UNION
query...
SELECT *
FROM vCounterpartyTrades AS c LEFT JOIN vOurTrades AS p
ON c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
c.custodianId = p.custodianId
WHERE p.quantity IS NULL OR c.quantity <> p.quantity
UNION
SELECT *
FROM vCounterpartyTrades AS c RIGHT JOIN vOurTrades AS p
ON c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
c.custodianId = p.custodianId
WHERE p.quantity <> 0 AND (c.quantity IS NULL OR c.quantity = 0)
The problem is that the UNION causes a temp table to be created. When
I try to add filters to reduce the number of rows that are returned,
the filter is applied to the temp table, not the views, so every
single query takes the same amount of time -- about 20 seconds, and
that's simply too long.
In the past I have had excellent results by replacing views with the
SQL that makes the view. So in this case, "vCounterpartyTrades as c"
is replaced by "( all of the SQL in that view ) as c". That way I can
apply any WHERE filters directly in the SQL by splicing in the WHERE
in VBA, and everything gets a lot faster.
But the problem here is that both views appear twice, on either side
of the UNION. That not only dramatically expands the resulting SQL
(both the views are pretty long as it is) but means both are run
twice. So to really speed this up I think I need to remove the UNION
and replace it with some sort of join...
SELECT *
FROM *a whole bunch of SQL* AS c, *another whole bunch of SQL* AS p
WHERE c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
c.custodianId = p.custodianId
AND (p.quantity IS NOT null AND (c.quantity IS NULL OR c.quantity =
0)
OR ( p.quantity IS NULL OR c.quantity <> p.quantity))
...is the obvious solution, but this eliminates anything where one
side or the other is null. I know there's a simple solution to this
using other join styles, but I have very little familiarity with them.
Can someone help me out?
Maury
p.s. Is anyone else having problems with MS's interface to these
groups? I can no longer post via their web site, because the post form
will not pop up.
> But the problem here is that both views appear twice, on either side
> of the UNION. That not only dramatically expands the resulting SQL
> (both the views are pretty long as it is) but means both are run
> twice. So to really speed this up I think I need to remove the UNION
> and replace it with some sort of join...
I don't fully understand your requirements but is it possible to use UNION
ALL instead of just UNION? I would expect that to reduce the amount of work
that needs to be performed. Another option is to use a FULL JOIN instead of
the UNION of the LEFT/RIGHT JOINs. Maybe something like:
SELECT *
FROM vCounterpartyTrades AS c
FULL JOIN vOurTrades AS p ON
c.portfolioId = p.portfolioId AND
c.cusip = p.cusip AND
c.custodianId = p.custodianId
WHERE
p.quantity IS NULL OR
c.quantity <> p.quantity OR
(p.quantity <> 0 AND
(c.quantity IS NULL OR c.quantity = 0)
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Maury Markowitz" <maury.markowitz@.gmail.com> wrote in message
news:a7f0ed7f-982e-4c41-9123-671e1958e37b@.f10g2000hsf.googlegroups.com...
>I have two tables of "trade" data, one comes from out accounting
> system, the other from text files sent to us by the counterparties
> (brokers). I attacked the problem by creating two views that map the
> data into a common format. I then used both of these in a UNION
> query...
> SELECT *
> FROM vCounterpartyTrades AS c LEFT JOIN vOurTrades AS p
> ON c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
> c.custodianId = p.custodianId
> WHERE p.quantity IS NULL OR c.quantity <> p.quantity
> UNION
> SELECT *
> FROM vCounterpartyTrades AS c RIGHT JOIN vOurTrades AS p
> ON c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
> c.custodianId = p.custodianId
> WHERE p.quantity <> 0 AND (c.quantity IS NULL OR c.quantity = 0)
> The problem is that the UNION causes a temp table to be created. When
> I try to add filters to reduce the number of rows that are returned,
> the filter is applied to the temp table, not the views, so every
> single query takes the same amount of time -- about 20 seconds, and
> that's simply too long.
> In the past I have had excellent results by replacing views with the
> SQL that makes the view. So in this case, "vCounterpartyTrades as c"
> is replaced by "( all of the SQL in that view ) as c". That way I can
> apply any WHERE filters directly in the SQL by splicing in the WHERE
> in VBA, and everything gets a lot faster.
> But the problem here is that both views appear twice, on either side
> of the UNION. That not only dramatically expands the resulting SQL
> (both the views are pretty long as it is) but means both are run
> twice. So to really speed this up I think I need to remove the UNION
> and replace it with some sort of join...
> SELECT *
> FROM *a whole bunch of SQL* AS c, *another whole bunch of SQL* AS p
> WHERE c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
> c.custodianId = p.custodianId
> AND (p.quantity IS NOT null AND (c.quantity IS NULL OR c.quantity =
> 0)
> OR ( p.quantity IS NULL OR c.quantity <> p.quantity))
> ...is the obvious solution, but this eliminates anything where one
> side or the other is null. I know there's a simple solution to this
> using other join styles, but I have very little familiarity with them.
> Can someone help me out?
> Maury
> p.s. Is anyone else having problems with MS's interface to these
> groups? I can no longer post via their web site, because the post form
> will not pop up.

Comparing two tables

I have two tables of "trade" data, one comes from out accounting
system, the other from text files sent to us by the counterparties
(brokers). I attacked the problem by creating two views that map the
data into a common format. I then used both of these in a UNION
query...
SELECT *
FROM vCounterpartyTrades AS c LEFT JOIN vOurTrades AS p
ON c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
c.custodianId = p.custodianId
WHERE p.quantity IS NULL OR c.quantity <> p.quantity
UNION
SELECT *
FROM vCounterpartyTrades AS c RIGHT JOIN vOurTrades AS p
ON c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
c.custodianId = p.custodianId
WHERE p.quantity <> 0 AND (c.quantity IS NULL OR c.quantity = 0)
The problem is that the UNION causes a temp table to be created. When
I try to add filters to reduce the number of rows that are returned,
the filter is applied to the temp table, not the views, so every
single query takes the same amount of time -- about 20 seconds, and
that's simply too long.
In the past I have had excellent results by replacing views with the
SQL that makes the view. So in this case, "vCounterpartyTrades as c"
is replaced by "( all of the SQL in that view ) as c". That way I can
apply any WHERE filters directly in the SQL by splicing in the WHERE
in VBA, and everything gets a lot faster.
But the problem here is that both views appear twice, on either side
of the UNION. That not only dramatically expands the resulting SQL
(both the views are pretty long as it is) but means both are run
twice. So to really speed this up I think I need to remove the UNION
and replace it with some sort of join...
SELECT *
FROM *a whole bunch of SQL* AS c, *another whole bunch of SQL* AS p
WHERE c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
c.custodianId = p.custodianId
AND (p.quantity IS NOT null AND (c.quantity IS NULL OR c.quantity = 0)
OR ( p.quantity IS NULL OR c.quantity <> p.quantity))
...is the obvious solution, but this eliminates anything where one
side or the other is null. I know there's a simple solution to this
using other join styles, but I have very little familiarity with them.
Can someone help me out?
Maury
p.s. Is anyone else having problems with MS's interface to these
groups? I can no longer post via their web site, because the post form
will not pop up.> But the problem here is that both views appear twice, on either side
> of the UNION. That not only dramatically expands the resulting SQL
> (both the views are pretty long as it is) but means both are run
> twice. So to really speed this up I think I need to remove the UNION
> and replace it with some sort of join...
I don't fully understand your requirements but is it possible to use UNION
ALL instead of just UNION? I would expect that to reduce the amount of work
that needs to be performed. Another option is to use a FULL JOIN instead of
the UNION of the LEFT/RIGHT JOINs. Maybe something like:
SELECT *
FROM vCounterpartyTrades AS c
FULL JOIN vOurTrades AS p ON
c.portfolioId = p.portfolioId AND
c.cusip = p.cusip AND
c.custodianId = p.custodianId
WHERE
p.quantity IS NULL OR
c.quantity <> p.quantity OR
(p.quantity <> 0 AND
(c.quantity IS NULL OR c.quantity = 0)
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Maury Markowitz" <maury.markowitz@.gmail.com> wrote in message
news:a7f0ed7f-982e-4c41-9123-671e1958e37b@.f10g2000hsf.googlegroups.com...
>I have two tables of "trade" data, one comes from out accounting
> system, the other from text files sent to us by the counterparties
> (brokers). I attacked the problem by creating two views that map the
> data into a common format. I then used both of these in a UNION
> query...
> SELECT *
> FROM vCounterpartyTrades AS c LEFT JOIN vOurTrades AS p
> ON c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
> c.custodianId = p.custodianId
> WHERE p.quantity IS NULL OR c.quantity <> p.quantity
> UNION
> SELECT *
> FROM vCounterpartyTrades AS c RIGHT JOIN vOurTrades AS p
> ON c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
> c.custodianId = p.custodianId
> WHERE p.quantity <> 0 AND (c.quantity IS NULL OR c.quantity = 0)
> The problem is that the UNION causes a temp table to be created. When
> I try to add filters to reduce the number of rows that are returned,
> the filter is applied to the temp table, not the views, so every
> single query takes the same amount of time -- about 20 seconds, and
> that's simply too long.
> In the past I have had excellent results by replacing views with the
> SQL that makes the view. So in this case, "vCounterpartyTrades as c"
> is replaced by "( all of the SQL in that view ) as c". That way I can
> apply any WHERE filters directly in the SQL by splicing in the WHERE
> in VBA, and everything gets a lot faster.
> But the problem here is that both views appear twice, on either side
> of the UNION. That not only dramatically expands the resulting SQL
> (both the views are pretty long as it is) but means both are run
> twice. So to really speed this up I think I need to remove the UNION
> and replace it with some sort of join...
> SELECT *
> FROM *a whole bunch of SQL* AS c, *another whole bunch of SQL* AS p
> WHERE c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
> c.custodianId = p.custodianId
> AND (p.quantity IS NOT null AND (c.quantity IS NULL OR c.quantity => 0)
> OR ( p.quantity IS NULL OR c.quantity <> p.quantity))
> ...is the obvious solution, but this eliminates anything where one
> side or the other is null. I know there's a simple solution to this
> using other join styles, but I have very little familiarity with them.
> Can someone help me out?
> Maury
> p.s. Is anyone else having problems with MS's interface to these
> groups? I can no longer post via their web site, because the post form
> will not pop up.|||Ok, I actually found a great guide on all of this and got it working.
The key was to use "FULL OUTER JOIN" in the FROM. This makes all rows
from either side appear. After that there was a lot of tweaking of the
WHERE, but I did manage to get it working in the end. I still have to
use the two subqueries, but by moving the filters into their WHEREs
the performance is excellent, basically instant.
Maury

Comparing two strings inside a stored procedure

Basically I have two strings. Both strings will contain similar data because the 2nd string is the first string after an update of the first string takes place. Both strings are returned in my Stored Procedure

For example:
String1 = "Here is some data. lets type some more data"
String2 = "Here's some data. Lets type some data here"

I would want to change string2 (inside my Stored Procedure) to show the changed/added text highlighted and the deleted text with a strike though.

So I would want string2 to look like this

string2 = "Here<font color = \"#00FF00\">'s</font> <strike>is</strike> some data. <font color = \"#00FF00\">L</font>ets type some <strike>more</strike> data <font color = \"#00FF00\">here</font>"

Is there an way to accomplish this inside a stored procedure?

First, you'll have to decide what algorithm determines what matches and what is different. For example, if you just compare the strings by the same position, you'll get the first part the same, the end of the first string as deleted and the end of the second string as inserted. I don't know how to implement the algorithm but I can think of some possibilities:

1. Regular expressions may have some support for this.

2. Track the user's changes character by character on the client.

3. Find a differencing algorithm / tool somewhere.

Once you've solved that, I suggest you pass the difference string to your procedure. You'll have much more flexibility and support in .NET than in SQL.

Sorry I couldn't help more. Good Luck.

|||Yes, you can. However, that is best left up to an application as it's a matter of presentation and application logic.

comparing two string columns and show difference

Hello,

I am having this problem. I have a table to log any changes to the configuration of a program. If something changes, it gets recorded in this table.

So, I have two columns: BeforeChange, AfterChange. It looks like this:

BeforeChange AfterChange
------------------------
Amount = 30.00, Quantity = 3 Amount=30.00, Quantity=2

When I query those changes, I want to show only the changed values.
So, it will show only:

ChangeMade
------
Quantity = 2

Can anyone help me on this?

Thanks in advance.What about to normalize your table - problem will be resolved by itself.|||Originally posted by snail
What about to normalize your table - problem will be resolved by itself.

The problem is that, this table is filled by a program that I have no access to. (Honestly, I don't really know about the program at all)

I can only query the modificationlog table, and I have to make a report of any changes made with stored procedure.

If you can enlighten me a bit more, I would really appreciate it. I'm new to this MS SQL)

Thank you...

Comparing two SSIS Packages?

I'd like to know how people out there are comparing ("diffing") their DTSX files.

Using XML file compare doesn't seem to work, because the package XML appears to be arbitrarily ordered and reordered by the designer in Visual Studio.

ApexSQL is supposedly planning to have a tool available in Q3, but even their product page does not seem too hopeful: "Compare and Document SSIS Packages (may be released with ApexSQL Doc and ApexSQL Diff in Q3)"

I remember seeing a CodePlex project that was hoping to address this and other issues, but it was in its infancy, and I can't seem to locate it today.

I'm thinking of starting to write my own package compare tool that will work with the SSIS .NET API, but this seems like it's going to be a ridiculous amount of work and an exercise in pain, and it seems unlikely that I will make the time to target anything more than the specific portions of the package model that I really need.

So if anyone out there has words of wisdom to share, I'd love to hear it. And if (dare I get my hopes up?) have a URL to share, I'll buy you a beer at TechEd in Orlando week after next.

Thanks in advance!

No words of wisdom to share, but I agree on the need for this kind of tool. We've been looking into rolling our own tools as well, but creating anything of general applicability would be a major project.|||

I don't know if there is a clever way of comparing packages. Perhaps you should open a suggestion in SQL Server connect site

http://connect.microsoft.com/site/sitehome.aspx?SiteID=68

[Microsoft follow-up]

|||

Thanks for the feedback, Rich and Rafael. I'll definitely post this as a suggestion in Connect, but...

Is no one out there (or more specifically, no one here on the SSIS forums) doing anything to compare or diff SSIS packages? I honestly wasn't expecting anyone to say "oh yeah, I use XXX tool, it does a great job" but I was expecting something...

To take this thread in a somewhat different direction, if I were to start a CodePlex project with the aim of developing a package diff utility based on the SSIS object model, would anyone else be interested in contributing? I'll bet I'm not the only SSIS ETL guy out there with a C# background...

|||

Rather than starting a new project, would you be interested in rolling this into BIDSHelper? http://www.codeplex.com/bidshelper

Sounds like the type of functionality we are interested in. Though I think having this available outside of BIDS as well would be handy.

|||

jwelch wrote:

Rather than starting a new project, would you be interested in rolling this into BIDSHelper? http://www.codeplex.com/bidshelper

Sounds like the type of functionality we are interested in. Though I think having this available outside of BIDS as well would be handy.

Thank you!

This was the CodePlex project I mentioned in my original post - I stumbled across it once, but could not find it again when I started this thread here.

|||

jwelch wrote:

Rather than starting a new project, would you be interested in rolling this into BIDSHelper? http://www.codeplex.com/bidshelper

Sounds like the type of functionality we are interested in. Though I think having this available outside of BIDS as well would be handy.

So... what are you using to compare packages, today?

|||

I try not to

Usually I use a text difference tool and deal with the headaches of elements being in different order.

|||

jwelch wrote:

I try not to

Usually I use a text difference tool and deal with the headaches of elements being in different order.

Well, it's good to know that great minds do indeed think alike.

|||

Can I just add my voice to the need for such a tool. I still use DTS Compare for our 2000 installations and something similar for SSIS would be of great use.