Comparing two rows in a Table

Hi Gurus,
Is there any automated way to compare the contents of TWO rows in the same
table of SQL Server ?
Thanks in advance.
Regards,
YogWhat do mean by "Automated"? There is certainly a way, just write a query
that returns a boolean 1/0 based on whether the row column values are the
same or different... How to "Automate" it, depends on what "event" you want
to "trigger", (cause ) it to run... If yo want it to run automatically on
some time interval, use SQLAgent...
If you want it to run whenever someone inserts, updates, or deletes a record
from the table, then use a trugger.
"Yog" wrote:

> Hi Gurus,
> Is there any automated way to compare the contents of TWO rows in the same
> table of SQL Server ?
> Thanks in advance.
> Regards,
> Yog|||What result do you want from the comparison? Do you mean you want to
compart two particular rows? In that case you can use a self-join on
the columns you want to compare.
Maybe you just want to find duplicates, in which case:
SELECT col1, col2, col3, ...
FROM YourTable
GROUP BY col1, col2, col3, ...
HAVING COUNT(*)>1
David Portas
SQL Server MVP
--|||Yog wrote:
> Hi Gurus,
> Is there any automated way to compare the contents of TWO rows in the
> same table of SQL Server ?
> Thanks in advance.
> Regards,
> Yog
You can use the CHECKSUM() function as in:
Create table #checktest(col1 int, col2 int, col3 int)
insert into #checktest values (1, 2, 2)
insert into #checktest values (2, 2, 2)
insert into #checktest values (3, 2, 3)
Select CHECKSUM(col2, col3) as "ID 1"
From #checktest
where col1 = 1
UNION ALL
Select CHECKSUM(col2, col3) as "ID 2"
From #checktest
where col1 = 2
UNION ALL
Select CHECKSUM(col2, col3) as "ID 3"
From #checktest
where col1 = 3
drop table #checktest
David Gugick
Imceda Software
www.imceda.com|||To precisely put it , the content of the problem is as below
Objective :- Compare TWO rows contents for few selected Columns. If they are
different then show those columns with the contents.
ENV:- SQL Server 2000 , T-SQL, SQL Server Tools (No other programming
language or front end)
"CBretana" wrote:
> What do mean by "Automated"? There is certainly a way, just write a quer
y
> that returns a boolean 1/0 based on whether the row column values are the
> same or different... How to "Automate" it, depends on what "event" you wa
nt
> to "trigger", (cause ) it to run... If yo want it to run automatically on
> some time interval, use SQLAgent...
> If you want it to run whenever someone inserts, updates, or deletes a reco
rd
> from the table, then use a trugger.
> "Yog" wrote:
>|||SELECT A.col1, A.col2, ...
FROM YourTable AS A,
YourTable AS B
WHERE A.x = ?
AND B.x = ? /* Specify the two rows to be compared */
AND
(A.col1<>B.col1
OR A.col1<>B.col1
OR ...)
David Portas
SQL Server MVP
--|||Hi David,
Many thanks for your POST.
Your answer provided me HINT to achieve my goal of comparing two rows for my
requirement.
Regards,
Yog
"David Portas" wrote:

> SELECT A.col1, A.col2, ...
> FROM YourTable AS A,
> YourTable AS B
> WHERE A.x = ?
> AND B.x = ? /* Specify the two rows to be compared */
> AND
> (A.col1<>B.col1
> OR A.col1<>B.col1
> OR ...)
> --
> David Portas
> SQL Server MVP
> --
>

Comparing two rows in a Table

Hi Gurus,
Is there any automated way to compare the contents of TWO rows in the same
table of SQL Server ?
Thanks in advance.
Regards,
Yog
On Thu, 17 Mar 2005 10:19:02 -0800, Yog wrote:

>Hi Gurus,
>Is there any automated way to compare the contents of TWO rows in the same
>table of SQL Server ?
Hi Yog,
The answer is probably yes - but you've given too little information
about your needs to allow for any more specific information.
Please check out www.aspfaq.com/5006to find out what information is
needed (and in what form) to allow others to understand your problem and
proposes solutions.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Well,
To precisely put it , the content of the problem is as below
Objective :- Compare TWO rows contents for few selected Columns. If they are
different then show those columns with the contents.
ENV:- SQL Server 2000 , T-SQL, SQL Server Tools (No other programming
language or front end)
Hope this makes sence.
"Hugo Kornelis" wrote:

> On Thu, 17 Mar 2005 10:19:02 -0800, Yog wrote:
>
> Hi Yog,
> The answer is probably yes - but you've given too little information
> about your needs to allow for any more specific information.
> Please check out www.aspfaq.com/5006to find out what information is
> needed (and in what form) to allow others to understand your problem and
> proposes solutions.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 17 Mar 2005 22:27:02 -0800, Yog wrote:

>Well,
>To precisely put it , the content of the problem is as below
>Objective :- Compare TWO rows contents for few selected Columns. If they are
>different then show those columns with the contents.
>ENV:- SQL Server 2000 , T-SQL, SQL Server Tools (No other programming
>language or front end)
>Hope this makes sence.
Hi Yog,
Yes - but not enough to provide actual help. I need your table structure
(posted as CREATE TABLE stmt), some sample data (as INSERT stmts) and
the expected output.
I see that the link I posted got mangled - I missed a space between the
link and the next word. Here's the link again:
www.aspfaq.com/5006
Please follow that link and read the information there; then post the
required information here for further help.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

comparing two integers and returning a third

I am having difficulty trying to figure out how to compare two integers stored in a table to return a third. I have two integer fields in one table and two in another like this:

Table1.SomeNumber1 = 1

Table1.SomeNumber2 = 2

Table2.SomeNumber1 = 2

Table2.SomeNumber2 = 1

I need to be able to compare the first number from the first table to the first number in the second table. If the values are different I need to set a variable or field to 0. If the numbers are the same I need to set my variable or field to 1.

I need to follow the same procedure comparing the second number in the first table to the second number in the second table. In addition, I need to be able to do it in a single select statement.

Does anyone have any ideas on how this could be done? Thank you for any help you may be able to provide.

Gmz

CREATE TABLE [dbo].[num1](
[id] [int] NULL,
[number1] [int] NULL,
[number2] [int] NULL,
[flag1] [int] NULL,
[flag2] [int] NULL
)
CREATE TABLE [dbo].[num2](
[id] [int] NULL,
[number1] [int] NULL,
[number2] [int] NULL
)
--After the comparison, two flag fields in table num1 are updated
UPDATE num1
SET flag1=(SELECT (CASE WHEN a.number1 = b.number1 THEN 1 ELSE 0 END) FROM num1 a INNER JOIN
num2 b ON a.id = b.id and a.id=c.id), flag2=(SELECT (CASE WHEN a.number2 = b.number2 THEN 1 ELSE 0 END) FROM num1 a INNER JOIN
num2 b ON a.id = b.id and a.id=c.id)
FROM num1 c|||

I took what you posted and applied it to just return the values of the comparisons as results of my query which saved me from having to store the extra data in the table. It worked great.

Thank you!!!!

GMZ

sqlsql

comparing two fields

Greetings.

I am trying to write a sql that identifies all the rows in one table that do not match another. The sql needs to compare the values in two columns of each table. In the past, I've used a where not exists and a subquery to identify missing rows.

select a.block_code

from BLKPTS_ADDXY a

where not exists

(select block_code

from allblkzips as s

where s.block_code = a.block_code)

I need to add a second column from each table (zip) to the query. For some reason, it doesn't seem as straightforward as I thought it should be. Suggestions are appreciated.

Thanks.

alan

If you are using SQL 2005, try a EXCEPT JOIN. Something like:

SELECT Block_Code, Zip
FROM BLKPTS_ADDXY
EXCEPT
SELECT Block_Code, Zip
FROM allblkzips

|||I usually use a not in clause

I m pretty sure this will work..you can concat the two columns thus,

select convert(varchar(10),a.block_code)+convert(varchar(10),a.second_code), <anything else you want to select>

from BLKPTS_ADDXY a

where convert(varchar(10),a.block_code)+convert(varchar(10),a.second_code) not in

(

selectconvert(varchar(10),s.block_code)+convert(varchar(10),s.second_code)

from allblkzips as s

)

This will probably run faster than the query you are using which tries to run a where condition in an inner query with a field in the outer query, comparable with a for followed by another v/s a nested for loop in procedural programming.|||

Do either of the queries below return the results you are expecting?

Chris

SELECT a.block_code

FROM BLKPTS_ADDXY a

WHERE NOT EXISTS

(SELECT block_code

FROM allblkzips s

WHERE s.block_code = a.block_code

AND s.zip = a.zip)

