ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,751 through 9,765 (of 10,143 total)

RE: Comparing Results

That's ok Grinja, my fault, not enough coffee. This should do the trick:
-- identifies which CoreItem has SubItems with categories which do not correspond to any of the CoreItem categories
SELECT...
^{“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

July 30, 2008 at 3:38 am

#850324
RE: COUNT problem

Hi Adam
Here's a little extra material relating to your question. It pulls in Jack's nifty solution which would appear to be ideal in your case, and an alternative method or...
^{“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

July 30, 2008 at 3:03 am

#850308
RE: Comparing Results

No problem Grinja.
This would appear to do the trick:
DROP TABLE #CoreItem
DROP TABLE #SubItem
CREATE TABLE #CoreItem (CoreItemID INT, Category CHAR(1))
INSERT INTO #CoreItem (CoreItemID, Category)
SELECT 1, 'A' UNION ALL ...
^{“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

July 30, 2008 at 2:24 am

#850297
RE: duplicate key

What do you get when you run this, Ray?
SELECT COUNT(*), CardNo,Base,TxType,RspCode,RspFault,TxDate,TxCurrency,TxAmount,SettlementCurrency,SettleAmount,
Acquirer,TerminalID,TraceNr,Merchant,Accountno,Rundate
FROM Test
WHERE ISNUMERIC(Accountno) <> 1
GROUP BY CardNo,Base,TxType,RspCode,RspFault,TxDate,TxCurrency,TxAmount,SettlementCurrency,SettleAmount,
Acquirer,TerminalID,TraceNr,Merchant,Accountno,Rundate
HAVING COUNT(*) > 1
^{“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

July 29, 2008 at 10:26 am

#849962
RE: One Heck of a Query

Oops sorry, that's always the danger of not having test data to...well, test.
D1.X_AREA2 as c4,
should read
D1.X_AREA as c4,
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

July 29, 2008 at 9:51 am

#849924
RE: IF - Then , Yes or No Statement

Hi Pete, I'm really sorry I can't help you any further with this, I don't have the client tools you are using. I'm sure someone will hop in shortly and...
^{“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

July 29, 2008 at 9:36 am

#849906
RE: IF - Then , Yes or No Statement

I'm not entirely sure what you mean...but you would usually apply an alias to a CASE construct in a SELECT in one of two ways:
SELECT NewColumnName = CASE WHEN [Start...
^{“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

July 29, 2008 at 9:20 am

#849890
RE: Comparing Results

Hi Grinja
I suspect there may be a flaw in the test data. Try running this and examine the results. I'm guessing that you want to eliminate category 'D' for coreitemid=2...
^{“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

July 29, 2008 at 9:14 am

#849884
RE: IF - Then , Yes or No Statement

pete.trudell (7/29/2008)
I am a novice, and I need to dupicate a formula used in Access in a Stored Procedure of our Database. The Access Column reads as : ...
^{“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

July 29, 2008 at 8:37 am

#849836
RE: Cursors and variables outside them

rbarryyoung (7/29/2008)
Atif Sheikh (7/29/2008)
1. The variable @ReturnID id NOT the output parameter returned from your SP. It will always return 0 if the sp executes successfully.
No, that is...
^{“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

July 29, 2008 at 7:58 am

#849801
RE: One Heck of a Query

I wonder if it's the peculiar join between D1 and D2? Using a proper INNER JOIN might help...
select distinct D3.c4 as c1,
D3.c4 as c2,
...
^{“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

July 29, 2008 at 7:57 am

#849800
RE: inner join duplicate removal

You're welcome, Usul.
Shame that, like us, you're not using 2k5. The solution is more elegant and much faster...
http://www.sqlservercentral.com/Forums/Topic537762-338-3.aspx#bm538585
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

July 29, 2008 at 7:41 am

#849787
RE: inner join duplicate removal

Is this what you're expecting to see?
DROP TABLE #Test
CREATE TABLE #Test (Rowid INT, orderdt DATETIME, orderid INT, comment VARCHAR(60))
INSERT INTO #Test (Rowid, orderdt, orderid, comment)
SELECT 1, CAST('13/05/2008 07:54:13' AS DATETIME),...
^{“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

July 29, 2008 at 3:57 am

#849644
RE: The Daily Commute

tedo (7/29/2008)
sorry hehe it takes 1hr and 6min to London waterloo, that is still quicker, I just checked out nationalrail.co.uk I still knew it was quicker. cost is 12:80 for...
^{“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

July 29, 2008 at 2:51 am

#849620
RE: The Daily Commute

tedo (7/28/2008)
I live in Japan, I have to go everywhere by train, I live about 30k from tokyo, however, the land of the shinkansen, has really slow trains. in 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

July 29, 2008 at 2:30 am

#849616

Viewing 15 posts - 9,751 through 9,765 (of 10,143 total)