ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,206 through 8,220 (of 10,143 total)

RE: How could this be completed without a cursor

Here's version 2 Gareth, with an extended sample data set.
-- test data --
DROP TABLE #wms_requirements
DROP TABLE #wms_batch_queue
CREATE TABLE #wms_requirements
(
[warehouse] [char](2) NOT NULL,
...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 20, 2010 at 10:25 am

#1106181
RE: Help with a join

AND a.History_YN IS NULL
The opposite would be:
AND NOT a.History_YN IS NULL
or
AND a.History_YN IS NOT NULL
If you want to compare your column with a second literal, then it might...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 20, 2010 at 7:49 am

#1106056
RE: Help with a join

SELECT a.Contact_info
,b.MEMBER_NUMBER
,c.CLIENT_NUMBER
,a.CONTACT_TYPE_ID
,d.history_YN
FROM FRATERNAL.CONTACTS a
inner join FRATERNAL.CLIENT_CONTACTS a1 on a.CONTACT_ID = a1.CONTACT_ID
inner join FRATERNAL.MEMBER b on a1.CLIENT_ID = b.CLIENT_ID
inner join FRATERNAL.CLIENT c on a1.CLIENT_ID = c.CLIENT_ID
WHERE a.CONTACT_TYPE_ID in ('Email1')
AND b.MEMBER_NUMBER...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 20, 2010 at 7:34 am

#1106041
RE: Help with a join

Which table contains the column HISTORY_YN?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 20, 2010 at 7:21 am

#1106030
RE: Help with a join

Jpotucek (1/20/2010)
awesome.. it's actually all coming together in my head 🙂
The legendary Linda Lovelace apparently said something similar.
The Y is a literal, and as such requires...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 20, 2010 at 7:11 am

#1106022
RE: Help with a join

SELECT
a.Contact_info
,b.MEMBER_NUMBER
,c.CLIENT_NUMBER
,a.CONTACT_TYPE_ID
FROM FRATERNAL.CONTACTS a
inner join FRATERNAL.CLIENT_CONTACTS a1 on a.CONTACT_ID = a1.CONTACT_ID
inner join FRATERNAL.MEMBER b on a1.CLIENT_ID = b.CLIENT_ID
inner join FRATERNAL.CLIENT c on a1.CLIENT_ID = c.CLIENT_ID
WHERE a.CONTACT_TYPE_ID in ('Email1')
AND b.MEMBER_NUMBER...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 20, 2010 at 6:48 am

#1106003
RE: Extract Data Defined pattern

mranganwa (1/19/2010)
Burn.After.Reading
:w00t:
Some of the recent questions might have been better marked "burn before reading"!
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 20, 2010 at 6:44 am

#1106001
RE: Are the posted questions getting worse?

lmu92 (1/20/2010)
Gianluca Sartori (1/20/2010)
...
Gianluca Sartori (1/19/2010)
What are writing about, Paul?
I started writing my first article 9 months ago and it isn't finished yet.:crying:
Well, I can say that I had a...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 20, 2010 at 5:52 am

#1105971
RE: SQL SERVER 2008 Pivot

Your character string - your query - is missing the closing quotation mark.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 20, 2010 at 5:48 am

#1105965
RE: How could this be completed without a cursor

Hey Gareth
The solution I've posted above works, but looking at it I can't help thinking of one of Jeff Moden's mantras - make it work, make it fast, make it...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 19, 2010 at 9:34 am

#1105518
RE: How could this be completed without a cursor

CREATE TABLE #wms_requirements
(
[warehouse] [char](2) NOT NULL,
[product] [char](20) NOT NULL,
...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 19, 2010 at 7:32 am

#1105411
RE: Detect duplicates with custom rule and update a row according to date

Hi Biz
Can you please run the following code and identify those rows which you call Duplicates?
I've rehashed the sample data so that it's now a temporary table.
-- Set up sample...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 19, 2010 at 5:53 am

#1105357
RE: Caution with EXCEPT

Some articles are well worth reading twice.
I've spent all afternoon working for the first time with EXCEPT instead of the usual LEFT JOIN and checking for NULL, and it's taken...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 18, 2010 at 10:17 am

#1105025
RE: Question about query error received

Kit G (1/18/2010)
Chris Morris-439714 (1/15/2010)
Hello
The UPDATE and the SET apply to the same table.
Ideally the first table listed after FROM is also the target of the update.
In your case,...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 18, 2010 at 8:24 am

#1104965
RE: Is it possible to keep my database in RAM????

Saravanan T (1/18/2010)
Is it possible to keep my database in RAM????
Having performance issues, Saravan? Can you go into some more detail please?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 18, 2010 at 7:17 am

#1104922

Viewing 15 posts - 8,206 through 8,220 (of 10,143 total)