ChrisM@Work

Forum Replies Created

Viewing 15 posts - 10,006 through 10,020 (of 10,143 total)

RE: Crystal Reports XI and SQL Server 2k

Thanks again...but...one of the reports returns 4.4 million rows and since this is financials, the NOLOCK hint is "probably best not used" (see today's editorials). This one report can 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

December 17, 2007 at 8:34 am

#760709
RE: Crystal Reports XI and SQL Server 2k

Yvan Bouchard (12/17/2007)
Transfer the heavy work to the SQL Server and you will be ok.
Hi Yvan
Many thanks for the quick reply. I've figured out that it's the SQL side where...
^{“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 17, 2007 at 7:59 am

#760686
RE: Partial String Search from Subquery

Welcome, Lenard. Good luck!
^{“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 16, 2007 at 11:21 am

#760588
RE: Contract job

It's not so bad here in London - see Jobserve.
Most of my contractor mates start looking 4 weeks before term. Until a renewal is signed, we're actively looking 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

December 16, 2007 at 4:36 am

#760574
RE: Best Place for stay (OneFriday Street - UK or Finsbury Circle - UK)

Karthik
You need to account for the duration and cost of travel. London is about 30 miles across, and people live within and outside its limits. Cheaper accommodation further away from...
^{“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 16, 2007 at 4:02 am

#760572
RE: Partial String Search from Subquery

You could try these as an alternative:
SELECT
p.ID,
p.FirstName,
p.LastName
FROM Person p
INNER JOIN @searchParts s
ON (p.FirstName...
^{“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 16, 2007 at 3:07 am

#760571
RE: More nesting trouble

Yay! 😀
^{“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

November 29, 2007 at 8:20 am

#755479
RE: Matching names & addresses between tables

Hi David
Working with person names and addresses is far easier than working with company names and addresses because company names are likely to contain common elements which relate to 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

November 29, 2007 at 6:37 am

#755391
RE: More nesting trouble

Use table aliases to make your query far more readable, and correct your join syntax:
SELECT gr.Code_t,
SUM(case when gr.RevDate = '1/1/07' then gr.CommissionableRevenue end) as JanCommis,--
SUM(case when gr.RevDate = '1/1/07' then...
^{“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

November 29, 2007 at 2:16 am

#755283
RE: Trouble with cost summaries

Do you know any of the details? Some older db's e.g. xbase clones had implementations of SQL which worked perfectly well using IIF() instead of CASE;
SUM(IIF(today = 'wednesday', 1, 0))
^{“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

November 28, 2007 at 5:43 am

#754933
RE: Trouble with cost summaries

What's your linked server, Edmund?
^{“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

November 28, 2007 at 5:33 am

#754931
RE: Trouble with cost summaries

Adam is spot on, but you only need to join your recoverable costs table once:
[font="Courier New"]SELECT UVS.StockbookNumber,
UVS.FullChassis,
UVS.SellingPrice,
UVS.RegistrationNumber,
Sum(CASE OTn.RecoverableNRC WHEN 'N' THEN VSn.ActualCost ELSE 0 END) AS 'NR Sum of ActualCost',
Sum(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

November 28, 2007 at 3:05 am

#754889
RE: Using CASE result in WHERE

Blimey! That would sure confuse the hell out of anybody trying to maintain it! Here's a way of doing it which is very close, but which we've all seen 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

November 15, 2007 at 9:18 am

#751570
RE: NUll values for input parameters

Hi Jonathan
Yes, you could use ISNULL to swap null values for "unrealistic" integer values for your ID's, so that the test would fail. However, if any of your ID parameters...
^{“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

November 14, 2007 at 9:50 am

#751156
RE: INSERT into Excel leaves values as text

Hi smv
I'm using linked spreadsheets and doing updates, but the principle is probably the same - use a template spreadsheet and ensure that the columns you want numeric, are formatted...
^{“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

November 14, 2007 at 2:50 am

#751000

Viewing 15 posts - 10,006 through 10,020 (of 10,143 total)