SELECT a.block_code

FROM BLKPTS_ADDXY a

WHERE NOT EXISTS

(SELECT block_code

FROM allblkzips s

WHERE s.block_code = a.block_code)

OR NOT EXISTS

(SELECT block_code

FROM allblkzips s

WHERE s.zip = a.zip)

|||

I prefer the "outer join" method, since it helps enforce thinking in terms of INNER and OUTER JOINs.

select

a.block_code

from BLKPTS_ADDXY a

left outer join allblkzips s

on a.block_code = s.block_code and

a.zip = s.zip

where (s.zip is null) -- you only need to specify one column here, since the JOIN fails if both do not match

Dan

Comparing two date periods for overlapping

hi guys,

i have a booking table which has the following columns...

booking
--------------
dCheckin (format 11/9/2006 12:00:00 AM)
dCheckout (format 11/11/2006 12:00:00 AM)

when a new booking is entered, we want to make sure that the period entered does not conflict with an existing record.

not sure how to go about building the query required. any help would be greatly appreciated.

mikeDo periods that "touch" count as an overlap? Do you need to consider rooms, customers, or anything else for an overlap, or is all of the data in the table the same?

-PatP|||it does matter if they are touching eg. someone cannot checkin during a period already occupied. the other data is in another table.

m.|||ahh sorry patp. i see what you mean. touching yes it does matter. a person cannot checkin on the day someone checks out.

mike|||CREATE TABLE #patp (
id INT IDENTITY
, dCheckin DATETIME
, dCheckout DATETIME
)

INSERT INTO #patp (
dCheckin, dCheckout
) SELECT '2006-01-01', '2006-01-10'
UNION ALL SELECT '2006-01-15', '2006-01-20'
UNION ALL SELECT '2006-02-01', '2006-02-10'
UNION ALL SELECT '2006-02-10', '2006-02-15'
UNION ALL SELECT '2006-03-01', '2006-03-10'
UNION ALL SELECT '2006-03-02', '2006-03-07'
UNION ALL SELECT '2006-04-01', '2006-04-10'
UNION ALL SELECT '2006-04-08', '2006-04-13'

SELECT *
FROM #patp AS a
WHERE EXISTS (SELECT *
FROM #patp AS b
WHERE b.id != a.id -- Never compare a row with itself
AND (b.dCheckin <= a.dCheckout -- A starts before B ends
AND a.dCheckin <= b.dCheckout)) -- B ends after A starts
ORDER BY a.dCheckin

DROP TABLE #patp-PatP

Comparing two databases

Hi,
I would like to compare two sql server databases.
I need to find the difference in the two database(i.e table, colums,
data type)
Is there any free utility available to compare sql server database
I'm using sql server 2000.
Help me out to compare databases

Thanks & Regards,
ManiIt's not free, but you can download a 14-day trial version;
http://www.redgate.com/products/SQL_Compare/index.htm
Markus|||Another one is AlfaAlfa's SQL Server Comparison Tool (SCT)

http://www.sql-server-tool.com
- also not free, but it has 30 days of free evaluation period, which
can be extended to 90 days. Comparisons can be fully automated
through the usage of command line parameters.

SCT works with SQL Server 2005, 2000 and 7.0 and between these
versions.

Dariusz Dziewialtowski.

Comparing two data sets betweeen two tables

I am using Sql Server 7 and here is what I am wanting to do. I want to
be able to compare a data set from table A to see a exact match of data
set exist in table B, if it does not exist then I want to go ahead and
add the data set to tableB.
Here is an example I want check and see if sets of rows from table A =
sets of rows from TableB. If they do not match I would then go ahead
and add that set from tableA to tableB.
In my example the set in tableA is grouped by COL1 and COL2 and the set
in tableB is grouped by COLB and COLC.
So in this example 10-A,10-B do not exist in TableB. So I should be
able to add it to Table B.
TableA
COL1 COL2
10 A
10 B
30 X
30 Y
TableB
COLA COLB COLC
1 10 A
1 10 B
1 10 C
2 30 X
2 30 Y
Any help in this regard will be greatly appreciated. I am trying to
avoid cursors to achieve this.
Thanks
ShubINSERT INTO TableB (colb, colc)
SELECT col1, col2
FROM TableA
WHERE NOT EXISTS
(SELECT *
FROM TableB
WHERE colb = TableA.col1
AND colc = TableA.col2) ;
David Portas
SQL Server MVP
--|||Hi David,
I am just curious to ask that why SQL Server do not have set
operater MINUS , INTERSECT implemented, which can be implemented by
using query as you did for MINUS operator
If MINUS were implemented the query would be lot simpler
Insert into tableA select * from TableA Minus Select * From TableB
With Warm regards
Jatinder Singh|||EXISTS = INTERSECT
NOT EXISTS = MINUS
Or is it the spelling your concerned about?
Nik Marshall-Blank MCSD/MCDBA
<jatinder.singh@.clovertechnologies.com> wrote in message
news:1126248735.883313.245060@.f14g2000cwb.googlegroups.com...
> Hi David,
> I am just curious to ask that why SQL Server do not have set
> operater MINUS , INTERSECT implemented, which can be implemented by
> using query as you did for MINUS operator
> If MINUS were implemented the query would be lot simpler
> Insert into tableA select * from TableA Minus Select * From TableB
>
> With Warm regards
> Jatinder Singh
>|||I do appreciate your quick response and apologise for not explaining my
situation clearly in my first post. I have added few lines of code that
will create and insert the rows for my examples.
After you run the script I provided to create and insert the rows in
TableA and TableB, if I run your query it does not insert rows 10-A and
10-B (First two rows) into TableB. But it really should because
although 10-A,10-B,10-C exist in table B, no set of just 10-A and 10-B
exist in tableB. I am referring to sets in tableB by uniqe value in
COLA in table B. So in my example there are really are two different
sets in table.
--Creates TableA and inserts the rows for my example
select 10 as 'col1','A' as 'col2' into tableA
insert tableA
select 10,'B'
insert tableA
select 30,'X'
insert tableA
select 30,'Y'
--Creates TableB and inserts the rows for my example
select 1 as 'cola',10 as 'colB','A' as colC into tableB
insert tableB
Select 1, 10, 'B'
insert tableB
Select 1, 10, 'C'
insert tableB
Select 2, 30, 'X'
insert tableB
Select 2, 30, 'Y'
--Displays the content of both the tables
select * from tableA order by col1,col2
Select * from tableB order by cola,colb,colc
--This is not acomplishing what I am wanting to achieve
INSERT INTO TableB (colb, colc)
SELECT col1, col2
FROM TableA
WHERE NOT EXISTS
(SELECT *
FROM TableB
WHERE colb = TableA.col1
AND colc = TableA.col2)
Thanks again for your time.
Shub|||You can also do a
select checksum_agg(binary_checksum(*)) from [Table_Name]
on both tables. You should get the same value if data is the same.
Microsoft claims that this is not absolutely one-hundred-percent perfect as
it is possible for two different tables to return the same value based on
the ascii character values; however, this is unlikely and the method has
always worked for me.
walt
<shubtech@.gmail.com> wrote in message
news:1126209114.244231.60810@.g49g2000cwa.googlegroups.com...
>I am using Sql Server 7 and here is what I am wanting to do. I want to
> be able to compare a data set from table A to see a exact match of data
> set exist in table B, if it does not exist then I want to go ahead and
> add the data set to tableB.
> Here is an example I want check and see if sets of rows from table A =
> sets of rows from TableB. If they do not match I would then go ahead
> and add that set from tableA to tableB.
> In my example the set in tableA is grouped by COL1 and COL2 and the set
> in tableB is grouped by COLB and COLC.
> So in this example 10-A,10-B do not exist in TableB. So I should be
> able to add it to Table B.
> TableA
> COL1 COL2
> 10 A
> 10 B
> 30 X
> 30 Y
> TableB
> COLA COLB COLC
> 1 10 A
> 1 10 B
> 1 10 C
> 2 30 X
> 2 30 Y
>
> Any help in this regard will be greatly appreciated. I am trying to
> avoid cursors to achieve this.
> Thanks
> Shub
>|||not sure if that is what you wanted:
create table t1(i int identity, j int)
insert into t1(j) values(1)
insert into t1(j) values(2)
insert into t1(j) values(3)
create table t2(i int identity, j int)
insert into t2(j) values(1)
insert into t2(j) values(2)
insert into t2(j) values(4)
-- rows in t1 that do not have exact match in t2
select * from t1 t
-- there is a row in t2 with the same PK
where exists(select * from t2 where t2.i = t.i)
-- but some other columns are different
and
(select count(*) from
(
select * from t1
union
select * from t2
)t_both where t_both.i = t.i
) = 2
i j
-- --
3 3
(1 row(s) affected)
-- rows in t2 that do not have exact match in t1
select * from t2 t
-- there is a row in t1 with the same PK
where exists(select * from t1 where t1.i = t.i)
-- but some other columns are different
and
(select count(*) from
(
select * from t1
union
select * from t2
)t_both where t_both.i = t.i
) = 2
i j
-- --
3 4
(1 row(s) affected)
drop table t1
drop table t2
and yes, it's easier to accomplish using MINUS|||SQL Server 2005 has the ANSI operators INTERSECT and EXCEPT. You can
also do a "minus join" in SQL2000:
INSERT INTO TableB (colb, colc)
SELECT col1, col2
FROM TableA
LEFT JOIN TableB
ON TableB.colb = TableA.col1
AND TableB.colc = TableA.col2
WHERE TableB.colb IS NULL ;
David Portas
SQL Server MVP
--|||> ON TableB.colb = TableA.col1
> AND TableB.colc = TableA.col2
well If I have more than a hundred columns I need to generate some
really long SQL like this
AND TableB.col121 = TableA.col121
AND TableB.col122 = TableA.col122
kinda boring, is it not? I'd rather have SQL Server do the job for me,
using UNION...|||This is not exactly what I am looking. In my example I should be able
to insert rows
10 A
10 B
because 10 A and 10 B do not exist as a group defined by COlA in table
B
So I should still be able to insert 10 A and 10 B rows with a different
ID for COLA in table B.
After that is inserted into the table I should no longer be able to
insert this into tableB the only other possible rows that I could
insert into table B for my given example would be
10 B
10 C
Hope this make sense. I know I am not explaining it so well.
I do appreciate your time.
Alexander Kuznetsov wrote:
> not sure if that is what you wanted:
> create table t1(i int identity, j int)
> insert into t1(j) values(1)
> insert into t1(j) values(2)
> insert into t1(j) values(3)
> create table t2(i int identity, j int)
> insert into t2(j) values(1)
> insert into t2(j) values(2)
> insert into t2(j) values(4)
> -- rows in t1 that do not have exact match in t2
> select * from t1 t
> -- there is a row in t2 with the same PK
> where exists(select * from t2 where t2.i = t.i)
> -- but some other columns are different
> and
> (select count(*) from
> (
> select * from t1
> union
> select * from t2
> )t_both where t_both.i = t.i
> ) = 2
> i j
> -- --
> 3 3
> (1 row(s) affected)
>
> -- rows in t2 that do not have exact match in t1
> select * from t2 t
> -- there is a row in t1 with the same PK
> where exists(select * from t1 where t1.i = t.i)
> -- but some other columns are different
> and
> (select count(*) from
> (
> select * from t1
> union
> select * from t2
> )t_both where t_both.i = t.i
> ) = 2
>
> i j
> -- --
> 3 4
> (1 row(s) affected)
>
> drop table t1
> drop table t2
> and yes, it's easier to accomplish using MINUS

