ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,226 through 6,240 (of 10,143 total)

RE: Record Matching in FIFO Basic in Stock data

Nageswari.J (6/20/2012)
Hi Chris
Yeah u r correct, I also face the same problem when i checked.. I thought to ask to u. before itself u answered... Thanks once...
^{“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

June 20, 2012 at 1:52 am

#1503255
RE: Concatenate 2 rows based on grouping

pwalter83 (6/19/2012)
ChrisM@Work (6/19/2012)
Paul, how many rows max will you have to roll up like this? Your example shows two rows - can it be 3, or 4, or n?
Could be...
^{“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

June 19, 2012 at 10:11 am

#1502964
RE: Help needed to INSERT using a column values variable as a result of a SELECT

Your first post has a code sample like so:
DECLARE @eof as nchar(1)
DECLARE @replace as nvarchar(max)
DECLARE @exec as nvarchar(max)
SELECT @eof = CONVERT(NCHAR(1), 0xFDF8)
SELECT @replace = char(39) + char(39) + char(39) +...
^{“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

June 19, 2012 at 9:12 am

#1502927
RE: Concatenate 2 rows based on grouping

Paul, how many rows max will you have to roll up like this? Your example shows two rows - can it be 3, or 4, or n?
^{“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

June 19, 2012 at 9:06 am

#1502922
RE: Row_Number doubt

snigdhandream (6/19/2012)
I have a large table having millions of rows. There is one datetime column which captures the date and time a row is inserted to the table. Now, I...
^{“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

June 19, 2012 at 6:37 am

#1502783
RE: select statement blocking update statement until select statement is finished

If you absolutely cannot follow any of Grant's excellent suggestions, then there is a cheat which may work for you:
-- fuzzy non-blocking prefetch, faster without blob column
SELECT [ONLY the...
^{“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

June 19, 2012 at 5:40 am

#1502740
RE: Search with 80% accuracy

Fuzzy matching generates a similarity index between two strings: try this and this.
Cheers
ChrisM
^{“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

June 19, 2012 at 3:32 am

#1502651
RE: Record Matching in FIFO Basic in Stock data

Gosh, thanks for the generous comments guys - however, the method fails if the sample data set is expanded to have more sales than buys within a partition. Here's code...
^{“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

June 19, 2012 at 3:26 am

#1502648
RE: Record Matching in FIFO Basic in Stock data

This generates the correct results from the given sample data, however I'd be more comfortable with a larger sample:
;WITH Buys AS (
SELECT rn = ROW_NUMBER() OVER (PARTITION BY clientcode, productid...
^{“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

June 18, 2012 at 8:41 am

#1502256
RE: Nasty, nasty, nasty....

Stueyd (6/13/2012)
....So for example, I insert a new row of data into the main table it will also cascade the identity into about 14 other tables....
Interesting. Care to elaborate?
^{“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

June 18, 2012 at 2:58 am

#1502128
RE: Nasty, nasty, nasty....

Have a look at Glenn Berry's diagnostic information queries[/url]. Two of these queries should be particularly relevant to you; missing indexes, and unused indexes. The comments attached to both are...
^{“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

June 18, 2012 at 2:56 am

#1502127
RE: Case With Group by Row Values into Column

farooq.hbs (6/17/2012)
If i Execute the above query
I'm getting Result set as
Msg 2714, Level 16, State 6, Line 8
There is already an object named '#View_Tag' in the database.
The #temp...
^{“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

June 18, 2012 at 2:45 am

#1502121
RE: Unpivoting Data

Jeff Moden (6/15/2012)
ChrisM@Work (6/15/2012)
vinu512 (6/15/2012)
ChrisM@Work (6/15/2012)
anthony.green (6/15/2012)
Thanks guys, much appreciated. Was racking the brain yesterday on that and for the life of me couldnt get unpivot working.
I like the...
^{“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

June 15, 2012 at 8:34 am

#1501718
RE: Case With Group by Row Values into Column

I'm right with you on that Sean. There are some other quirks in the query which suggest that if it's generating correct results, it is, to some extent, a happy...
^{“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

June 15, 2012 at 8:32 am

#1501717
RE: Case With Group by Row Values into Column

Here's a start. Test and comment.
Declare @portfolioId Varchar(8000),
@ParentWorkids Varchar(50)
Set @portfolioId = '1800m380000iot22g3t0000000_1800m380000io7c5nr50000000' -- this looks like TWO concatenated portfolioIds
set @ParentWorkids = '1801a2g0000ih0vl2abg000000'
---------------------------------------------------------------------------
SELECT child_work_id = [object_id],
PrimaryBBPProcess = MAX(CASE WHEN...
^{“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

June 15, 2012 at 8:06 am

#1501684

Viewing 15 posts - 6,226 through 6,240 (of 10,143 total)