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
Showing posts with label columns. Show all posts
Showing posts with label columns. Show all posts
Thursday, March 29, 2012
Comparing time values
I have a table called WorkItem. It models a chunk of work done during
a working day.
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
Tuesday, March 27, 2012
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 Smalldatetime column
I have two Smalldatetime columns. One of the columns have
NULL values in some of the rows. When I compare the two
columns, I don't get the rows that have NULL values (I am
comparing them to be NOT the same e.g Select * from
mytable where column1 <> column2). I get all the rows that
are not the same but have values but not the NULL columns.
How can I get the NULL columns too '
Thanks for help.IS NULL and IS NOT NULL are the predicates used to test for the presence or
absence of NULL:
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
You can read about three-value logic and NULL values in Books Online:
http://msdn.microsoft.com/library/e..._qd_02_8pwy.asp
In general a comparison of anything to NULL is always UNKNOWN which means
NULL values are excluded by statements like
...
WHERE x <> y
David Portas
--
Please reply only to the newsgroup
--|||ANSI defines NULL = NULL as false.
NULL is the abscence of a value and, NULL does not equal NULL.
You can:
a) use IS NULL to perform the test or
b) you could say SET ANSI_NULLS OFF
Books Online will have lot's more info on each option... I hope this helps,
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"James" <anonymous@.discussions.microsoft.com> wrote in message
news:000e01c3dc43$7c50b750$a101280a@.phx.gbl...
ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE - it's still UNKNOWN.
David Portas
--
Please reply only to the newsgroup
--|||I guess I have something wrong in my query. I also was
trying to exclude the values in one of the columns which
was the dates that had 1900 in it (E.g NOT Like '%1900%').
I was trying to add that statement to
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
--OR/AND column1 Not like '%1900%'
but somehow it is eliminating the NULL columns.
Thanks.
it's still UNKNOWN.
trying to exclude the values in one of the columns which
was the dates that had 1900 in it (E.g NOT Like '%1900%').
I was trying to add that statement to
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
--OR/AND column1 Not like '%1900%'
but somehow it is eliminating the NULL columns.
Thanks.
it's still UNKNOWN.
1900%').
required results. It's not clear to me exactly what result you're trying to
get in this case.
When combining AND and OR you have to watch out for the order of precedence.
Use parentheses to make the order of evaluation clear otherwise x OR y AND z
is evaluated as x OR (y AND z).
Don't use LIKE to compare dates. LIKE performs a string comparison and if
you use it on dates then every date has to be cast as a string. Best way to
eliminate the year 1900 is to test for a date >= 1901-01-01.
Here's some code to reproduce a result that *may* be what you want:
CREATE TABLE MyTable (keycol INTEGER PRIMARY KEY, column1 SMALLDATETIME,
column2 SMALLDATETIME)
INSERT INTO MyTable VALUES (1,'20030101','20030101')
INSERT INTO MyTable VALUES (2,'20030101','20030102')
INSERT INTO MyTable VALUES (3,'19000101','19000101')
INSERT INTO MyTable VALUES (4,'19000101','20030101')
INSERT INTO MyTable VALUES (5,'20030101','19000101')
INSERT INTO MyTable VALUES (6,'19000101',NULL)
INSERT INTO MyTable VALUES (7,NULL,'19000101')
INSERT INTO MyTable VALUES (8,'20030101',NULL)
INSERT INTO MyTable VALUES (9,NULL,'20030101')
INSERT INTO MyTable VALUES (10,NULL,NULL)
SELECT *
FROM MyTable
WHERE
COALESCE(column1,'19000101') <> COALESCE(column2,'19000101')
AND
(column1 >= '19010101' OR column2 >= '19010101')
Which returns the rows where Keycol is 2,4,5,8 and 9.
If that's not it and you need more help then you'll have to specify which
rows you want to be included in your result. I should have covered all the
relevant combinations.
Hope this helps.
David Portas
--
Please reply only to the newsgroup
--|||Thanks a lot..........
James.
problem and show your
result you're trying to
order of precedence.
otherwise x OR y AND z
comparison and if
string. Best way to
01.
you want:
SMALLDATETIME,
(column2,'19000101')
to specify which
have covered all the
NULL values in some of the rows. When I compare the two
columns, I don't get the rows that have NULL values (I am
comparing them to be NOT the same e.g Select * from
mytable where column1 <> column2). I get all the rows that
are not the same but have values but not the NULL columns.
How can I get the NULL columns too '
Thanks for help.IS NULL and IS NOT NULL are the predicates used to test for the presence or
absence of NULL:
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
You can read about three-value logic and NULL values in Books Online:
http://msdn.microsoft.com/library/e..._qd_02_8pwy.asp
In general a comparison of anything to NULL is always UNKNOWN which means
NULL values are excluded by statements like
...
WHERE x <> y
David Portas
--
Please reply only to the newsgroup
--|||ANSI defines NULL = NULL as false.
NULL is the abscence of a value and, NULL does not equal NULL.
You can:
a) use IS NULL to perform the test or
b) you could say SET ANSI_NULLS OFF
Books Online will have lot's more info on each option... I hope this helps,
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"James" <anonymous@.discussions.microsoft.com> wrote in message
news:000e01c3dc43$7c50b750$a101280a@.phx.gbl...
quote:|||> b) you could say SET ANSI_NULLS OFF
> I have two Smalldatetime columns. One of the columns have
> NULL values in some of the rows. When I compare the two
> columns, I don't get the rows that have NULL values (I am
> comparing them to be NOT the same e.g Select * from
> mytable where column1 <> column2). I get all the rows that
> are not the same but have values but not the NULL columns.
> How can I get the NULL columns too '
>
> Thanks for help.
ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE - it's still UNKNOWN.
David Portas
--
Please reply only to the newsgroup
--|||I guess I have something wrong in my query. I also was
trying to exclude the values in one of the columns which
was the dates that had 1900 in it (E.g NOT Like '%1900%').
I was trying to add that statement to
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
--OR/AND column1 Not like '%1900%'
but somehow it is eliminating the NULL columns.
Thanks.
quote:
>--Original Message--
>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -
it's still UNKNOWN.
quote:|||I guess I have something wrong in my query. I also was
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>
trying to exclude the values in one of the columns which
was the dates that had 1900 in it (E.g NOT Like '%1900%').
I was trying to add that statement to
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
--OR/AND column1 Not like '%1900%'
but somehow it is eliminating the NULL columns.
Thanks.
quote:
>--Original Message--
>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -
it's still UNKNOWN.
quote:|||SORRY FOR THE DOUBLE POST...
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>
quote:
>--Original Message--
>I guess I have something wrong in my query. I also was
>trying to exclude the values in one of the columns which
>was the dates that had 1900 in it (E.g NOT Like '%
1900%').
quote:|||It helps if you can post some code to reproduce your problem and show your
>I was trying to add that statement to
>SELECT *
> FROM mytable
> WHERE column1 <> column2
> OR column1 IS NULL
> OR column2 IS NULL
>--OR/AND column1 Not like '%1900%'
>but somehow it is eliminating the NULL columns.
>Thanks.
>
>it's still UNKNOWN.
>.
>
required results. It's not clear to me exactly what result you're trying to
get in this case.
When combining AND and OR you have to watch out for the order of precedence.
Use parentheses to make the order of evaluation clear otherwise x OR y AND z
is evaluated as x OR (y AND z).
Don't use LIKE to compare dates. LIKE performs a string comparison and if
you use it on dates then every date has to be cast as a string. Best way to
eliminate the year 1900 is to test for a date >= 1901-01-01.
Here's some code to reproduce a result that *may* be what you want:
CREATE TABLE MyTable (keycol INTEGER PRIMARY KEY, column1 SMALLDATETIME,
column2 SMALLDATETIME)
INSERT INTO MyTable VALUES (1,'20030101','20030101')
INSERT INTO MyTable VALUES (2,'20030101','20030102')
INSERT INTO MyTable VALUES (3,'19000101','19000101')
INSERT INTO MyTable VALUES (4,'19000101','20030101')
INSERT INTO MyTable VALUES (5,'20030101','19000101')
INSERT INTO MyTable VALUES (6,'19000101',NULL)
INSERT INTO MyTable VALUES (7,NULL,'19000101')
INSERT INTO MyTable VALUES (8,'20030101',NULL)
INSERT INTO MyTable VALUES (9,NULL,'20030101')
INSERT INTO MyTable VALUES (10,NULL,NULL)
SELECT *
FROM MyTable
WHERE
COALESCE(column1,'19000101') <> COALESCE(column2,'19000101')
AND
(column1 >= '19010101' OR column2 >= '19010101')
Which returns the rows where Keycol is 2,4,5,8 and 9.
If that's not it and you need more help then you'll have to specify which
rows you want to be included in your result. I should have covered all the
relevant combinations.
Hope this helps.
David Portas
--
Please reply only to the newsgroup
--|||Thanks a lot..........
James.
quote:
>--Original Message--
>It helps if you can post some code to reproduce your
problem and show your
quote:
>required results. It's not clear to me exactly what
result you're trying to
quote:
>get in this case.
>When combining AND and OR you have to watch out for the
order of precedence.
quote:
>Use parentheses to make the order of evaluation clear
otherwise x OR y AND z
quote:
>is evaluated as x OR (y AND z).
>Don't use LIKE to compare dates. LIKE performs a string
comparison and if
quote:
>you use it on dates then every date has to be cast as a
string. Best way to
quote:
>eliminate the year 1900 is to test for a date >= 1901-01-
01.
quote:
>Here's some code to reproduce a result that *may* be what
you want:
quote:
>CREATE TABLE MyTable (keycol INTEGER PRIMARY KEY, column1
SMALLDATETIME,
quote:
>column2 SMALLDATETIME)
>INSERT INTO MyTable VALUES (1,'20030101','20030101')
>INSERT INTO MyTable VALUES (2,'20030101','20030102')
>INSERT INTO MyTable VALUES (3,'19000101','19000101')
>INSERT INTO MyTable VALUES (4,'19000101','20030101')
>INSERT INTO MyTable VALUES (5,'20030101','19000101')
>INSERT INTO MyTable VALUES (6,'19000101',NULL)
>INSERT INTO MyTable VALUES (7,NULL,'19000101')
>INSERT INTO MyTable VALUES (8,'20030101',NULL)
>INSERT INTO MyTable VALUES (9,NULL,'20030101')
>INSERT INTO MyTable VALUES (10,NULL,NULL)
>SELECT *
> FROM MyTable
> WHERE
> COALESCE(column1,'19000101') <> COALESCE
(column2,'19000101')
quote:
d">
> AND
> (column1 >= '19010101' OR column2 >= '19010101')
>Which returns the rows where Keycol is 2,4,5,8 and 9.
>If that's not it and you need more help then you'll have
to specify which
quote:
>rows you want to be included in your result. I should
have covered all the
quote:
>relevant combinations.
>Hope this helps.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>
Comparing Smalldatetime column
I have two Smalldatetime columns. One of the columns have
NULL values in some of the rows. When I compare the two
columns, I don't get the rows that have NULL values (I am
comparing them to be NOT the same e.g Select * from
mytable where column1 <> column2). I get all the rows that
are not the same but have values but not the NULL columns.
How can I get the NULL columns too '
Thanks for help.IS NULL and IS NOT NULL are the predicates used to test for the presence or
absence of NULL:
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
You can read about three-value logic and NULL values in Books Online:
http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_02_8pwy.asp
In general a comparison of anything to NULL is always UNKNOWN which means
NULL values are excluded by statements like
...
WHERE x <> y
--
David Portas
--
Please reply only to the newsgroup
--|||ANSI defines NULL = NULL as false.
NULL is the abscence of a value and, NULL does not equal NULL.
You can:
a) use IS NULL to perform the test or
b) you could say SET ANSI_NULLS OFF
Books Online will have lot's more info on each option... I hope this helps,
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"James" <anonymous@.discussions.microsoft.com> wrote in message
news:000e01c3dc43$7c50b750$a101280a@.phx.gbl...
> I have two Smalldatetime columns. One of the columns have
> NULL values in some of the rows. When I compare the two
> columns, I don't get the rows that have NULL values (I am
> comparing them to be NOT the same e.g Select * from
> mytable where column1 <> column2). I get all the rows that
> are not the same but have values but not the NULL columns.
> How can I get the NULL columns too '
>
> Thanks for help.|||> b) you could say SET ANSI_NULLS OFF
ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE - it's still UNKNOWN.
--
David Portas
--
Please reply only to the newsgroup
--|||I guess I have something wrong in my query. I also was
trying to exclude the values in one of the columns which
was the dates that had 1900 in it (E.g NOT Like '%1900%').
I was trying to add that statement to
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
--OR/AND column1 Not like '%1900%'
but somehow it is eliminating the NULL columns.
Thanks.
>--Original Message--
>> b) you could say SET ANSI_NULLS OFF
>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -
it's still UNKNOWN.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>|||I guess I have something wrong in my query. I also was
trying to exclude the values in one of the columns which
was the dates that had 1900 in it (E.g NOT Like '%1900%').
I was trying to add that statement to
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
--OR/AND column1 Not like '%1900%'
but somehow it is eliminating the NULL columns.
Thanks.
>--Original Message--
>> b) you could say SET ANSI_NULLS OFF
>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -
it's still UNKNOWN.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>|||SORRY FOR THE DOUBLE POST...
>--Original Message--
>I guess I have something wrong in my query. I also was
>trying to exclude the values in one of the columns which
>was the dates that had 1900 in it (E.g NOT Like '%
1900%').
>I was trying to add that statement to
>SELECT *
> FROM mytable
> WHERE column1 <> column2
> OR column1 IS NULL
> OR column2 IS NULL
>--OR/AND column1 Not like '%1900%'
>but somehow it is eliminating the NULL columns.
>Thanks.
>>--Original Message--
>> b) you could say SET ANSI_NULLS OFF
>>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -
>it's still UNKNOWN.
>>--
>>David Portas
>>--
>>Please reply only to the newsgroup
>>--
>>
>>.
>.
>|||It helps if you can post some code to reproduce your problem and show your
required results. It's not clear to me exactly what result you're trying to
get in this case.
When combining AND and OR you have to watch out for the order of precedence.
Use parentheses to make the order of evaluation clear otherwise x OR y AND z
is evaluated as x OR (y AND z).
Don't use LIKE to compare dates. LIKE performs a string comparison and if
you use it on dates then every date has to be cast as a string. Best way to
eliminate the year 1900 is to test for a date >= 1901-01-01.
Here's some code to reproduce a result that *may* be what you want:
CREATE TABLE MyTable (keycol INTEGER PRIMARY KEY, column1 SMALLDATETIME,
column2 SMALLDATETIME)
INSERT INTO MyTable VALUES (1,'20030101','20030101')
INSERT INTO MyTable VALUES (2,'20030101','20030102')
INSERT INTO MyTable VALUES (3,'19000101','19000101')
INSERT INTO MyTable VALUES (4,'19000101','20030101')
INSERT INTO MyTable VALUES (5,'20030101','19000101')
INSERT INTO MyTable VALUES (6,'19000101',NULL)
INSERT INTO MyTable VALUES (7,NULL,'19000101')
INSERT INTO MyTable VALUES (8,'20030101',NULL)
INSERT INTO MyTable VALUES (9,NULL,'20030101')
INSERT INTO MyTable VALUES (10,NULL,NULL)
SELECT *
FROM MyTable
WHERE
COALESCE(column1,'19000101') <> COALESCE(column2,'19000101')
AND
(column1 >= '19010101' OR column2 >= '19010101')
Which returns the rows where Keycol is 2,4,5,8 and 9.
If that's not it and you need more help then you'll have to specify which
rows you want to be included in your result. I should have covered all the
relevant combinations.
Hope this helps.
--
David Portas
--
Please reply only to the newsgroup
--|||Thanks a lot..........
James.
>--Original Message--
>It helps if you can post some code to reproduce your
problem and show your
>required results. It's not clear to me exactly what
result you're trying to
>get in this case.
>When combining AND and OR you have to watch out for the
order of precedence.
>Use parentheses to make the order of evaluation clear
otherwise x OR y AND z
>is evaluated as x OR (y AND z).
>Don't use LIKE to compare dates. LIKE performs a string
comparison and if
>you use it on dates then every date has to be cast as a
string. Best way to
>eliminate the year 1900 is to test for a date >= 1901-01-
01.
>Here's some code to reproduce a result that *may* be what
you want:
>CREATE TABLE MyTable (keycol INTEGER PRIMARY KEY, column1
SMALLDATETIME,
>column2 SMALLDATETIME)
>INSERT INTO MyTable VALUES (1,'20030101','20030101')
>INSERT INTO MyTable VALUES (2,'20030101','20030102')
>INSERT INTO MyTable VALUES (3,'19000101','19000101')
>INSERT INTO MyTable VALUES (4,'19000101','20030101')
>INSERT INTO MyTable VALUES (5,'20030101','19000101')
>INSERT INTO MyTable VALUES (6,'19000101',NULL)
>INSERT INTO MyTable VALUES (7,NULL,'19000101')
>INSERT INTO MyTable VALUES (8,'20030101',NULL)
>INSERT INTO MyTable VALUES (9,NULL,'20030101')
>INSERT INTO MyTable VALUES (10,NULL,NULL)
>SELECT *
> FROM MyTable
> WHERE
> COALESCE(column1,'19000101') <> COALESCE
(column2,'19000101')
> AND
> (column1 >= '19010101' OR column2 >= '19010101')
>Which returns the rows where Keycol is 2,4,5,8 and 9.
>If that's not it and you need more help then you'll have
to specify which
>rows you want to be included in your result. I should
have covered all the
>relevant combinations.
>Hope this helps.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>
NULL values in some of the rows. When I compare the two
columns, I don't get the rows that have NULL values (I am
comparing them to be NOT the same e.g Select * from
mytable where column1 <> column2). I get all the rows that
are not the same but have values but not the NULL columns.
How can I get the NULL columns too '
Thanks for help.IS NULL and IS NOT NULL are the predicates used to test for the presence or
absence of NULL:
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
You can read about three-value logic and NULL values in Books Online:
http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_02_8pwy.asp
In general a comparison of anything to NULL is always UNKNOWN which means
NULL values are excluded by statements like
...
WHERE x <> y
--
David Portas
--
Please reply only to the newsgroup
--|||ANSI defines NULL = NULL as false.
NULL is the abscence of a value and, NULL does not equal NULL.
You can:
a) use IS NULL to perform the test or
b) you could say SET ANSI_NULLS OFF
Books Online will have lot's more info on each option... I hope this helps,
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"James" <anonymous@.discussions.microsoft.com> wrote in message
news:000e01c3dc43$7c50b750$a101280a@.phx.gbl...
> I have two Smalldatetime columns. One of the columns have
> NULL values in some of the rows. When I compare the two
> columns, I don't get the rows that have NULL values (I am
> comparing them to be NOT the same e.g Select * from
> mytable where column1 <> column2). I get all the rows that
> are not the same but have values but not the NULL columns.
> How can I get the NULL columns too '
>
> Thanks for help.|||> b) you could say SET ANSI_NULLS OFF
ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE - it's still UNKNOWN.
--
David Portas
--
Please reply only to the newsgroup
--|||I guess I have something wrong in my query. I also was
trying to exclude the values in one of the columns which
was the dates that had 1900 in it (E.g NOT Like '%1900%').
I was trying to add that statement to
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
--OR/AND column1 Not like '%1900%'
but somehow it is eliminating the NULL columns.
Thanks.
>--Original Message--
>> b) you could say SET ANSI_NULLS OFF
>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -
it's still UNKNOWN.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>|||I guess I have something wrong in my query. I also was
trying to exclude the values in one of the columns which
was the dates that had 1900 in it (E.g NOT Like '%1900%').
I was trying to add that statement to
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
--OR/AND column1 Not like '%1900%'
but somehow it is eliminating the NULL columns.
Thanks.
>--Original Message--
>> b) you could say SET ANSI_NULLS OFF
>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -
it's still UNKNOWN.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>|||SORRY FOR THE DOUBLE POST...
>--Original Message--
>I guess I have something wrong in my query. I also was
>trying to exclude the values in one of the columns which
>was the dates that had 1900 in it (E.g NOT Like '%
1900%').
>I was trying to add that statement to
>SELECT *
> FROM mytable
> WHERE column1 <> column2
> OR column1 IS NULL
> OR column2 IS NULL
>--OR/AND column1 Not like '%1900%'
>but somehow it is eliminating the NULL columns.
>Thanks.
>>--Original Message--
>> b) you could say SET ANSI_NULLS OFF
>>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -
>it's still UNKNOWN.
>>--
>>David Portas
>>--
>>Please reply only to the newsgroup
>>--
>>
>>.
>.
>|||It helps if you can post some code to reproduce your problem and show your
required results. It's not clear to me exactly what result you're trying to
get in this case.
When combining AND and OR you have to watch out for the order of precedence.
Use parentheses to make the order of evaluation clear otherwise x OR y AND z
is evaluated as x OR (y AND z).
Don't use LIKE to compare dates. LIKE performs a string comparison and if
you use it on dates then every date has to be cast as a string. Best way to
eliminate the year 1900 is to test for a date >= 1901-01-01.
Here's some code to reproduce a result that *may* be what you want:
CREATE TABLE MyTable (keycol INTEGER PRIMARY KEY, column1 SMALLDATETIME,
column2 SMALLDATETIME)
INSERT INTO MyTable VALUES (1,'20030101','20030101')
INSERT INTO MyTable VALUES (2,'20030101','20030102')
INSERT INTO MyTable VALUES (3,'19000101','19000101')
INSERT INTO MyTable VALUES (4,'19000101','20030101')
INSERT INTO MyTable VALUES (5,'20030101','19000101')
INSERT INTO MyTable VALUES (6,'19000101',NULL)
INSERT INTO MyTable VALUES (7,NULL,'19000101')
INSERT INTO MyTable VALUES (8,'20030101',NULL)
INSERT INTO MyTable VALUES (9,NULL,'20030101')
INSERT INTO MyTable VALUES (10,NULL,NULL)
SELECT *
FROM MyTable
WHERE
COALESCE(column1,'19000101') <> COALESCE(column2,'19000101')
AND
(column1 >= '19010101' OR column2 >= '19010101')
Which returns the rows where Keycol is 2,4,5,8 and 9.
If that's not it and you need more help then you'll have to specify which
rows you want to be included in your result. I should have covered all the
relevant combinations.
Hope this helps.
--
David Portas
--
Please reply only to the newsgroup
--|||Thanks a lot..........
James.
>--Original Message--
>It helps if you can post some code to reproduce your
problem and show your
>required results. It's not clear to me exactly what
result you're trying to
>get in this case.
>When combining AND and OR you have to watch out for the
order of precedence.
>Use parentheses to make the order of evaluation clear
otherwise x OR y AND z
>is evaluated as x OR (y AND z).
>Don't use LIKE to compare dates. LIKE performs a string
comparison and if
>you use it on dates then every date has to be cast as a
string. Best way to
>eliminate the year 1900 is to test for a date >= 1901-01-
01.
>Here's some code to reproduce a result that *may* be what
you want:
>CREATE TABLE MyTable (keycol INTEGER PRIMARY KEY, column1
SMALLDATETIME,
>column2 SMALLDATETIME)
>INSERT INTO MyTable VALUES (1,'20030101','20030101')
>INSERT INTO MyTable VALUES (2,'20030101','20030102')
>INSERT INTO MyTable VALUES (3,'19000101','19000101')
>INSERT INTO MyTable VALUES (4,'19000101','20030101')
>INSERT INTO MyTable VALUES (5,'20030101','19000101')
>INSERT INTO MyTable VALUES (6,'19000101',NULL)
>INSERT INTO MyTable VALUES (7,NULL,'19000101')
>INSERT INTO MyTable VALUES (8,'20030101',NULL)
>INSERT INTO MyTable VALUES (9,NULL,'20030101')
>INSERT INTO MyTable VALUES (10,NULL,NULL)
>SELECT *
> FROM MyTable
> WHERE
> COALESCE(column1,'19000101') <> COALESCE
(column2,'19000101')
> AND
> (column1 >= '19010101' OR column2 >= '19010101')
>Which returns the rows where Keycol is 2,4,5,8 and 9.
>If that's not it and you need more help then you'll have
to specify which
>rows you want to be included in your result. I should
have covered all the
>relevant combinations.
>Hope this helps.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>
Comparing result set values of 2 queries ?
Any assistance would be so helpful !!
We have 2 tables.. lets call them INV and COST
Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...
ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425
ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 ... different to ID 1 in INV Table
2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
Thats it ?
Please could someone out there offer some ideas ?
THANKS
JONselect t1.[id] from [inv table] t1 inner join [cost table] t2 on t1.[id]=t2.[id] where t1.amount+t1.vat<>t2.amount+t2.vat|||Hi Rafala, thanks so much for the assistance,GREATLY APPRECIATED
I have a slight problem though...
Table COST can be made up of multiple entries/rows..ie, ID is not the primary key, so it is possible to have multiple rows, all with the same ID.
Table INV has single row entries for each ID(Primary Key)
Basically, INV table has 1 row, say ID 7
COST Table could have 4 rows, all ID 7. I need to add the AMOUNT and VAT columns for all 4 rows of Table COST and measure that up against the total (AMOUNT+VAT)for the single row of Table INV.
ie.
COST
id7 12 4
id7 21 7
id7 35 1
id7 10 87 ...TOTAL 177
INV
id7 78 99 ...TOTAL 177 ..In this case all is well
Should the TOTAL of ALL rows under cost.amount and cost.vat for cost.ID7 not equal TOTAL of inv.amount+inv.vat for inv.ID7, I would need it to bring up this problem ID...
Am I making much sense...
CHEERS|||Hi Rafala, thanks so much for the assistance,GREATLY APPRECIATED
I have a slight problem though...
Table COST can be made up of multiple entries/rows..ie, ID is not the primary key, so it is possible to have multiple rows, all with the same ID.
Table INV has single row entries for each ID(Primary Key)
Basically, INV table has 1 row, say ID 7
COST Table could have 4 rows, all ID 7. I need to add the AMOUNT and VAT columns for all 4 rows of Table COST and measure that up against the total (AMOUNT+VAT)for the single row of Table INV.
ie.
COST
id7 12 4
id7 21 7
id7 35 1
id7 10 87 ...TOTAL 177
INV
id7 78 99 ...TOTAL 177 ..In this case all is well
Should the TOTAL of ALL rows under cost.amount and cost.vat for cost.ID7 not equal TOTAL of inv.amount+inv.vat for inv.ID7, I would need it to bring up this problem ID...
Am I making much sense...
CHEERS|||select t1.[id] from [inv table] t1 inner join (select t2.[id], cost_total=sum(t2.amount+t2.vat) from [cost table] t2 where t1.[id]=t2.[id] group by t2.[id]) co where t1.[id] = co.[id] and (t1.amount+t1.vat)<>co.cost_total
We have 2 tables.. lets call them INV and COST
Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...
ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425
ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 ... different to ID 1 in INV Table
2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
Thats it ?
Please could someone out there offer some ideas ?
THANKS
JONselect t1.[id] from [inv table] t1 inner join [cost table] t2 on t1.[id]=t2.[id] where t1.amount+t1.vat<>t2.amount+t2.vat|||Hi Rafala, thanks so much for the assistance,GREATLY APPRECIATED
I have a slight problem though...
Table COST can be made up of multiple entries/rows..ie, ID is not the primary key, so it is possible to have multiple rows, all with the same ID.
Table INV has single row entries for each ID(Primary Key)
Basically, INV table has 1 row, say ID 7
COST Table could have 4 rows, all ID 7. I need to add the AMOUNT and VAT columns for all 4 rows of Table COST and measure that up against the total (AMOUNT+VAT)for the single row of Table INV.
ie.
COST
id7 12 4
id7 21 7
id7 35 1
id7 10 87 ...TOTAL 177
INV
id7 78 99 ...TOTAL 177 ..In this case all is well
Should the TOTAL of ALL rows under cost.amount and cost.vat for cost.ID7 not equal TOTAL of inv.amount+inv.vat for inv.ID7, I would need it to bring up this problem ID...
Am I making much sense...
CHEERS|||Hi Rafala, thanks so much for the assistance,GREATLY APPRECIATED
I have a slight problem though...
Table COST can be made up of multiple entries/rows..ie, ID is not the primary key, so it is possible to have multiple rows, all with the same ID.
Table INV has single row entries for each ID(Primary Key)
Basically, INV table has 1 row, say ID 7
COST Table could have 4 rows, all ID 7. I need to add the AMOUNT and VAT columns for all 4 rows of Table COST and measure that up against the total (AMOUNT+VAT)for the single row of Table INV.
ie.
COST
id7 12 4
id7 21 7
id7 35 1
id7 10 87 ...TOTAL 177
INV
id7 78 99 ...TOTAL 177 ..In this case all is well
Should the TOTAL of ALL rows under cost.amount and cost.vat for cost.ID7 not equal TOTAL of inv.amount+inv.vat for inv.ID7, I would need it to bring up this problem ID...
Am I making much sense...
CHEERS|||select t1.[id] from [inv table] t1 inner join (select t2.[id], cost_total=sum(t2.amount+t2.vat) from [cost table] t2 where t1.[id]=t2.[id] group by t2.[id]) co where t1.[id] = co.[id] and (t1.amount+t1.vat)<>co.cost_total
Comparing result set values of 2 queries ?
Any assistance would be so helpful !!
We have 2 tables.. lets call them INV and COST
Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...
ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425
ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 ... different to ID 1 in INV Table
2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
Thats it ?
Please could someone out there offer some ideas ?
THANKS
JONI'd use:SELECT *
FROM inv
JOIN cost
ON (cost.id = inv.id)
WHERE inv.amount <> cost.amount
OR inv.vat <> cost.vat-PatP
We have 2 tables.. lets call them INV and COST
Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...
ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425
ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 ... different to ID 1 in INV Table
2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
Thats it ?
Please could someone out there offer some ideas ?
THANKS
JONI'd use:SELECT *
FROM inv
JOIN cost
ON (cost.id = inv.id)
WHERE inv.amount <> cost.amount
OR inv.vat <> cost.vat-PatP
Comparing result set values of 2 queries ?
Any assistance would be so helpful !!
We have 2 tables.. lets call them INV and COST
Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...
ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425
ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 ... different to ID 1 in INV Table
2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
Thats it ?
Please could someone out there offer some ideas ?
THANKS
JONselect id, sum(amount) as inv_amount, sum(vat) as inv_vat,
cast(0, decimal(11,3)) as cost_amount, cast(0, decimal(11,3)) as cost_vat
into #inv
from INV
group by id
select id, sum(amount) as cost_amount, sum(vat) as cost_vat
into #cost
from cost
group by id
update x
set x.cost_amount = v.cost_amount,
x.cost_vat = v.cost_vat
from #inv x, #cost v
where x.id = v.id
select * from #inv|||How is this different from your first post (http://www.dbforums.com/t994762.html)?
-PatP|||???|||Originally posted by mkkmg
??? Click the link I posted. This isn't the first time they've posted that question.
-PatPsqlsql
We have 2 tables.. lets call them INV and COST
Table INV and COST have 3 related columns, namely ID,AMOUNT and VAT. As shown below...
ID | AMOUNT | VAT ( INV TABLE )
1 |20.125 |2.896
2 |10.524 |1.425
ID | AMOUNT | VAT ( COST TABLE )
1 |20.125 |4.821 ... different to ID 1 in INV Table
2 |10.524 |1.425
If you look above, I need to sum the AMOUNT and VAT columns and get a value for each ID, then compare the two tables and get the ID's that have different values...in this case I would need a result saying ID1 as the total of INV TABLE ID1 (23.021) is different to the corresponding ID1 row in COST TABLE (24.946)
Thats it ?
Please could someone out there offer some ideas ?
THANKS
JONselect id, sum(amount) as inv_amount, sum(vat) as inv_vat,
cast(0, decimal(11,3)) as cost_amount, cast(0, decimal(11,3)) as cost_vat
into #inv
from INV
group by id
select id, sum(amount) as cost_amount, sum(vat) as cost_vat
into #cost
from cost
group by id
update x
set x.cost_amount = v.cost_amount,
x.cost_vat = v.cost_vat
from #inv x, #cost v
where x.id = v.id
select * from #inv|||How is this different from your first post (http://www.dbforums.com/t994762.html)?
-PatP|||???|||Originally posted by mkkmg
??? Click the link I posted. This isn't the first time they've posted that question.
-PatPsqlsql
Sunday, March 25, 2012
Comparing queries for flagging conflicts
Hello all... I'm stuck, I cannot figure out how I should go about flagging
conflicts on a sheduling app. I currently have 8 columns (school grades)
that have class over the course of 9 periods. I am populating the asp page
fine, and making changes to the database with forms lists. I need to compare
all the results of one period (thats 8 results) so that i may find a
classroom conflict. Is there any solution in SQL?
conflicts on a sheduling app. I currently have 8 columns (school grades)
that have class over the course of 9 periods. I am populating the asp page
fine, and making changes to the database with forms lists. I need to compare
all the results of one period (thats 8 results) so that i may find a
classroom conflict. Is there any solution in SQL?
This is my query:
sql = "SELECT * FROM schedule WHERE period ='"&num&"'"
I step through this 9 times in a for/next loop
Thanks in advance!
Alpay EnoAlpay Eno (eno@.spamsux.com) writes:
> Hello all... I'm stuck, I cannot figure out how I should go about
> flagging conflicts on a sheduling app. I currently have 8 columns
> (school grades) that have class over the course of 9 periods. I am
> populating the asp page fine, and making changes to the database with
> forms lists. I need to compare all the results of one period (thats 8
> results) so that i may find a classroom conflict. Is there any solution
> in SQL?
Dunno. If you post:
o CREATE TABLE statement(s) for the involved table(s)
o INSERT statements with sample data
o The desired output from that sample data
there are odds that you will get a more precise answer.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Comparing DB's columns and create script
Hi,
I am looking for a solution that can generate a script on column difference
between two databases and its tables.
Basically, I have a one core db and one development db. The development db
has been revised several time and now I need to generate a script that just
adds all new table columns with their defaults to the old core db.
Any knowledge about this or some methods to use.
Thanks in advance
ChristianCheck out SQLCompare, from Red-GAte Software... It will compare the two
databases, and generate a script to do exactly what you need...
http://www.red-gate.com
"Christian Perthen" wrote:
> Hi,
> I am looking for a solution that can generate a script on column differenc
e
> between two databases and its tables.
> Basically, I have a one core db and one development db. The development db
> has been revised several time and now I need to generate a script that jus
t
> adds all new table columns with their defaults to the old core db.
> Any knowledge about this or some methods to use.
> Thanks in advance
> Christian
>
>
I am looking for a solution that can generate a script on column difference
between two databases and its tables.
Basically, I have a one core db and one development db. The development db
has been revised several time and now I need to generate a script that just
adds all new table columns with their defaults to the old core db.
Any knowledge about this or some methods to use.
Thanks in advance
ChristianCheck out SQLCompare, from Red-GAte Software... It will compare the two
databases, and generate a script to do exactly what you need...
http://www.red-gate.com
"Christian Perthen" wrote:
> Hi,
> I am looking for a solution that can generate a script on column differenc
e
> between two databases and its tables.
> Basically, I have a one core db and one development db. The development db
> has been revised several time and now I need to generate a script that jus
t
> adds all new table columns with their defaults to the old core db.
> Any knowledge about this or some methods to use.
> Thanks in advance
> Christian
>
>
comparing DateTime in UK Format
Hello friends,
I am trying to return all records between 2 dates. The Date columns are in DateTime format, and i am ignoring the timestamp. The user should be able to input UK Date Format (dd/mm/yyyy) and return the rows. This sql code works fine for American date format, but i get an error: converting from varchar to datetime when i put in a UK format. eg. 22/11/06. Please advise on this problem! many thanks!
ALTER PROCEDURE SalaryBetweenDates
(
@.WeekStart datetime,
@.WeekEnd datetime
)
AS
BEGIN
SET @.WeekStart = (SELECT REPLACE(CONVERT(DATETIME,@.WeekStart ,103),' ','-'))
SET @.WeekEnd = (SELECT REPLACE(CONVERT(DATETIME,@.WeekEnd ,103),' ','-'))
END
BEGIN
SELECT s.StaffNo,s.StaffName,s.StaffAddress, s.HourlyRate,
sh.HoursWorked, CONVERT(varchar(12), sh.WeekStart, 103) AS StartDate, CONVERT(varchar(12), sh.WeekEnd, 103)As EndDate,(sh.HoursWorked * s.HourlyRate)"Salary"
From Staff As S INNER JOIN StaffHours As Sh
On S.StaffNo = Sh.StaffNo
WHERE sh.WeekStart >= (@.WeekStart)
AND sh.WeekEnd <= (@.WeekEnd)
FOR XML RAW ('paySlip'), root('Staff'), ELEMENTS XSINIL
END
ReturnYou need to convert the UK format date into a format that Sql can read.
I always use the following ones
'YYYY-MM-DD' for date
'YYYY-MM-DD HH:NN:SS' for date & time
use exactly as is... don't change the sperators
so '22/11/06' should be passed to sqlserver as '2006-11-22'|||
If you want to be able to call the procedure like this
EXEC SalaryBetweenDates '22/11/06', '1/12/06'
you're going to have to make the procedure parameters varchars and write some string handling code to figure out the strings that are passed in. I'd recommend that you leave it as it is and have the application pass dates in the format that SQL Server expects, if necessary have the application do the work at figuring out what date the user actually entered.
|||thanks for your help guys. I set the parameters as strings in the end, and used REPLACE(CONVERT) to handle the function
:-)
comparing dates(Minutes, Hours etc)
guys - is this a decent query to pull all columns (dateCreate)
that have a timestamp less than five minutes?
i know its simple, but i've never done a date compare with minutes or hours
in sql server
thanks
rik:o
select top 10 * from ptpuritm
where datediff(MINUTE,dateCreate,getdate()) <=5
select top 10 * from ptpuritm
where datediff(MINUTE,dateCreate,current_timestamp) <=5Type CTRL+K and look at the execution plans for both of the following examples
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myTable99 (dateCreate datetime)
GO
CREATE INDEX myIndex99 ON myTable99(dateCreate)
GO
INSERT INTO myTable99(dateCreate)
SELECT '12/31/1999 23:00:00' UNION ALL
SELECT '12/31/1999 23:10:00' UNION ALL
SELECT '12/31/1999 23:20:00' UNION ALL
SELECT '12/31/1999 23:30:00' UNION ALL
SELECT '12/31/1999 23:40:00' UNION ALL
SELECT '12/31/1999 23:50:00' UNION ALL
SELECT '12/31/1999 23:55:00' UNION ALL
SELECT '12/31/1999 23:56:00' UNION ALL
SELECT '12/31/1999 23:57:00' UNION ALL
SELECT '12/31/1999 23:58:00' UNION ALL
SELECT '12/31/1999 23:59:00' UNION ALL
SELECT '12/31/1999 23:59:59'
GO
SELECT *
FROM myTable99
WHERE datediff(MINUTE,dateCreate,'1/1/2000 00:00:00') <=5
SELECT *
FROM myTable99
WHERE dateCreate <= dateadd(MINUTE,-5,'1/1/2000 00:00:00')
GO
SET NOCOUNT OFF
DROP TABLE myTable99
GO|||Brett, thanks so much for the help on this. Coming from an Oracle background, i can tell you i'm growing to appreciate SQL SERVER each day.
thanks
again|||You like that?
Look here
http://www.sqlteam.com/
that have a timestamp less than five minutes?
i know its simple, but i've never done a date compare with minutes or hours
in sql server
thanks
rik:o
select top 10 * from ptpuritm
where datediff(MINUTE,dateCreate,getdate()) <=5
select top 10 * from ptpuritm
where datediff(MINUTE,dateCreate,current_timestamp) <=5Type CTRL+K and look at the execution plans for both of the following examples
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myTable99 (dateCreate datetime)
GO
CREATE INDEX myIndex99 ON myTable99(dateCreate)
GO
INSERT INTO myTable99(dateCreate)
SELECT '12/31/1999 23:00:00' UNION ALL
SELECT '12/31/1999 23:10:00' UNION ALL
SELECT '12/31/1999 23:20:00' UNION ALL
SELECT '12/31/1999 23:30:00' UNION ALL
SELECT '12/31/1999 23:40:00' UNION ALL
SELECT '12/31/1999 23:50:00' UNION ALL
SELECT '12/31/1999 23:55:00' UNION ALL
SELECT '12/31/1999 23:56:00' UNION ALL
SELECT '12/31/1999 23:57:00' UNION ALL
SELECT '12/31/1999 23:58:00' UNION ALL
SELECT '12/31/1999 23:59:00' UNION ALL
SELECT '12/31/1999 23:59:59'
GO
SELECT *
FROM myTable99
WHERE datediff(MINUTE,dateCreate,'1/1/2000 00:00:00') <=5
SELECT *
FROM myTable99
WHERE dateCreate <= dateadd(MINUTE,-5,'1/1/2000 00:00:00')
GO
SET NOCOUNT OFF
DROP TABLE myTable99
GO|||Brett, thanks so much for the help on this. Coming from an Oracle background, i can tell you i'm growing to appreciate SQL SERVER each day.
thanks
again|||You like that?
Look here
http://www.sqlteam.com/
Thursday, March 22, 2012
Comparing databases
Hi,
I need to compare 2 databases to check for missing objects, columns, etc.
Comparing objects was pretty easy. Just a pair of sql statements on the
sysobjects table and it worked fine.
Now I need to go a level deeper, by comparing missing & different columns in
tables. Is it possible to get the results from the system tables or do I
have to use DMO?
Thanks,
IvanIvan
Visit at http://www.red-gate.com
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:OywqAZsiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I need to compare 2 databases to check for missing objects, columns, etc.
> Comparing objects was pretty easy. Just a pair of sql statements on the
> sysobjects table and it worked fine.
> Now I need to go a level deeper, by comparing missing & different columns
> in
> tables. Is it possible to get the results from the system tables or do I
> have to use DMO?
> Thanks,
> Ivan
>|||I know that there are quite a few tools that exist, but I need to develop my
own tool as this will be part of yet another bigger suite of tools.
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:u9YYEdsiGHA.3884@.TK2MSFTNGP04.phx.gbl...
> Ivan
> Visit at http://www.red-gate.com
>
>
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:OywqAZsiGHA.1508@.TK2MSFTNGP04.phx.gbl...
etc.
columns
>|||Well , then I'd use DMO objects library
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:%231DU91siGHA.3780@.TK2MSFTNGP03.phx.gbl...
>I know that there are quite a few tools that exist, but I need to develop
>my
> own tool as this will be part of yet another bigger suite of tools.
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:u9YYEdsiGHA.3884@.TK2MSFTNGP04.phx.gbl...
> etc.
> columns
>|||What to use depends on whether you prefer to work at the TSQL level or at th
e API level:
TSQL: For 2000, use syscolumns. For 2005, use sys.columns. Or (either versio
n) use the
information_schema views.
API: For 2000, use DMO. For 2005, use SMO.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:%231DU91siGHA.3780@.TK2MSFTNGP03.phx.gbl...
>I know that there are quite a few tools that exist, but I need to develop m
y
> own tool as this will be part of yet another bigger suite of tools.
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:u9YYEdsiGHA.3884@.TK2MSFTNGP04.phx.gbl...
> etc.
> columns
>|||
> Now I need to go a level deeper, by comparing missing & different columns
in
> tables. Is it possible to get the results from the system tables or do I
> have to use DMO?
Try this
select name from <DB1>..syscolumns where
id=object_id('<DB1>..<TABLE_NAME>') and name not in
(select name from <DB2>..syscolumns where
id=object_id('<DB2>..<TABLE_NAME>'))
--This will return the additional columns in table in another database.
You can well modify it to meet your specefic requirement.
I need to compare 2 databases to check for missing objects, columns, etc.
Comparing objects was pretty easy. Just a pair of sql statements on the
sysobjects table and it worked fine.
Now I need to go a level deeper, by comparing missing & different columns in
tables. Is it possible to get the results from the system tables or do I
have to use DMO?
Thanks,
IvanIvan
Visit at http://www.red-gate.com
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:OywqAZsiGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I need to compare 2 databases to check for missing objects, columns, etc.
> Comparing objects was pretty easy. Just a pair of sql statements on the
> sysobjects table and it worked fine.
> Now I need to go a level deeper, by comparing missing & different columns
> in
> tables. Is it possible to get the results from the system tables or do I
> have to use DMO?
> Thanks,
> Ivan
>|||I know that there are quite a few tools that exist, but I need to develop my
own tool as this will be part of yet another bigger suite of tools.
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:u9YYEdsiGHA.3884@.TK2MSFTNGP04.phx.gbl...
> Ivan
> Visit at http://www.red-gate.com
>
>
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
> news:OywqAZsiGHA.1508@.TK2MSFTNGP04.phx.gbl...
etc.
columns
>|||Well , then I'd use DMO objects library
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:%231DU91siGHA.3780@.TK2MSFTNGP03.phx.gbl...
>I know that there are quite a few tools that exist, but I need to develop
>my
> own tool as this will be part of yet another bigger suite of tools.
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:u9YYEdsiGHA.3884@.TK2MSFTNGP04.phx.gbl...
> etc.
> columns
>|||What to use depends on whether you prefer to work at the TSQL level or at th
e API level:
TSQL: For 2000, use syscolumns. For 2005, use sys.columns. Or (either versio
n) use the
information_schema views.
API: For 2000, use DMO. For 2005, use SMO.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:%231DU91siGHA.3780@.TK2MSFTNGP03.phx.gbl...
>I know that there are quite a few tools that exist, but I need to develop m
y
> own tool as this will be part of yet another bigger suite of tools.
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:u9YYEdsiGHA.3884@.TK2MSFTNGP04.phx.gbl...
> etc.
> columns
>|||
> Now I need to go a level deeper, by comparing missing & different columns
in
> tables. Is it possible to get the results from the system tables or do I
> have to use DMO?
Try this
select name from <DB1>..syscolumns where
id=object_id('<DB1>..<TABLE_NAME>') and name not in
(select name from <DB2>..syscolumns where
id=object_id('<DB2>..<TABLE_NAME>'))
--This will return the additional columns in table in another database.
You can well modify it to meet your specefic requirement.
Tuesday, March 20, 2012
Comparing columns in two tables
Hi DBA's,
I need to figure out how to compare the column data in two distinct tables. I have two files that populate these two tables. Basically I am doing a file comparison here. Let me explain the process:
Table 1
Col 1 Col 2
ID Name
1 A
2 C,D
3 F
Table 2
Col
Name
E
F
D
Now if there is any data that is present in Table 2 that matches with the data in table 1 then I need to write the entire record of table 2 into a separate table OR file.
Here is what I think I need to do.
1. Take first record from Table 1 and scan Table 2 to see if the Name 'A' exists. If yes put/insert the record from Table 2 in a seprate table say table 3 and then go to the second record. If no match then go directly to the second record in table 1. Repeat the process till every record in table 1 is compared to the records in table 2.
2. Now the trick here is some Names have only last name. Others have last name and first name. So for Table 1, Name C,D should be a match to D in Table 2. I have to send this record to Table 3. How do I accomplish that? Should I spilt the Col2 into columns. How do I do that?
Please note that table 2 would have close to 5000 records.
Please advise.
Thanks in anticipation.Think you'll be having a lot of false matches on Smtih and Jones...
Maybe not Kaiser though...
Can you post the DDL of the Tables, and sample data..like
CREATE TABLE myTable99 (Col1 int, Col2, varchar(50), ect
For Sample Data, something like..
INSERT INTO myTable99 (Col1, Col2, ect)
SELECT 1, 'Brett Kaiser', ect UNION ALL
SELECT 2, 'Indiana Jones', ect UNION ALL
SELECT 3, 'Jones', ect UNION ALL
SELECT 4, 'Jeff Smith', ect UNION ALL
get the picture?
It's easier to help when we have the actual stuff...
Still think the matching will be a fudge though..
maybe you can match on exact, remove that population, then do the fudge on a smaller subset...sqlsql
I need to figure out how to compare the column data in two distinct tables. I have two files that populate these two tables. Basically I am doing a file comparison here. Let me explain the process:
Table 1
Col 1 Col 2
ID Name
1 A
2 C,D
3 F
Table 2
Col
Name
E
F
D
Now if there is any data that is present in Table 2 that matches with the data in table 1 then I need to write the entire record of table 2 into a separate table OR file.
Here is what I think I need to do.
1. Take first record from Table 1 and scan Table 2 to see if the Name 'A' exists. If yes put/insert the record from Table 2 in a seprate table say table 3 and then go to the second record. If no match then go directly to the second record in table 1. Repeat the process till every record in table 1 is compared to the records in table 2.
2. Now the trick here is some Names have only last name. Others have last name and first name. So for Table 1, Name C,D should be a match to D in Table 2. I have to send this record to Table 3. How do I accomplish that? Should I spilt the Col2 into columns. How do I do that?
Please note that table 2 would have close to 5000 records.
Please advise.
Thanks in anticipation.Think you'll be having a lot of false matches on Smtih and Jones...
Maybe not Kaiser though...
Can you post the DDL of the Tables, and sample data..like
CREATE TABLE myTable99 (Col1 int, Col2, varchar(50), ect
For Sample Data, something like..
INSERT INTO myTable99 (Col1, Col2, ect)
SELECT 1, 'Brett Kaiser', ect UNION ALL
SELECT 2, 'Indiana Jones', ect UNION ALL
SELECT 3, 'Jones', ect UNION ALL
SELECT 4, 'Jeff Smith', ect UNION ALL
get the picture?
It's easier to help when we have the actual stuff...
Still think the matching will be a fudge though..
maybe you can match on exact, remove that population, then do the fudge on a smaller subset...sqlsql
Comparing columns contents between 2 table...
any idea on the syntax for such a querie? all my attempts to compare an item # column in 2 seperate tables keep coming up an incorrect syntax...(I want to compare one column in one table with a column in another table).
Thanks in advance:rolleyes:I'm not sure on what you are asking?
SELECT a.*
FROM TBL a, TBL b
WHERE a.col = b.col
or
SELECT a.*
FROM TBL a
WHERE NOT EXISTS
(
SELECT *
FROM TBL b
WHERE a.col = b.col
)
??|||sorry I did not respond to your reply:
Thanks for the info - also the first one works for me...
SELECT *
FROM tableA, tableB
WHERE tableA.a_fieldname = tableB.b_fieldname
simple really :)
Again thanks... Now if I can only get it to ignore NULL and empty cells...|||SELECT *
FROM tableA, tableB
WHERE tableA.a_fieldname = tableB.b_fieldname
AND tableB.b_fieldname is not null
and LEN (tableB.b_fieldname) <> 0
--you can also put in tableA.a_fieldname as well if you so desire
-- tell me if that sorts out your problem|||thanks works fine :)
got to get it onto my production server and run now... Thanks,
Thanks in advance:rolleyes:I'm not sure on what you are asking?
SELECT a.*
FROM TBL a, TBL b
WHERE a.col = b.col
or
SELECT a.*
FROM TBL a
WHERE NOT EXISTS
(
SELECT *
FROM TBL b
WHERE a.col = b.col
)
??|||sorry I did not respond to your reply:
Thanks for the info - also the first one works for me...
SELECT *
FROM tableA, tableB
WHERE tableA.a_fieldname = tableB.b_fieldname
simple really :)
Again thanks... Now if I can only get it to ignore NULL and empty cells...|||SELECT *
FROM tableA, tableB
WHERE tableA.a_fieldname = tableB.b_fieldname
AND tableB.b_fieldname is not null
and LEN (tableB.b_fieldname) <> 0
--you can also put in tableA.a_fieldname as well if you so desire
-- tell me if that sorts out your problem|||thanks works fine :)
got to get it onto my production server and run now... Thanks,
Comparing column data in two tables
DBA's of SQL Server:
I have two tables, the columns of which I need to compare.
Table A
Col1............Col2
Name.......ID
ABC........ 1
DEF ........ 2
WXY.........3
Table B
Col1......Col2....Col3
Name1...Name2..Name3
A......... B.......C
D........ G........Z
I need to output every record of Table A where the even a single alphabet in name column matches with Table B.
So as in the above example there is a match for record 1 and record 2( D in DEF matches with record2 Name1 in Table B) in Table A but record3 (WXY) does not match with any of the alphabets of Table B. So I should get only the first two rows of Table A. Can anyone help me structure this query?
Appreciate your help.Try something like this
select * from tablea ta , tableb tb
where
ta.name like '%' + convert(varchar(1),tb.name1) +'%'
or
ta.name like '%' + convert(varchar(1),tb.name2) +'%'
or
ta.name like '%' + convert(varchar(1),tb.name3) +'%'
Originally posted by vivek_vdc
DBA's of SQL Server:
I have two tables, the columns of which I need to compare.
Table A
Col1............Col2
Name.......ID
ABC........ 1
DEF ........ 2
WXY.........3
Table B
Col1......Col2....Col3
Name1...Name2..Name3
A......... B.......C
D........ G........Z
I need to output every record of Table A where the even a single alphabet in name column matches with Table B.
So as in the above example there is a match for record 1 and record 2( D in DEF matches with record2 Name1 in Table B) in Table A but record3 (WXY) does not match with any of the alphabets of Table B. So I should get only the first two rows of Table A. Can anyone help me structure this query?
Appreciate your help.|||This doesn't work. Here is what happens. Let me make the tables simple.
Table A
Name......ID
B C A........1
D E F.........2
A............3
Table B
Name1
A
I need all records that contain alphabet A. With the query -
select * from tablea ta , tableb tb
where
ta.name like '%' + convert(varchar(1),tb.name1) +'%'
Only record 3 (A...3) is returned. I also need record 1. Note that the individual alphabets are separated by spaces.
Let me know. Thanks.
Originally posted by fhunth
Try something like this
select * from tablea ta , tableb tb
where
ta.name like '%' + convert(varchar(1),tb.name1) +'%'
or
ta.name like '%' + convert(varchar(1),tb.name2) +'%'
or
ta.name like '%' + convert(varchar(1),tb.name3) +'%'|||Here you go. I created two tables exactly as you specified:
create table table1
(Name varchar(3)
,ID int identity (1,1))
It has values:
ABC, 1
DEF, 2
WXY, 3
create table table2
(Name1 char(1)
,Name2 char(1)
,Name3 char(1))
It has values:
A, B, C
D, G, Z
Now run this:
select distinct table1.Name, table1.ID from table1, table2
where charindex (substring(NAME,1,1), Name1+Name2+Name3) <> 0
or charindex (substring(NAME,1,2), Name1+Name2+Name3) <> 0
or charindex (substring(NAME,1,3), Name1+Name2+Name3) <> 0
I have two tables, the columns of which I need to compare.
Table A
Col1............Col2
Name.......ID
ABC........ 1
DEF ........ 2
WXY.........3
Table B
Col1......Col2....Col3
Name1...Name2..Name3
A......... B.......C
D........ G........Z
I need to output every record of Table A where the even a single alphabet in name column matches with Table B.
So as in the above example there is a match for record 1 and record 2( D in DEF matches with record2 Name1 in Table B) in Table A but record3 (WXY) does not match with any of the alphabets of Table B. So I should get only the first two rows of Table A. Can anyone help me structure this query?
Appreciate your help.Try something like this
select * from tablea ta , tableb tb
where
ta.name like '%' + convert(varchar(1),tb.name1) +'%'
or
ta.name like '%' + convert(varchar(1),tb.name2) +'%'
or
ta.name like '%' + convert(varchar(1),tb.name3) +'%'
Originally posted by vivek_vdc
DBA's of SQL Server:
I have two tables, the columns of which I need to compare.
Table A
Col1............Col2
Name.......ID
ABC........ 1
DEF ........ 2
WXY.........3
Table B
Col1......Col2....Col3
Name1...Name2..Name3
A......... B.......C
D........ G........Z
I need to output every record of Table A where the even a single alphabet in name column matches with Table B.
So as in the above example there is a match for record 1 and record 2( D in DEF matches with record2 Name1 in Table B) in Table A but record3 (WXY) does not match with any of the alphabets of Table B. So I should get only the first two rows of Table A. Can anyone help me structure this query?
Appreciate your help.|||This doesn't work. Here is what happens. Let me make the tables simple.
Table A
Name......ID
B C A........1
D E F.........2
A............3
Table B
Name1
A
I need all records that contain alphabet A. With the query -
select * from tablea ta , tableb tb
where
ta.name like '%' + convert(varchar(1),tb.name1) +'%'
Only record 3 (A...3) is returned. I also need record 1. Note that the individual alphabets are separated by spaces.
Let me know. Thanks.
Originally posted by fhunth
Try something like this
select * from tablea ta , tableb tb
where
ta.name like '%' + convert(varchar(1),tb.name1) +'%'
or
ta.name like '%' + convert(varchar(1),tb.name2) +'%'
or
ta.name like '%' + convert(varchar(1),tb.name3) +'%'|||Here you go. I created two tables exactly as you specified:
create table table1
(Name varchar(3)
,ID int identity (1,1))
It has values:
ABC, 1
DEF, 2
WXY, 3
create table table2
(Name1 char(1)
,Name2 char(1)
,Name3 char(1))
It has values:
A, B, C
D, G, Z
Now run this:
select distinct table1.Name, table1.ID from table1, table2
where charindex (substring(NAME,1,1), Name1+Name2+Name3) <> 0
or charindex (substring(NAME,1,2), Name1+Name2+Name3) <> 0
or charindex (substring(NAME,1,3), Name1+Name2+Name3) <> 0
Comparing 2 Columns Containing Null Values
Hi All.
I'm having some issues with what seems to be a simple update statement but is giving me grief when one or both of the columns I'm comparing are null. My statement (simplified) is as follows:-
UPDATE
TAB_A
SET
TAB_A.TRADCODE = TAB_B.TRADCODE
FROM
TADS_STAGE.DBO.UNCLBRAMDEPT TAB_B
JOIN
TADS.DBO.UNCLBRAMDEPT TAB_A
ON
TAB_B.BRANCODE = TAB_A.BRANCODE
AND
TAB_B.MERDCODE = TAB_A.MERDCODE
AND
(
TAB_B.TRADCODE <> TAB_A.TRADCODE
)
If either of the TRADCODE fields (or both) are null then the comparison fails to return the row to update. I've tried setting the ANSI_NULLS setting to off, this has no effect, presumably because it will only work when comparing a column to a variable or evaluating if the column is null itself.
I've considered using ISNULL, but if one of the columns happens to contain the value that I specify as the replacement value then the comparison will result true and not include the row.
I'd be grateful for any pointers!
Thanks in advance
Sorted it :)
I've used the ISNULL(exp,'') function, as I'm not bothered about updating nothing ('') to null and vice versa.
Just got to go back and alter all 60 scripts now
Nick
|||Nick Colebourn wrote:
I've considered using ISNULL, but if one of the columns happens to contain the value that I specify as the replacement value then the comparison will result true and not include the row.
In this type of situation you would replace your ISNULL with some impossible calue that would never come up. Here are some other ideas:
1. You only want to update Tab_A with Tab_B's value if they're different. Why not just update it all the time?
Cons: Slower Update, More Locks, More overhead if set to full recovery mode.
UPDATE
TAB_A
SET
TAB_A.TRADCODE = TAB_B.TRADCODE
FROM
TADS_STAGE.DBO.UNCLBRAMDEPT TAB_B
JOIN
TADS.DBO.UNCLBRAMDEPT TAB_A
ON
TAB_B.BRANCODE = TAB_A.BRANCODE
AND
TAB_B.MERDCODE = TAB_A.MERDCODE
2. Make your JOIN even more complicated
UPDATE
TAB_A
SET
TAB_A.TRADCODE = TAB_B.TRADCODE
FROM
TADS_STAGE.DBO.UNCLBRAMDEPT TAB_B
JOIN
TADS.DBO.UNCLBRAMDEPT TAB_A
ON
TAB_B.BRANCODE = TAB_A.BRANCODE
AND
TAB_B.MERDCODE = TAB_A.MERDCODE
AND
(
(TAB_B.TRADCODE IS NULL AND TAB_A.TRADCODE IS NOT NULL)
OR (TAB_B.TRADCODE IS NOT NULL AND TAB_A.TRADCODE IS NULL)
OR TAB_B.TRADCODE <> TAB_A.TRADCODE
)
Hi Jared,
Thanks for your reply. I think I'm going to take a multi faceted approach to this, some of my tables are only a few thousand rows, so I'll probably just do the full update, and some are 20 million rows or more, so I'll spend the time writing the intricate where clauses or experimenting with the ISNULL function.
Cheers for taking the time to respond.
Nick
Compare two tables query result
dear all
i need to write SQL Query that compares 2 tables as folows:
lets say i have:
table A with A.A, A.B, A.C columns (Table A with Columns A,B,C)
table B with B.A, B.B, B.C columns (Table B with Columns A,B,C)
i need to compare and to mark (get in result Query) each row lets say in table B that was changed from table A
For Example
Table A
A B C
12 15 hello
15 17 adv
asd 19 23
14 rer 89
Table B
A B C
12 15 hello
15 19 adv
*** 19 23
14 rer 89
the result is the records which was changed from A to B
A B C
15 19 adv
*** 19 23
Thnks alot!
That is probably non-trivial in terms of performance:
Select
CASE WHEN A.A != A.A THEN '***' ELSE A.A AS A
CASE WHEN A.B != A.B THEN '***' ELSE A.B AS B
CASE WHEN A.C != A.C THEN '***' ELSE A.C AS C
FROM TableA
INNER Join TableB
ON A.IdentifierWhichcannotchange = b.IdentifierWhichcannotchange
You will need the IdentifierWhichcannotchange as elsewhere you cannot identify the matching rows, e.g. how to know that the if two rows look the same after the change from which rows they were actually sourced on.
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||can you explain in more details the A.IdentifierWhichCannotchange, B.IdentifierWhichCannotchange?
the query result dows not change anything on the tables
p.s i run the query in msAccess
And very important thing that i forgotten to mention
The firsrt 3 columns are my Unique (Primary Key)
|||
You need an identitfier in both tables which cannot change, otherwise you won′t be able to match the rows. its like building a sum from multiple values and then afterwards trying to identity which values the sum is based on. In your case, if you have two rows containg the values
1;23;27
3;23;59
which are changed to
4 (changed);23;54 (changed)
4 (changed);23;55 (changed)
How would you know which row refers to which in the other table. they have lost their identifying attribute here (the combination of values). if you had some identifying value which is not supposed to change then you can easily compare the values in the rows refering to the non-changeable attribute (like I did in the query above)
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||OK
first thanks for the replies
so here is my tables and data:
my Id is Columns A,B,C
in the result we can notice 2 things
1) 019 001 000004 was added because it exists in t05 and not t04
2) the value in col j for 019 003 000036 was changed from 003 to 002
the goal is to create table of diffrences!
in general i can unified columns A,B,C to one column (if it helps) 019001000004 for examle
i quess i clarified my self better
|||That will be something like:
Select
CASE WHEN A.D != A.D THEN '***' ELSE A.D AS D,
CASE WHEN A.E != A.E THEN '***' ELSE A.E AS E,
CASE WHEN A.F != A.F THEN '***' ELSE A.F AS F,
CASE WHEN A.H != A.H THEN '***' ELSE A.H AS H,
CASE WHEN A.I != A.I THEN '***' ELSE A.I AS I,
CASE WHEN A.J != A.JTHEN '***' ELSE A.J AS J
FROM TableA A
INNER Join TableB B
ON
A.A = B.A AND
A.B = B.B AND
A.C = C.C
Jens K. Suessmeyer
http://www.sqlserver2005.de
Monday, March 19, 2012
Compare Two Columns with WildCard
I am trying to compare columns in two tables with a wildcard character.
One table: Other Table:
-------
ID Col1 | ID Col2
-------
1 1 1 1A
1 1B
2 2 2 2A
3 3 3 3A
4 5 4 5A
4 5B
4 5C
5 7
6 27
7 50 7 50A
----------
I want to writing something like:
SELECT Table1.ID, Table1.Col1, Table2.ID, Table2.Col2
From Table1, Table2
WHERE (Table1.ID = Table2.ID) AND (Table2.Col2 LIKE Table1.Col1%)
which obviously does not work.
basically "column2 Text%" so if ID = 1, Col1 = 1 => will have the following comparisons turn out true:
1A LIKE '1*'
1B LIKE '1*'
How can I do a comparison like this?I am reminded of an old saying:
"Make it possible for programmers to write programs in English, and you will find that programmers cannot write in English."
Care to try that explanation again? Once more, with feeling...|||ha, sorry about that. my formatting is all messed up above too, i'm sure that didn't help either.
I basically just want to write a SELECT query and compare two columns with a wildcard character.
how do I do this?
do something like:
table1.col1 LIKE 'sam%'
except with another column like:
table1.col1 LIKE '(table2.col2)%'
except that doesn't work... can I do this?|||select Table1.ID
, Table1.Col1
, Table2.ID
, Table2.Col2
from Table1
left outer
join Table2
on Table2.ID = Table1.ID
and Table1.Col1 like Table2.Col2 + '%'|||create table #t1 (id int, c1 int)
insert into #t1 select
1, 1 union all select
2, 2 union all select
3, 3
create table #t2 (id int, c1 varchar(10))
insert into #t2 select
1, '1A' union all select
1, '1B' union all select
2, '8A' union all select
2, '8B' union all select
3, '3'
select * from #t1 a,#t2 b
where a.id=b.id
and b.c1 like convert(varchar(10),a.c1)+'%'
drop table #t1
drop table #t2
One table: Other Table:
-------
ID Col1 | ID Col2
-------
1 1 1 1A
1 1B
2 2 2 2A
3 3 3 3A
4 5 4 5A
4 5B
4 5C
5 7
6 27
7 50 7 50A
----------
I want to writing something like:
SELECT Table1.ID, Table1.Col1, Table2.ID, Table2.Col2
From Table1, Table2
WHERE (Table1.ID = Table2.ID) AND (Table2.Col2 LIKE Table1.Col1%)
which obviously does not work.
basically "column2 Text%" so if ID = 1, Col1 = 1 => will have the following comparisons turn out true:
1A LIKE '1*'
1B LIKE '1*'
How can I do a comparison like this?I am reminded of an old saying:
"Make it possible for programmers to write programs in English, and you will find that programmers cannot write in English."
Care to try that explanation again? Once more, with feeling...|||ha, sorry about that. my formatting is all messed up above too, i'm sure that didn't help either.
I basically just want to write a SELECT query and compare two columns with a wildcard character.
how do I do this?
do something like:
table1.col1 LIKE 'sam%'
except with another column like:
table1.col1 LIKE '(table2.col2)%'
except that doesn't work... can I do this?|||select Table1.ID
, Table1.Col1
, Table2.ID
, Table2.Col2
from Table1
left outer
join Table2
on Table2.ID = Table1.ID
and Table1.Col1 like Table2.Col2 + '%'|||create table #t1 (id int, c1 int)
insert into #t1 select
1, 1 union all select
2, 2 union all select
3, 3
create table #t2 (id int, c1 varchar(10))
insert into #t2 select
1, '1A' union all select
1, '1B' union all select
2, '8A' union all select
2, '8B' union all select
3, '3'
select * from #t1 a,#t2 b
where a.id=b.id
and b.c1 like convert(varchar(10),a.c1)+'%'
drop table #t1
drop table #t2
Subscribe to:
Posts (Atom)