Comparing two data sets betweeen two tables

I am using Sql Server 7 and here is what I am wanting to do. I want to
be able to compare a data set from table A to see a exact match of data
set exist in table B, if it does not exist then I want to go ahead and
add the data set to tableB.
Here is an example I want check and see if sets of rows from table A =
sets of rows from TableB. If they do not match I would then go ahead
and add that set from tableA to tableB.
In my example the set in tableA is grouped by COL1 and COL2 and the set
in tableB is grouped by COLB and COLC.
So in this example 10-A,10-B do not exist in TableB. So I should be
able to add it to Table B.
TableA
COL1 COL2
10 A
10 B
30 X
30 Y
TableB
COLA COLB COLC
1 10 A
1 10 B
1 10 C
2 30 X
2 30 Y
Any help in this regard will be greatly appreciated. I am trying to
avoid cursors to achieve this.
Thanks
Shub
INSERT INTO TableB (colb, colc)
SELECT col1, col2
FROM TableA
WHERE NOT EXISTS
(SELECT *
FROM TableB
WHERE colb = TableA.col1
AND colc = TableA.col2) ;
David Portas
SQL Server MVP
|||Hi David,
I am just curious to ask that why SQL Server do not have set
operater MINUS , INTERSECT implemented, which can be implemented by
using query as you did for MINUS operator
If MINUS were implemented the query would be lot simpler
Insert into tableA select * from TableA Minus Select * From TableB
With Warm regards
Jatinder Singh
|||EXISTS = INTERSECT
NOT EXISTS = MINUS
Or is it the spelling your concerned about?
Nik Marshall-Blank MCSD/MCDBA
<jatinder.singh@.clovertechnologies.com> wrote in message
news:1126248735.883313.245060@.f14g2000cwb.googlegr oups.com...
> Hi David,
> I am just curious to ask that why SQL Server do not have set
> operater MINUS , INTERSECT implemented, which can be implemented by
> using query as you did for MINUS operator
> If MINUS were implemented the query would be lot simpler
> Insert into tableA select * from TableA Minus Select * From TableB
>
> With Warm regards
> Jatinder Singh
>
|||I do appreciate your quick response and apologise for not explaining my
situation clearly in my first post. I have added few lines of code that
will create and insert the rows for my examples.
After you run the script I provided to create and insert the rows in
TableA and TableB, if I run your query it does not insert rows 10-A and
10-B (First two rows) into TableB. But it really should because
although 10-A,10-B,10-C exist in table B, no set of just 10-A and 10-B
exist in tableB. I am referring to sets in tableB by uniqe value in
COLA in table B. So in my example there are really are two different
sets in table.
--Creates TableA and inserts the rows for my example
select 10 as 'col1','A' as 'col2' into tableA
insert tableA
select 10,'B'
insert tableA
select 30,'X'
insert tableA
select 30,'Y'
--Creates TableB and inserts the rows for my example
select 1 as 'cola',10 as 'colB','A' as colC into tableB
insert tableB
Select 1, 10, 'B'
insert tableB
Select 1, 10, 'C'
insert tableB
Select 2, 30, 'X'
insert tableB
Select 2, 30, 'Y'
--Displays the content of both the tables
select * from tableA order by col1,col2
Select * from tableB order by cola,colb,colc
--This is not acomplishing what I am wanting to achieve
INSERT INTO TableB (colb, colc)
SELECT col1, col2
FROM TableA
WHERE NOT EXISTS
(SELECT *
FROM TableB
WHERE colb = TableA.col1
AND colc = TableA.col2)
Thanks again for your time.
Shub
|||You can also do a
select checksum_agg(binary_checksum(*)) from [Table_Name]
on both tables. You should get the same value if data is the same.
Microsoft claims that this is not absolutely one-hundred-percent perfect as
it is possible for two different tables to return the same value based on
the ascii character values; however, this is unlikely and the method has
always worked for me.
walt
<shubtech@.gmail.com> wrote in message
news:1126209114.244231.60810@.g49g2000cwa.googlegro ups.com...
>I am using Sql Server 7 and here is what I am wanting to do. I want to
> be able to compare a data set from table A to see a exact match of data
> set exist in table B, if it does not exist then I want to go ahead and
> add the data set to tableB.
> Here is an example I want check and see if sets of rows from table A =
> sets of rows from TableB. If they do not match I would then go ahead
> and add that set from tableA to tableB.
> In my example the set in tableA is grouped by COL1 and COL2 and the set
> in tableB is grouped by COLB and COLC.
> So in this example 10-A,10-B do not exist in TableB. So I should be
> able to add it to Table B.
> TableA
> COL1 COL2
> 10 A
> 10 B
> 30 X
> 30 Y
> TableB
> COLA COLB COLC
> 1 10 A
> 1 10 B
> 1 10 C
> 2 30 X
> 2 30 Y
>
> Any help in this regard will be greatly appreciated. I am trying to
> avoid cursors to achieve this.
> Thanks
> Shub
>
|||not sure if that is what you wanted:
create table t1(i int identity, j int)
insert into t1(j) values(1)
insert into t1(j) values(2)
insert into t1(j) values(3)
create table t2(i int identity, j int)
insert into t2(j) values(1)
insert into t2(j) values(2)
insert into t2(j) values(4)
-- rows in t1 that do not have exact match in t2
select * from t1 t
-- there is a row in t2 with the same PK
where exists(select * from t2 where t2.i = t.i)
-- but some other columns are different
and
(select count(*) from
(
select * from t1
union
select * from t2
)t_both where t_both.i = t.i
) = 2
i j
-- --
3 3
(1 row(s) affected)
-- rows in t2 that do not have exact match in t1
select * from t2 t
-- there is a row in t1 with the same PK
where exists(select * from t1 where t1.i = t.i)
-- but some other columns are different
and
(select count(*) from
(
select * from t1
union
select * from t2
)t_both where t_both.i = t.i
) = 2
i j
-- --
3 4
(1 row(s) affected)
drop table t1
drop table t2
and yes, it's easier to accomplish using MINUS
|||SQL Server 2005 has the ANSI operators INTERSECT and EXCEPT. You can
also do a "minus join" in SQL2000:
INSERT INTO TableB (colb, colc)
SELECT col1, col2
FROM TableA
LEFT JOIN TableB
ON TableB.colb = TableA.col1
AND TableB.colc = TableA.col2
WHERE TableB.colb IS NULL ;
David Portas
SQL Server MVP
|||> ON TableB.colb = TableA.col1
> AND TableB.colc = TableA.col2
well If I have more than a hundred columns I need to generate some
really long SQL like this
AND TableB.col121 = TableA.col121
AND TableB.col122 = TableA.col122
kinda boring, is it not? I'd rather have SQL Server do the job for me,
using UNION...
|||This is not exactly what I am looking. In my example I should be able
to insert rows
10 A
10 B
because 10 A and 10 B do not exist as a group defined by COlA in table
B
So I should still be able to insert 10 A and 10 B rows with a different
ID for COLA in table B.
After that is inserted into the table I should no longer be able to
insert this into tableB the only other possible rows that I could
insert into table B for my given example would be
10 B
10 C
Hope this make sense. I know I am not explaining it so well.
I do appreciate your time.
Alexander Kuznetsov wrote:
> not sure if that is what you wanted:
> create table t1(i int identity, j int)
> insert into t1(j) values(1)
> insert into t1(j) values(2)
> insert into t1(j) values(3)
> create table t2(i int identity, j int)
> insert into t2(j) values(1)
> insert into t2(j) values(2)
> insert into t2(j) values(4)
> -- rows in t1 that do not have exact match in t2
> select * from t1 t
> -- there is a row in t2 with the same PK
> where exists(select * from t2 where t2.i = t.i)
> -- but some other columns are different
> and
> (select count(*) from
> (
> select * from t1
> union
> select * from t2
> )t_both where t_both.i = t.i
> ) = 2
> i j
> -- --
> 3 3
> (1 row(s) affected)
>
> -- rows in t2 that do not have exact match in t1
> select * from t2 t
> -- there is a row in t1 with the same PK
> where exists(select * from t1 where t1.i = t.i)
> -- but some other columns are different
> and
> (select count(*) from
> (
> select * from t1
> union
> select * from t2
> )t_both where t_both.i = t.i
> ) = 2
>
> i j
> -- --
> 3 4
> (1 row(s) affected)
>
> drop table t1
> drop table t2
> and yes, it's easier to accomplish using MINUS
sqlsql

