ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,356 through 8,370 (of 10,143 total)

RE: update cursor

charu.verma (12/11/2009)
Im not aware of the set based method..Can you pls suggest how is that used?
Like this:
DROP TABLE #Tbl1
DROP TABLE #Tbl2
DROP TABLE #Tbl3
CREATE TABLE #Tbl1 ([sid] int, cid int)
CREATE TABLE...
^{“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

December 11, 2009 at 4:34 am

#1091081
RE: Elliminate Cross Join in the query

vkundar (12/10/2009)
Thanks for the reply, I want first and last row as output rather then all the four rows.
waiting for your reply..:-)
But Nabha's query works...
CREATE TABLE #ticket_history (id INT,...
^{“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

December 11, 2009 at 2:40 am

#1091038
RE: Join problem in query

Lynn Pettis (12/10/2009)
Grasshopper (aka sk.panda ),
Would like a correct answer or just shots in the dark?
If you want a correct answer, please read and follow the instructions in this article[/url]...
^{“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

December 11, 2009 at 2:19 am

#1091030
RE: T-SQL Query

Dave Ballantyne (12/10/2009)
I think this means that the collation of the current database is different to that of the tempdb.
Depends if the code failed on the sample using the #table,...
^{“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

December 10, 2009 at 10:13 am

#1090703
RE: T-SQL Query

Interesting - from BOL "Character literals and variables are assigned the default collation of the current database". So the default collation of your db is different to that of 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

December 10, 2009 at 9:08 am

#1090637
RE: Join problem in query

There was an irresistable whiff of buffalo chips in the air...
--Transaction Table
CREATE TABLE #INBOUND_PLM_CHCM (PER_NR INT, [STATUS] INT, SOC_MAPPING VARCHAR(10))
INSERT INTO #INBOUND_PLM_CHCM (PER_NR, [STATUS], SOC_MAPPING)
SELECT 12345, 0, 'PLM-5000'...
^{“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

December 10, 2009 at 7:42 am

#1090552
RE: Join problem in query

if IPC.STATUS=CHCM.INCOMING_DATA then CHCM.OUTGOING_DATA AS 'STATUS'
else the condition does not satisfy then display IPC.STATUS
You've specified an INNER JOIN in your query. You will never see when the "condition does not...
^{“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

December 10, 2009 at 7:10 am

#1090523
RE: Pipe delimited field - splitting?

Rob-350472 (12/10/2009)
I've been playing around with this, and come up with this, which seems to work:
DECLARE loop_cursor CURSOR
FOR
SELECT id, type FROM tblA WHERE id < 100
OPEN loop_cursor
DECLARE...
^{“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

December 10, 2009 at 7:04 am

#1090514
RE: Pipe delimited field - splitting?

Sure...read this[/url]
^{“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

December 10, 2009 at 6:31 am

#1090484
RE: T-SQL Query

Run this:
SET DATEFORMAT DMY
DROP TABLE #Temp
CREATE TABLE #Temp (ProductId INT,
ValiFrom DATETIME, ValidTO DATETIME,
ProductDescription CHAR(3), CountryCode CHAR(2), OpenDate DATETIME)
INSERT INTO #Temp (ProductId, ValiFrom, ValidTO, ProductDescription, CountryCode, OpenDate)
SELECT 123, '01/01/2008',...
^{“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

December 10, 2009 at 5:06 am

#1090454
RE: T-SQL Query

Hi Arun
Can you precisely define the rules which determine that this row:
123 30/11/2009 07/12/2009 NULL NULL 15/09/1998
should be updated to this:
123 30/11/2009 07/12/2009 WWW GB 15/09/1998
And so on for 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

December 10, 2009 at 3:17 am

#1090431
RE: Can I improve this procedure with a join?

Slightly different to Thomas' solution:
SELECT rp.StartDate, SUM(productionVolume) AS totalVolume
FROM @productionData pd
INNER JOIN @reportPeriods rp
ON pd.productionDate >= rp.StartDate
AND pd.productionDate < rp.EndDate
GROUP BY rp.StartDate
ORDER BY rp.StartDate
^{“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

December 10, 2009 at 2:40 am

#1090421
RE: If we do not know exactly which column to join to, can we use an or clause?

You're not very familiar with LEFT JOIN's then?
Nothing beats a little testing - try them for yourself.
All of the different conditions which have come up in this thread.
^{“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

December 9, 2009 at 9:40 am

#1089983
RE: If we do not know exactly which column to join to, can we use an or clause?

It scales very poorly. It works fine, just don't try it on large tables.
^{“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

December 9, 2009 at 9:16 am

#1089961
RE: Ages and Age Ranges

You're welcome.
^{“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

December 9, 2009 at 9:07 am

#1089949

Viewing 15 posts - 8,356 through 8,370 (of 10,143 total)