q

CREATE TABLE U(col1 INT);
INSERT INTO U VALUES(2);
INSERT INTO U VALUES(7);
INSERT INTO U VALUES(9);
CREATE TABLE V(col1 INT);
INSERT INTO V VALUES(3);
INSERT INTO V VALUES(7);
INSERT INTO V VALUES(NULL);
SELECT * FROM U WHERE
col1 NOT IN(SELECT col1 FROM V);
i expected it to return 2 and 9 but it returned nothing. can anyone explain?This is because of NULL value in the V table. Comparing to NULL, we don't
know whether the known values are equal or different, so we have to make an
agreement how to deal in such situations. In your case, you see the result.
If you want to get the values you are mentioning, change the query to
SELECT * FROM U WHERE
U.col1 NOT IN (SELECT V.col1 FROM V WHERE V.col1 IS NOT NULL);
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"ichor" <ichor@.hotmail.com> wrote in message
news:OqZWh2FrFHA.2076@.TK2MSFTNGP14.phx.gbl...
> CREATE TABLE U(col1 INT);
> INSERT INTO U VALUES(2);
> INSERT INTO U VALUES(7);
> INSERT INTO U VALUES(9);
> CREATE TABLE V(col1 INT);
> INSERT INTO V VALUES(3);
> INSERT INTO V VALUES(7);
> INSERT INTO V VALUES(NULL);
>
> SELECT * FROM U WHERE
> col1 NOT IN(SELECT col1 FROM V);
> i expected it to return 2 and 9 but it returned nothing. can anyone
> explain?
>|||If you are using the NOT IN Opereator with a subquery and the subquery
contains any NULL values, the subquery will return NULL!. This can be
dangerous, and this is not the case if you use IN.
http://toponewithties.blogspot.com/...es.blogspot.com
"ichor" <ichor@.hotmail.com> wrote in message
news:OqZWh2FrFHA.2076@.TK2MSFTNGP14.phx.gbl...
> CREATE TABLE U(col1 INT);
> INSERT INTO U VALUES(2);
> INSERT INTO U VALUES(7);
> INSERT INTO U VALUES(9);
> CREATE TABLE V(col1 INT);
> INSERT INTO V VALUES(3);
> INSERT INTO V VALUES(7);
> INSERT INTO V VALUES(NULL);
>
> SELECT * FROM U WHERE
> col1 NOT IN(SELECT col1 FROM V);
> i expected it to return 2 and 9 but it returned nothing. can anyone
> explain?
>|||That exact example is given in Itzik Ben-Gan's T-SQL Black Belt column
this month in SQLMag (Don't Avoid the UNKNOWN
<http://www.windowsitpro.com/Article...47010.html?Ad=1> ).
If you want a fuller explanation, you should read his article.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Dejan Sarka wrote:

>This is because of NULL value in the V table. Comparing to NULL, we don't
>know whether the known values are equal or different, so we have to make an
>agreement how to deal in such situations. In your case, you see the result.
>If you want to get the values you are mentioning, change the query to
>SELECT * FROM U WHERE
> U.col1 NOT IN (SELECT V.col1 FROM V WHERE V.col1 IS NOT NULL);
>
>|||this is where i took the code from. i have subscribed to sqlmag but cant see
the entire article.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:OA$
QFNGrFHA.3096@.TK2MSFTNGP15.phx.gbl...
That exact example is given in Itzik Ben-Gan's T-SQL Black Belt column this
month in SQLMag (Don't Avoid the UNKNOWN). If you want a fuller explanation
, you should read his article.
mike hodgson
blog: http://sqlnerd.blogspot.com
Dejan Sarka wrote:
This is because of NULL value in the V table. Comparing to NULL, we don't
know whether the known values are equal or different, so we have to make an
agreement how to deal in such situations. In your case, you see the result.
If you want to get the values you are mentioning, change the query to
SELECT * FROM U WHERE
U.col1 NOT IN (SELECT V.col1 FROM V WHERE V.col1 IS NOT NULL);

Pulling Random Distinct Percentage

Having trouble coming up with a fast and efficient sproc for this.
Thanks in advance for any help!
tblRecords
record_id (int) [key]
location_id (int)
owned_by_id (int) [default = 0]
is_complete (int) [default = 0]
date_entered (datetime) [default = getDate()]
date_completed (datetime)
I'm trying to populate a Datagrid with ((Total - total completed -
total owned) * 7%) of random non-complete records and all owned records
within a given date range for each site sorted by date_entered.
I figure I'll need to create a temp table holding all the totals
(total, total completed & total owned) for each site (using distinct
and count). And then for each row (or site) loop through the math
shown above and then select random top X where is_complete = 0 AND
owned_by_id = 0 AND date_entered BETWEEN startDate AND stopDate.
Then SELECT WHERE is_complete = 0 AND owned_by_id <> 0 AND date_enteed
BETWEEN startDate AND stopDate. Then somehow merge the result... then
merge again with the looping table. Once complete sort table by
date_entered and return. I'm not sure if that is the best/easiest way
to do this... so I'm wondering if someone else knows a better way, and
can provide an example, as my SQL skills are not up to par for this.
Thanks again!
~GregHi
Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data. Also posting the required resultset from your data would be
helpful.
If your is_completed column can only have values of 0 or 1 then you could do
something like:
SELECT location_id, owned_by_id, SUM(is_complete) as Completed,
SUM(1-is_complete)AS incomplete, COUNT(*) AS total
FROM tblRecords
GROUP BY location_id, owned_by_id
to get the number completed. You will need to add the date constriction in a
WHERE CLAUSE. If you can have other values you can do something like:
SELECT location_id, owned_by_id, SUM(CASE WHEN is_complete = 1 THEN 1 ELSE 0
END) as Completed, SUM(CASE WHEN is_complete = 0 THEN 1 ELSE 0 END)AS
incomplete, COUNT(*) AS total
FROM tblRecords
GROUP BY location_id, owned_by_id
It is not clear if your owned_by_id is actually an is_owned binary value!.
You could use this query in a view or derived table instead of using a
temporary table.
HTH
John
"Bac2Day1" <Bac2Day1@.gmail.com> wrote in message
news:1136405669.374006.39790@.g44g2000cwa.googlegroups.com...

> Having trouble coming up with a fast and efficient sproc for this.
> Thanks in advance for any help!
> tblRecords
> record_id (int) [key]
> location_id (int)
> owned_by_id (int) [default = 0]
> is_complete (int) [default = 0]
> date_entered (datetime) [default = getDate()]
> date_completed (datetime)
> I'm trying to populate a Datagrid with ((Total - total completed -
> total owned) * 7%) of random non-complete records and all owned records
> within a given date range for each site sorted by date_entered.
> I figure I'll need to create a temp table holding all the totals
> (total, total completed & total owned) for each site (using distinct
> and count). And then for each row (or site) loop through the math
> shown above and then select random top X where is_complete = 0 AND
> owned_by_id = 0 AND date_entered BETWEEN startDate AND stopDate.
> Then SELECT WHERE is_complete = 0 AND owned_by_id <> 0 AND date_enteed
> BETWEEN startDate AND stopDate. Then somehow merge the result... then
> merge again with the looping table. Once complete sort table by
> date_entered and return. I'm not sure if that is the best/easiest way
> to do this... so I'm wondering if someone else knows a better way, and
> can provide an example, as my SQL skills are not up to par for this.
> Thanks again!
> ~Greg
>