Comparing two data sets betweeen two tables

I am using Sql Server 7 and here is what I am wanting to do. I want to
be able to compare a data set from table A to see a exact match of data
set exist in table B, if it does not exist then I want to go ahead and
add the data set to tableB.
Here is an example I want check and see if sets of rows from table A = sets of rows from TableB. If they do not match I would then go ahead
and add that set from tableA to tableB.
In my example the set in tableA is grouped by COL1 and COL2 and the set
in tableB is grouped by COLB and COLC.
So in this example 10-A,10-B do not exist in TableB. So I should be
able to add it to Table B.
TableA
COL1 COL2
10 A
10 B
30 X
30 Y
TableB
COLA COLB COLC
1 10 A
1 10 B
1 10 C
2 30 X
2 30 Y
Any help in this regard will be greatly appreciated. I am trying to
avoid cursors to achieve this.
Thanks
ShubINSERT INTO TableB (colb, colc)
SELECT col1, col2
FROM TableA
WHERE NOT EXISTS
(SELECT *
FROM TableB
WHERE colb = TableA.col1
AND colc = TableA.col2) ;
--
David Portas
SQL Server MVP
--|||Hi David,
I am just curious to ask that why SQL Server do not have set
operater MINUS , INTERSECT implemented, which can be implemented by
using query as you did for MINUS operator
If MINUS were implemented the query would be lot simpler
Insert into tableA select * from TableA Minus Select * From TableB
With Warm regards
Jatinder Singh|||EXISTS = INTERSECT
NOT EXISTS = MINUS
Or is it the spelling your concerned about?
--
Nik Marshall-Blank MCSD/MCDBA
<jatinder.singh@.clovertechnologies.com> wrote in message
news:1126248735.883313.245060@.f14g2000cwb.googlegroups.com...
> Hi David,
> I am just curious to ask that why SQL Server do not have set
> operater MINUS , INTERSECT implemented, which can be implemented by
> using query as you did for MINUS operator
> If MINUS were implemented the query would be lot simpler
> Insert into tableA select * from TableA Minus Select * From TableB
>
> With Warm regards
> Jatinder Singh
>|||I do appreciate your quick response and apologise for not explaining my
situation clearly in my first post. I have added few lines of code that
will create and insert the rows for my examples.
After you run the script I provided to create and insert the rows in
TableA and TableB, if I run your query it does not insert rows 10-A and
10-B (First two rows) into TableB. But it really should because
although 10-A,10-B,10-C exist in table B, no set of just 10-A and 10-B
exist in tableB. I am referring to sets in tableB by uniqe value in
COLA in table B. So in my example there are really are two different
sets in table.
--Creates TableA and inserts the rows for my example
select 10 as 'col1','A' as 'col2' into tableA
insert tableA
select 10,'B'
insert tableA
select 30,'X'
insert tableA
select 30,'Y'
--Creates TableB and inserts the rows for my example
select 1 as 'cola',10 as 'colB','A' as colC into tableB
insert tableB
Select 1, 10, 'B'
insert tableB
Select 1, 10, 'C'
insert tableB
Select 2, 30, 'X'
insert tableB
Select 2, 30, 'Y'
--Displays the content of both the tables
select * from tableA order by col1,col2
Select * from tableB order by cola,colb,colc
--This is not acomplishing what I am wanting to achieve
INSERT INTO TableB (colb, colc)
SELECT col1, col2
FROM TableA
WHERE NOT EXISTS
(SELECT *
FROM TableB
WHERE colb = TableA.col1
AND colc = TableA.col2)
Thanks again for your time.
Shub|||You can also do a
select checksum_agg(binary_checksum(*)) from [Table_Name]
on both tables. You should get the same value if data is the same.
Microsoft claims that this is not absolutely one-hundred-percent perfect as
it is possible for two different tables to return the same value based on
the ascii character values; however, this is unlikely and the method has
always worked for me.
walt
<shubtech@.gmail.com> wrote in message
news:1126209114.244231.60810@.g49g2000cwa.googlegroups.com...
>I am using Sql Server 7 and here is what I am wanting to do. I want to
> be able to compare a data set from table A to see a exact match of data
> set exist in table B, if it does not exist then I want to go ahead and
> add the data set to tableB.
> Here is an example I want check and see if sets of rows from table A => sets of rows from TableB. If they do not match I would then go ahead
> and add that set from tableA to tableB.
> In my example the set in tableA is grouped by COL1 and COL2 and the set
> in tableB is grouped by COLB and COLC.
> So in this example 10-A,10-B do not exist in TableB. So I should be
> able to add it to Table B.
> TableA
> COL1 COL2
> 10 A
> 10 B
> 30 X
> 30 Y
> TableB
> COLA COLB COLC
> 1 10 A
> 1 10 B
> 1 10 C
> 2 30 X
> 2 30 Y
>
> Any help in this regard will be greatly appreciated. I am trying to
> avoid cursors to achieve this.
> Thanks
> Shub
>|||not sure if that is what you wanted:
create table t1(i int identity, j int)
insert into t1(j) values(1)
insert into t1(j) values(2)
insert into t1(j) values(3)
create table t2(i int identity, j int)
insert into t2(j) values(1)
insert into t2(j) values(2)
insert into t2(j) values(4)
-- rows in t1 that do not have exact match in t2
select * from t1 t
-- there is a row in t2 with the same PK
where exists(select * from t2 where t2.i = t.i)
-- but some other columns are different
and
(select count(*) from
(
select * from t1
union
select * from t2
)t_both where t_both.i = t.i
) = 2
i j
-- --
3 3
(1 row(s) affected)
-- rows in t2 that do not have exact match in t1
select * from t2 t
-- there is a row in t1 with the same PK
where exists(select * from t1 where t1.i = t.i)
-- but some other columns are different
and
(select count(*) from
(
select * from t1
union
select * from t2
)t_both where t_both.i = t.i
) = 2
i j
-- --
3 4
(1 row(s) affected)
drop table t1
drop table t2
and yes, it's easier to accomplish using MINUS|||SQL Server 2005 has the ANSI operators INTERSECT and EXCEPT. You can
also do a "minus join" in SQL2000:
INSERT INTO TableB (colb, colc)
SELECT col1, col2
FROM TableA
LEFT JOIN TableB
ON TableB.colb = TableA.col1
AND TableB.colc = TableA.col2
WHERE TableB.colb IS NULL ;
--
David Portas
SQL Server MVP
--|||> ON TableB.colb = TableA.col1
> AND TableB.colc = TableA.col2
well If I have more than a hundred columns I need to generate some
really long SQL like this
AND TableB.col121 = TableA.col121
AND TableB.col122 = TableA.col122
kinda boring, is it not? I'd rather have SQL Server do the job for me,
using UNION...|||This is not exactly what I am looking. In my example I should be able
to insert rows
10 A
10 B
because 10 A and 10 B do not exist as a group defined by COlA in table
B
So I should still be able to insert 10 A and 10 B rows with a different
ID for COLA in table B.
After that is inserted into the table I should no longer be able to
insert this into tableB the only other possible rows that I could
insert into table B for my given example would be
10 B
10 C
Hope this make sense. I know I am not explaining it so well.
I do appreciate your time.
Alexander Kuznetsov wrote:
> not sure if that is what you wanted:
> create table t1(i int identity, j int)
> insert into t1(j) values(1)
> insert into t1(j) values(2)
> insert into t1(j) values(3)
> create table t2(i int identity, j int)
> insert into t2(j) values(1)
> insert into t2(j) values(2)
> insert into t2(j) values(4)
> -- rows in t1 that do not have exact match in t2
> select * from t1 t
> -- there is a row in t2 with the same PK
> where exists(select * from t2 where t2.i = t.i)
> -- but some other columns are different
> and
> (select count(*) from
> (
> select * from t1
> union
> select * from t2
> )t_both where t_both.i = t.i
> ) = 2
> i j
> -- --
> 3 3
> (1 row(s) affected)
>
> -- rows in t2 that do not have exact match in t1
> select * from t2 t
> -- there is a row in t1 with the same PK
> where exists(select * from t1 where t1.i = t.i)
> -- but some other columns are different
> and
> (select count(*) from
> (
> select * from t1
> union
> select * from t2
> )t_both where t_both.i = t.i
> ) = 2
>
> i j
> -- --
> 3 4
> (1 row(s) affected)
>
> drop table t1
> drop table t2
> and yes, it's easier to accomplish using MINUS|||I would try something like this:
-- groups that do not have exact matchselect
i.col1
from
(select col1, count(*) col1_cnt from tableA group by col1) i
where not
i.col1_cnt = (select count(*) from tableA
join tableB on tableA.col1 = tableB.colB and tableA.col2 = tableB.colC
where tableA.col1 = i.col1)
or not
i.col1_cnt = (select count(*) from tableB
where tableB.colB = i.col1)|||I think this is going to work. I have tried some example and its
looking like its doing what I want.
Thank you very much for your input and time Alexander.
Alexander Kuznetsov wrote:
> I would try something like this:
> -- groups that do not have exact matchselect
> i.col1
> from
> (select col1, count(*) col1_cnt from tableA group by col1) i
> where not
> i.col1_cnt = (select count(*) from tableA
> join tableB on tableA.col1 = tableB.colB and tableA.col2 = tableB.colC
> where tableA.col1 = i.col1)
> or not
> i.col1_cnt = (select count(*) from tableB
> where tableB.colB = i.col1)|||Ok I have a similar problem that I am unable to figure it out.
Please run the following script that will create table tabA and tabA
with few rows that will help me illustate my issue.
Basically I am trying to update col3 in tabB with the value of colc in
tabA. In tabA there are two sets of rows that are identified by unique
value in colc. So the fisrt two rows are one set and the last three are
another set. The rows in tabB should match the first two rows in tabA.
Can anyone please help me with this update statement. I havce tried few
different things but I am unable to come with a solution
Thanks in advance
Shub
--*******************************************
select 100 as cola, 1 as colb, 1 as colc
into tabA
insert tabA
Select 100,2,1
insert tabA
Select 100,1,2
insert tabA
Select 100,2,2
insert tabA
Select 100,3,2
select 100 as col1, 1 as col2, 0 as col3
into tabb
insert tabb
select 100,2,0
select * from taba order by cola,colc,colb
select * from tabb
--***********************************************|||how should tabb look like after the update?|||After the update tabB should have values of 1 for col3. Thanks for
looking into it Alexander.|||you welcome. Try this:
update tabb set col3 = matches.colc
from tabb,
(select colc, count(*) c from tabA where cola=100 group by colc)
all_taba_groups,
(select colc, count(*) c from tabA, tabB
where cola=100 and col1=100 and colb=col2
group by colc) matches
where col1=100 and all_taba_groups.colc = matches.colc and
all_taba_groups.c = matches.c
with all the usual warnings than update ... from will not tell you if
there is an ambiguity (i.e. more than one matching colc)|||Thanks and that works great, however I do not think I could hardcode
100 because there could be other sets present in both tables. Sorry my
example did not illustrate that. Here please run this script.
After the update I want values of 1 for col3 in tabb where col1= 100
and
values of 4 in col3 in tabb where col1 = 200
--******************************************
select 100 as cola, 1 as colb, 1 as colc
into tabA
insert tabA
Select 100,2,1
insert tabA
Select 100,1,2
insert tabA
Select 100,2,2
insert tabA
Select 100,3,2
insert taba
select 200, 1,3
insert taba
select 200, 2,3
insert taba
select 200, 1,4
insert taba
select 200, 2,4
insert taba
select 200, 3,4
select 100 as col1, 1 as col2, 0 as col3
into tabb
insert tabb
select 100,2,0
Insert tabb
select 200,1,0
Insert tabb
select 200,2,0
Insert tabb
select 200,3,0
select * from taba order by cola,colc,colb
select * from tabb order by col1,col2
--***************************************************|||try this one:
update tabb set col3 = matches.colc
from tabb,
(select colc, cola, count(*) c from tabA group by colc, cola)
all_taba_groups,
(select colc, cola, count(*) c from tabA, tabB
where cola=col1 and colb=col2
group by colc, cola) matches
where tabb.col1=matches.cola
and all_taba_groups.cola = matches.cola
and all_taba_groups.colc = matches.colc
and all_taba_groups.c = matches.c|||Actually I want col3 to be updated with 4 for all rows in tabb where
col1= 200.|||select * from tabb
update tabb set col3 = matches.colc
from tabb,
(select colc, cola, count(*) c from tabA group by colc, cola)
all_taba_groups,
(select col1, count(*) c from tabB group by col1) all_tabB_groups,
(select colc, cola, count(*) c from tabA, tabB
where cola=col1 and colb=col2
group by colc, cola) matches
where tabb.col1=matches.cola
and all_taba_groups.cola = matches.cola
and all_taba_groups.colc = matches.colc
and all_taba_groups.c = matches.c
and all_tabB_groups.col1 = matches.cola
and all_tabB_groups.c = matches.c
select * from tabb order by col1
--drop table tabA
--drop table tabB|||You daaa man. That is exactly what I wanted. Thanks for all your help,
it is greatly appreciated.|||Alexander,
I have run into a situation with this first query you helped me with.
Basically I want a query to report if there is any mismatch of set of
groups between tabaleA and tableB. When I run the query you helped me
with in this example it returns the value 10 but it really should not
return any rows in this instance because there is no mismatch of groups
between these two tables. This query worked in everyother case except
this. Here is the script to create tableA and TableB with data in it.
You will notice that there is no mismatch but the query is returning
10.
--**************************************
--Creates tablea
select 10 as col1,'A' as col2
into tablea
insert tablea
select 10,'B'
insert tablea
select 10,'C'
insert tablea
select 30,'X'
insert tablea
select 30,'Y'
insert tablea
select 40,'A'
insert tablea
select 40,'B'
insert tablea
select 40,'C'
--tableB
Select 1 as cola,10 as colb,'A' as colc
into tableb
insert tableb
select 1,10,'B'
insert tableb
select 2,30,'X'
insert tableb
select 2,30,'Y'
insert tableb
select 3,40,'A'
insert tableb
select 3,40,'B'
insert tableb
select 3,40,'C'
insert tableb
select 4,10,'A'
insert tableb
select 4,10,'B'
insert tableb
select 4,10,'C'
select * from tablea order by col1,col2
select * from tableB order by cola,colb,colc
-- groups that do not have exact matchselect
select
i.col1
from
(select col1, count(*) col1_cnt from tableA group by col1) i
where not
i.col1_cnt = (select count(*) from tableA
join tableB on tableA.col1 = tableB.colB and tableA.col2 = tableB.colC
where tableA.col1 = i.col1)
or not
i.col1_cnt = (select count(*) from tableB
where tableB.colB = i.col1)|||in fact the original query displays groups that have mismatches:
select
i.col1
from
(select col1, count(*) col1_cnt from tableA group by col1) i
where not
i.col1_cnt = (select count(*) from tableA
join tableB on tableA.col1 = tableB.colB and tableA.col2 = tableB.colC
where tableA.col1 = i.col1)
or not
i.col1_cnt = (select count(*) from tableB
where tableB.colB = i.col1)
10 has both a match (4) and a mismatch(1)
the query that shows groups that don't have matches is slightly
different:
select * from tableA
where col1 not in(
-- groups that have exact matches
select
i.col1
from
(select col1, count(*) col1_cnt from tableA group by col1) i,
(select colA, colB, count(*) colB_cnt from tableB group by colA, colB)
j
where i.col1 = j.colB
and i.col1_cnt = j.colB_cnt
and i.col1_cnt = (select count(*) from tableA
join tableB on tableA.col1 = tableB.colB and tableA.col2 = tableB.colC
where tableA.col1 = i.col1 and tableB.cola = j.colA)
)
BTW what do you need all this stuff for.
Is it online dating service looking for a perfect match?|||You are right about the first query and I see how you are looking at
it. However let me expalin what I am trying to do. By the way I want to
thank you for all your help on this it is greatly appreciated.
Basically my project is to allow telephone companies to set up a group
of telephone services and call it a Super Duper Bundle. Customers
could then select a group of customised services from that super duper
Bundle and still call it super duper Bundle. So if the Super duper
Bundle consisted of three different services Voice Mail, CallerID,
Three way calling the possible difeerent sets of Super Duper Bundle
could be consisting of
UniqueID 1 Super Duper Bundle- Voice Mail, CallerID
unique ID 2 Super Duper Bundle- Voice Mail, Three way Calling
unique ID 3 Super Duper Bundle- Caller Id, Three way calling
unique ID 4 Super Duper Bundle -CallerId, Three way calling, Voice
Mail.
So before I create a new bundle set (one of the above 4 combinations)
in this case, I need a query to see if it already exist in the
database. So in my example
TableA consists of all the diferent combination that already exist in
the database.
IDs refer to colA, Superduper Bundle refers to colb and different
services are represented in Colc with individual rows. So the rows in
tableA are grouped together by cola to define a combination.
TableB consists of another set of combination of this Bundle that I
need to check to see if it already exist in the database.
So if you run
select
i.col1
from
(select col1, count(*) col1_cnt from tableA group by col1) i
where not
i.col1_cnt = (select count(*) from tableA
join tableB on tableA.col1 = tableB.colB and tableA.col2 = tableB.colC
where tableA.col1 = i.col1)
or not
i.col1_cnt = (select count(*) from tableB
where tableB.colB = i.col1)
I should not get anything back because the combinations as a group
already exist in tableA, there is no mismatch.
Let me know if you can come up with a solution....
Thanks Again
Shub

Comparing time values

I have a table called WorkItem. It models a chunk of work done during
a working day.

It has two columns that I'm interested in:

Start (smalldatetime) - the TIME the work block is begun
Duration (int) - the duration in minutes of the work block.

In another table called OvertimeRates I have information about rate
multipliers and a column that tells me the TIME that the rate
multiplier kicks in.

e.g.

OTRateBegins (smalldatetime)

In terms of calculating whether a particular work block starts after
the OTRateBegins, I could (I presume) do something like:

If CONVERT(smalldatetime, Start, 108) > CONVERT(smalldatetime,
OTRateBegins, 108)

However, would I be better off using DATEPART functions to get the hour
and minute parts of both the Start and OTRateBegins, and using them
instead? For some reason, (probably paranoia!), I am suspicious of the
CONVERT function.

Apologies for not posting DDL, but I felt that the situation didn't
really warrant it.

Thanks

EdwardI'm confused; if both columns are smalldatetime, then why are you
converting them to smalldatetime?

Stu|||On 25 Aug 2005 09:24:33 -0700, teddysnips@.hotmail.com wrote:

(snip)
>Start (smalldatetime) - the TIME the work block is begun
(...)
>OTRateBegins (smalldatetime)
(...)

>If CONVERT(smalldatetime, Start, 108) > CONVERT(smalldatetime,
>OTRateBegins, 108)

Hi Edward,

If both columns store just a time (or rather: the datepart is left at
the default value), you can use a simple comparison:

IF Start > OTRateBegins

If either or both sport a date value as well, you'll need another
solution. The convert might work (no reason for your paranoia), but
there might be better solutions as well.

If you post CREATE TABLE statements, some INSERT statements with sample
data, and expected output, it'll be easier to help you.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote:
> On 25 Aug 2005 09:24:33 -0700, teddysnips@.hotmail.com wrote:
> (snip)
> >Start (smalldatetime) - the TIME the work block is begun
> (...)
> >OTRateBegins (smalldatetime)
> (...)
> >If CONVERT(smalldatetime, Start, 108) > CONVERT(smalldatetime,
> >OTRateBegins, 108)
> Hi Edward,
> If both columns store just a time (or rather: the datepart is left at
> the default value), you can use a simple comparison:
> IF Start > OTRateBegins
> If either or both sport a date value as well, you'll need another
> solution. The convert might work (no reason for your paranoia), but
> there might be better solutions as well.
> If you post CREATE TABLE statements, some INSERT statements with sample
> data, and expected output, it'll be easier to help you.

Thanks Hugo.

I don't particularly want to post DDL and INSERT statements, as there's
no real data to play with at the moment, and the table relations are a
good deal more complicated than I've let on.

However, suffice it to say that the datepart of the OTRateBegins is
entirely disposable - I'm only interested in the timepart element. So
I just want to ignore the datepart.

In terms of what I personally wish to do (I'm actually developing a UDF
to return accumulated minutes multiplied by the correct OT rate
multiplier) I'm not interested in the datepart of the Start column, but
in fact the datepart of this column is crucial, as it tells one when
the block of work was done (there are different multipliers for
different time periods and different days).

Hence the reason for the CONVERT function (as Stu asked), which I was
using merely to expose the timepart of the two fields. Is there a
better way? As I suggested in my original post, I *could* strip out
the HOUR and MINUTE values using the DATEPART function, and do some
rather more complex comparisons using them, but that seems rather
inefficient.

I dunno. As usual, I'm probably ignoring the obvious and elegant in
favour of the simple and quick. It was like that when I was a C
programmer - I never could get the hang of doing stuff in-line.

Anyway, many thanks for your help.

Edward|||On 25 Aug 2005 12:36:24 -0700, teddysnips@.hotmail.com wrote:

(snip)
>I don't particularly want to post DDL and INSERT statements, as there's
>no real data to play with at the moment, and the table relations are a
>good deal more complicated than I've let on.
>However, suffice it to say that the datepart of the OTRateBegins is
>entirely disposable - I'm only interested in the timepart element. So
>I just want to ignore the datepart.

Hi Edward,

The reason I asked for DDL and INSERT statements is to make sure that
there can be no misunderstanding. It may be because of me not being a
native English speaker, but I'm still not sure if your OTRateBegins
column will contain a time combined with an (irrelevant) date, or if
they will contain only the time (*).

(*) SQL Server will of course always store a date - "only the time"
means that you don't provide any date; in that case, SQL Server will use
the default date (January 1st 1900).

Anyway, here's a more generic answer for comparisons where you want to
compare only the time portion of the datetime:

- If both Column1 and Column2 contain only a time, you can compare them
with a straight comparison (Column1 > Column2); the advantage is that
the optimizer can choose to use any index on either or both of these
columns.
- If one of the columns contains a date + time and the other contains
only the time, you'll have to strip the datepart of the column with date
(Column1 > CONVERT(char(12), Column2, 114) or CONVERT(char(12), Column1,
114) > Column2); in this case, the optimizer can still use an index on
the column that has only the time - the other column is used in a
function, which precludes the use of an index.
- If both columns containt date and time, you'll have to strip both
(CONVERT(char(12), Column1, 114) > CONVERT(char(12), Column2, 114)); the
disadvantage is that the optimizer can't use the indexes on any of these
columns.

If you don't need millisecond precision, you can also use
CONVERT(char(8), Column1, 108).

>In terms of what I personally wish to do (I'm actually developing a UDF
>to return accumulated minutes multiplied by the correct OT rate
>multiplier)
(snip)

I was afraid that it'd be something like that. Yet another reason to
post the CREATE TABLE statements plus some sample data (can be made up)
and expected output, plus the code you currently have.

The use of IF in your original post suggests that you process your input
table row by row. In 99% of all cases, a set-based solution is faster,
shorter, easier to understand and hence also easier to maintain. If you
provide some more information, I (and maybe others as well) can take a
look at your logic and try our hand at converting it to a set-based
version.

(snip)
> As I suggested in my original post, I *could* strip out
>the HOUR and MINUTE values using the DATEPART function, and do some
>rather more complex comparisons using them, but that seems rather
>inefficient.

Ugh! Please don't go there - why would you even want to write messy and
complex code when a simple comparison (with CONVERT, if you have to use
it) will do?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||OK, now I'm even more confused. Too much time behind my keyboard, I
suppose. Hugo, why can't he do a simple compare if both columns
contain a date and time? Here's my example:

DECLARE @.Start smalldatetime
DECLARE @.OTRateBegins smalldatetime

SET @.Start = '6/1/2005 4:00:00 AM'
SET @.OTRateBegins = '5/30/2005 4:00:00 AM'

IF @.Start > @.OtRateBegins
PRINT 'Start > OTRate'
ELSE
PRINT 'SPLAT'

I guess the question is: what does OTRateBegins represent? Is it the
time of the day that overtime kicks in? Or is it a calendar date and
time that represents the overtime phase of a project (e.g., you have a
contract for 100 hours, and you want to be paid the overtime rate if
you go past the 100th hour)?

Still confused.
Stu|||Stu wrote:
> OK, now I'm even more confused. Too much time behind my keyboard, I
> suppose. Hugo, why can't he do a simple compare if both columns
> contain a date and time? Here's my example:
> DECLARE @.Start smalldatetime
> DECLARE @.OTRateBegins smalldatetime
> SET @.Start = '6/1/2005 4:00:00 AM'
> SET @.OTRateBegins = '5/30/2005 4:00:00 AM'
> IF @.Start > @.OtRateBegins
> PRINT 'Start > OTRate'
> ELSE
> PRINT 'SPLAT'
> I guess the question is: what does OTRateBegins represent? Is it the
> time of the day that overtime kicks in?

Yes, which is why I can't do a simple comparison as above
> Or is it a calendar date and
> time that represents the overtime phase of a project (e.g., you have a
> contract for 100 hours, and you want to be paid the overtime rate if
> you go past the 100th hour)?

No!

Thanks Stu and Hugo for your work on this. As it happens, Hugo nailed
it in the previous post. For the comparison I'm trying to do, it is
only the time component that matters, so the use of 108 (I don't need
milliseconds!) for the CONVERT style will be fine.

As Hugo guessed, I'm NOT using a SET based solution, because the
requirements are too complicated for my tiny brain! I'm going to
persevere with my design, and see how it performs. If it's a
three-legged dog, I'll come back here with my begging bowl, and some
DDL and INSERT statements and tax your generosity some more.

But in the meantime, THANK YOU to Stu and Hugo!

Edward

Comparing Tables By The First Seven Digits

I have one database called CAM and two tables. Table one is called WIRELESS and has one field called PHONE with 7 digit phone numbers. Table two is called MASTER and has one field called PHONE with ten digit phone numbers.

I want to compare table WIRELESS to MASTER. I want to compare the 7 digit phone number in WIRELESS table to the first 7 digits in the MASTER table.

Any idea on what the query syntax would be for this?Use (for Oracle) SUBSTR function which will return first 7 digits; something like

SELECT w.phone wireless_phone, SUBSTR(m.phone, 1, 7) master_phone
FROM wireless w, master m
WHERE w.phone_id = m.phone_id;|||I tried the following query

select wireless.wirelessphone, SUBSTR(master.phone, 1,7)
from wireless w, master m
where w.wirelessphone_id = m.phone_id;

and SQL replied that it is not a valid function name.|||What SQL database engine are you using? I'd try using substring as well as substr, since most of the engines that I can think of support the Substring function.

-PatP|||What SQL database engine are you using? I'd try using substring as well as substr, since most of the engines that I can think of support the Substring function.

-PatP

I am using SQL 2000

Comparing Tables and Columns Between Two Databases

Hi,

I have a two databases that may be similar but are not identical.I would like to compare the table and column names of the two databases to find out where they differ.Are there any tools that would help me in this task?In Oracle I would compare the meta data of the two databases in a select clause.Can we do the same thing in MS SQL?

Thanks in advance,

Dave

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23054 should get you what you are looking.

Comparing Tables

If I have Table A and Table B and would like to compare the records in both
tables and take the missing records from A and add them to table B. How can
I do this?
Table A and Table B are different but they have one field in common.
Thanks
Niles wrote:
> If I have Table A and Table B and would like to compare the records
> in both tables and take the missing records from A and add them to
> table B. How can I do this?
> Table A and Table B are different but they have one field in common.
> Thanks
Assuming they have keys that can be joined (I assume this is what you
mean by having one column in common).
create table #A (col1 int)
create table #B (col1 int)
insert into #A values (1)
insert into #A values (2)
insert into #A values (3)
insert into #B values (1)
insert into #B values (3)
Select * from #B
Insert Into #B (col1)
Select col1
From #A
Where Not Exists (
Select * from #B Where #B.col1 = #A.col1)
Select * from #B
David Gugick
Imceda Software
www.imceda.com
|||This query will do it...
INSERT INTO B (COLUMN1,COLUMN2)
SELECT (COLUMN1,COLUMN2)
FROM A
WHERE NOT EXISTS
(SELECT *
FROM B
WHERE A.COMMON_FIELD = B.COMMON_FIELD)
Arshad
arshadmd@.gmail.com
"Niles" wrote:

> If I have Table A and Table B and would like to compare the records in both
> tables and take the missing records from A and add them to table B. How can
> I do this?
> Table A and Table B are different but they have one field in common.
> Thanks
|||Well, I am trying to insert just the primary key From A, the rest of the
columns I want to enter another value such as 0 or some comment. How do I do
that?
"Arshad" wrote:
[vbcol=seagreen]
> This query will do it...
> INSERT INTO B (COLUMN1,COLUMN2)
> SELECT (COLUMN1,COLUMN2)
> FROM A
> WHERE NOT EXISTS
> (SELECT *
> FROM B
> WHERE A.COMMON_FIELD = B.COMMON_FIELD)
> Arshad
> arshadmd@.gmail.com
> "Niles" wrote:

Comparing Tables

If I have Table A and Table B and would like to compare the records in both
tables and take the missing records from A and add them to table B. How can
I do this?
Table A and Table B are different but they have one field in common.
ThanksNiles wrote:
> If I have Table A and Table B and would like to compare the records
> in both tables and take the missing records from A and add them to
> table B. How can I do this?
> Table A and Table B are different but they have one field in common.
> Thanks
Assuming they have keys that can be joined (I assume this is what you
mean by having one column in common).
create table #A (col1 int)
create table #B (col1 int)
insert into #A values (1)
insert into #A values (2)
insert into #A values (3)
insert into #B values (1)
insert into #B values (3)
Select * from #B
Insert Into #B (col1)
Select col1
From #A
Where Not Exists (
Select * from #B Where #B.col1 = #A.col1)
Select * from #B
David Gugick
Imceda Software
www.imceda.com|||This query will do it...
INSERT INTO B (COLUMN1,COLUMN2)
SELECT (COLUMN1,COLUMN2)
FROM A
WHERE NOT EXISTS
(SELECT *
FROM B
WHERE A.COMMON_FIELD = B.COMMON_FIELD)
Arshad
arshadmd@.gmail.com
"Niles" wrote:
> If I have Table A and Table B and would like to compare the records in both
> tables and take the missing records from A and add them to table B. How can
> I do this?
> Table A and Table B are different but they have one field in common.
> Thanks|||Well, I am trying to insert just the primary key From A, the rest of the
columns I want to enter another value such as 0 or some comment. How do I do
that?
"Arshad" wrote:
> This query will do it...
> INSERT INTO B (COLUMN1,COLUMN2)
> SELECT (COLUMN1,COLUMN2)
> FROM A
> WHERE NOT EXISTS
> (SELECT *
> FROM B
> WHERE A.COMMON_FIELD = B.COMMON_FIELD)
> Arshad
> arshadmd@.gmail.com
> "Niles" wrote:
> > If I have Table A and Table B and would like to compare the records in both
> > tables and take the missing records from A and add them to table B. How can
> > I do this?
> > Table A and Table B are different but they have one field in common.
> > Thankssqlsql

comparing tables

Hi,

I have two tables containing (ahem) lists of mp3 tunes. One is the "master" list (table name "mp3_master") - everything I've got on my home pc. The other (table name "mp3") details everything I've got on my work pc.

Both tables have an id field, an "artist" field and a "title" field.

I'd like to simply compare the two tables and return a list of any artist/title combinations that are in the mp3 table (ie: that I have at work) but not in the mp3_master table (ie: that I haven't yet taken home).standard sql solution:

select id, artist, title from mp3
except
select id, artist, title from mp3_master

microsoft sql/server syntax has minus instead of except, i believe

if that don't work, do it the old-fashioned way:

select id, artist, title from mp3
where id not in (select id from mp3_master)

rudy
http://rudy.ca/|||Except is a function used in Analysis Services - MDX

The query

select id, artist, title from mp3
where id not in (select id from mp3_master)

will work fine, however it is a slow way, performance wise. Once a match is found the main query will not stop scanning the sub-query. A faster query is to use the NOT EXISTS

select id, artist, title from mp3
where not exists (select * from mp3_master mp3.id = mp3_master.id)

Once a match is found the search stops|||Hmm - maybe I should have added that the id numbers in these tables don't match up.

select id, artist, title from mp3
where title not in (select title from mp3_master)

I used that. As long as I've not got the same tune by different artists, I should be ok.

select id, artist, title from mp3
where not exists (select * from mp3_master mp3.id = mp3_master.id)

I get a "syntax error near "." on that - is that some sort of shorthand for a join?

Cheers anyway :)|||i don't have sql/server to test on, so i'm guessing, but like i said, i think the operator is MINUS

select artist, title from mp3
minus
select artist, title from mp3_master

if the id numbers don't match up, you don't want to match on id number in the subselect, neither a's way nor mine

rudy|||Hi,

The 'minus' clause certainly won't work in Query Analyzer. There is no syntax even simlar in SQL Server.

The 'not in' or 'where not exists' are the correct syntax.

You had some typos in the SQL that failed with the syntax error. It should be:

select id, artist, title from mp3
where not exists (select * from mp3_master where mp3_master.mp3.id = mp3_master.id)

Hope this helps.

- Andy Abel|||thanks andy, it wouldn't be the first time sql/server didn't support standard sql ;)

spudhead, since you can't match on ids, try this --

select id, artist, title from mp3
where not exists
( select 1 from mp3_master
where artist = mp3.artist
and title = mp3.title )

rudy|||I love reading these forums, to help people and also to read SQLServer bashing. I'm a big fan of SQLServer, so when I read a jab or a bash I have to find out if what is said is true.

it wouldn't be the first time sql/server didn't support standard sql

I tried to find out the ANSI standards for SQL and the only thing I could find on MINUS is:
The MINUS keyword is not ANSI-compliant, the implementation of the MINUS operator is implemented in Oracle.
So bully for Oracle|||yeah, i love these syntax discussions too

actually, the ansi standard operator is EXCEPT

oracle's support of MINUS is non-standard

:cool:

rudy

Comparing table with excel spreadsheet

Hi all,

I have two tables in SQL Server 2005 and excel sheet ( Office 2003).

The colums of excel sheet are: name ,ssn, flagbit ( Note: Excel sheet contains data already)

Columns of table_one and table_two are: name ,ssn

I want to compare the ssn field from table_one and ssn in excelsheet_one, if it matches , then flagbit in excelsheet_one should say"T1".

If i compare ssn field from table_two and ssn in excelsheet_one, if it matches, the flagbit in excelsheet_one should say "T2".

Ex:

Table_one (input) - excelsheet_one ( Output)

ssn name - ssn name flagbit

11 NYC - 11 NYC T1

Both the tables refers same excelsheet_one and have to update the same flag bit column in excelsheet.

Basically, i want to compare table and excel sheet, then if it matches, then update excel sheet.

Does anybody how to do this.

Any help will be greatly appreciated.

Thanks

Export your table to excel and then do the work there.

Comparing table structures.

Hi all,

Could anyone let me know if there are any Scripts available for comparing the schema differences between two SQL server databases?

Thanks

DBAnalyst

I once made a script for that, feel free to contact me if I should send it over.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Please send it Jens, Thanks a lot.sqlsql

Comparing table structures between databases

Unfortunatly no there isn't an easy way.
If you have connection to the internet log on to the site
http://www.adeptsql.com/download.htm and download AdeptSQL
Diff ver 1.6.
We have used that for all our server and works very well.
N.B. You can program it yourself in SQL but will take a
bit of time, and this will hi-light every difference.
Peter
"What makes him think a middle aged actor [Clint
Eastwood], who's played with a chimp, could have a future
in politics?"
Ronald Reagan

>--Original Message--
>What is the easiest way to compare the table structure of
two databases?
>I have inherited a project and have found a couple of
undocumented
>inconsistencies between the development and production
databases.
>Is there an in-built function or a utility that can help
with this?
>Thanks,
>Mike
>
>.
>
Thanks, Peter.
That looks like it will do all I need and more.
Regards,
Mike
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:4bbe01c490da$17ef8560$a601280a@.phx.gbl...[vbcol=seagreen]
> Unfortunatly no there isn't an easy way.
> If you have connection to the internet log on to the site
> http://www.adeptsql.com/download.htm and download AdeptSQL
> Diff ver 1.6.
> We have used that for all our server and works very well.
> N.B. You can program it yourself in SQL but will take a
> bit of time, and this will hi-light every difference.
> Peter
> "What makes him think a middle aged actor [Clint
> Eastwood], who's played with a chimp, could have a future
> in politics?"
> Ronald Reagan
>
> two databases?
> undocumented
> databases.
> with this?

Comparing table structures between databases

What is the easiest way to compare the table structure of two databases?
I have inherited a project and have found a couple of undocumented
inconsistencies between the development and production databases.
Is there an in-built function or a utility that can help with this?
Thanks,
MikeUnfortunatly no there isn't an easy way.
If you have connection to the internet log on to the site
http://www.adeptsql.com/download.htm and download AdeptSQL
Diff ver 1.6.
We have used that for all our server and works very well.
N.B. You can program it yourself in SQL but will take a
bit of time, and this will hi-light every difference.
Peter
"What makes him think a middle aged actor [Clint
Eastwood], who's played with a chimp, could have a future
in politics?"
Ronald Reagan
>--Original Message--
>What is the easiest way to compare the table structure of
two databases?
>I have inherited a project and have found a couple of
undocumented
>inconsistencies between the development and production
databases.
>Is there an in-built function or a utility that can help
with this?
>Thanks,
>Mike
>
>.
>|||Thanks, Peter.
That looks like it will do all I need and more.
Regards,
Mike
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:4bbe01c490da$17ef8560$a601280a@.phx.gbl...
> Unfortunatly no there isn't an easy way.
> If you have connection to the internet log on to the site
> http://www.adeptsql.com/download.htm and download AdeptSQL
> Diff ver 1.6.
> We have used that for all our server and works very well.
> N.B. You can program it yourself in SQL but will take a
> bit of time, and this will hi-light every difference.
> Peter
> "What makes him think a middle aged actor [Clint
> Eastwood], who's played with a chimp, could have a future
> in politics?"
> Ronald Reagan
>
> >--Original Message--
> >What is the easiest way to compare the table structure of
> two databases?
> >
> >I have inherited a project and have found a couple of
> undocumented
> >inconsistencies between the development and production
> databases.
> >
> >Is there an in-built function or a utility that can help
> with this?
> >
> >Thanks,
> >
> >Mike
> >
> >
> >.
> >

Comparing table structures between databases

What is the easiest way to compare the table structure of two databases?
I have inherited a project and have found a couple of undocumented
inconsistencies between the development and production databases.
Is there an in-built function or a utility that can help with this?
Thanks,
MikeThanks, Peter.
That looks like it will do all I need and more.
Regards,
Mike
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:4bbe01c490da$17ef8560$a601280a@.phx.gbl...[vbcol=seagreen]
> Unfortunatly no there isn't an easy way.
> If you have connection to the internet log on to the site
> http://www.adeptsql.com/download.htm and download AdeptSQL
> Diff ver 1.6.
> We have used that for all our server and works very well.
> N.B. You can program it yourself in SQL but will take a
> bit of time, and this will hi-light every difference.
> Peter
> "What makes him think a middle aged actor [Clint
> Eastwood], who's played with a chimp, could have a future
> in politics?"
> Ronald Reagan
>
> two databases?
> undocumented
> databases.
> with this?